DuckDB by Example

DuckDB is a high-performance analytical database system. It is designed to be fast, reliable, portable, and easy to use.

DuckDB by Example introduces DuckDB's rich SQL dialect using annotated examples. To get the most out of these examples, be sure to install DuckDB and check out the official documentation.

Hello World

Install the DuckDB CLI:

$ curl https://install.duckdb.org | sh

Run the CLI:

$ duckdb

Run a SQL statement:

SELECT 'Hello, World!' AS greeting;

Selecting

Select all columns from the customers table:

SELECT *
FROM customers;

Select the first_name and city columns from the customers table:

SELECT
  first_name,
  city
FROM customers;

Select the first 10 rows from the customers table:

SELECT *
FROM customers
LIMIT 10;

Select all unique city values from the customers table:

SELECT DISTINCT city
FROM customers;

Select all columns except the city column from the customers table:

SELECT * EXCLUDE (city)
FROM customers;

Select all columns from the customers table, but replace city with lower(city):

SELECT * REPLACE (lower(city) AS city)
FROM customers;

Filtering

Select all rows from the customers table where the city is "Amsterdam":

SELECT *
FROM customers
WHERE city = 'Amsterdam';

Select all rows from the customers table where the customer_id is 6 or 7:

SELECT *
FROM customers
WHERE
  customer_id = 6
  OR customer_id = 7;

Select all rows from the customers table where the first_name includes "mark" (case-sensitive):

SELECT *
FROM customers
WHERE first_name LIKE '%mark%';

Select all rows from the customers table where the first_name includes "mark" (case-insensitive):

SELECT *
FROM customers
WHERE first_name ILIKE '%mark%';

Grouping

Count the number of entries in the customers table for each first_name:

SELECT
  first_name,
  count(*)
FROM customers
GROUP BY first_name;

Ordering

Select all columns from the customers table, ordered by city (ascending):

SELECT *
FROM customers
ORDER BY city;

Select all columns from the customers table, ordered by city (descending):

SELECT *
FROM customers
ORDER BY city DESC;

Select all columns from the customers table, ordered by country then city:

SELECT *
FROM customers
ORDER BY country, city;

Common Table Expressions

Create a CTE named people and use it in the main query:

WITH people AS (
  SELECT 'Mark' AS first_name
)
SELECT *
FROM people;

Window Functions

Generate a row_number column to enumerate rows:

SELECT
  row_number() OVER (),
  *
FROM customers;

Generate a row_number column to enumerate rows, ordered by created_at:

SELECT
  row_number() OVER (ORDER BY created_at),
  *
FROM customers;