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;

Create Table

Create a table from a CSV file:

CREATE TABLE penguins AS
  SELECT *
  FROM read_csv('http://blobs.duckdb.org/data/penguins.csv', nullstr = 'NA');

Selecting

Select all columns from the penguins table:

SELECT *
FROM penguins;

Select the species and island columns from the penguins table:

SELECT
  species,
  island
FROM penguins;

Select the first 10 rows from the penguins table:

SELECT *
FROM penguins
LIMIT 10;

Select all unique species values from the penguins table:

SELECT DISTINCT species
FROM penguins;

Select all columns except the island column from the penguins table:

SELECT * EXCLUDE (island)
FROM penguins;

Select all columns from the penguins table, but replace species with lower(species):

SELECT * REPLACE (lower(species) AS species)
FROM penguins;

Filtering

Select all rows from the penguins table where the species is "Chinstrap":

SELECT *
FROM penguins
WHERE species = 'Chinstrap';

Select all rows from the penguins table where the year is 2007 or 2008:

SELECT *
FROM penguins
WHERE
  year = 2007
  OR year = 2008;

Select all rows from the penguins table where the sex includes "male" (case-sensitive):

SELECT *
FROM penguins
WHERE sex LIKE '%male%';

Select all rows from the penguins table where the sex includes "male" (case-insensitive):

SELECT *
FROM penguins
WHERE sex ILIKE '%male%';

Grouping

Count the number of entries in the penguins table for each species:

SELECT
  species,
  count(*)
FROM penguins
GROUP BY species;

Ordering

Select all columns from the penguins table, ordered by bill_length_mm (ascending):

SELECT *
FROM penguins
ORDER BY bill_length_mm;

Select all columns from the penguins table, ordered by bill_length_mm (descending):

SELECT *
FROM penguins
ORDER BY bill_length_mm DESC;

Select all columns from the penguins table, ordered by species then bill_length_mm:

SELECT *
FROM penguins
ORDER BY species, bill_length_mm;

Limit and Offset

Select the first 10 rows from the penguins table:

SELECT *
FROM penguins
LIMIT 10;

Select the first 25% of rows from the penguins table:

SELECT *
FROM penguins
LIMIT 25%;

Select 10 rows from the penguins table, starting at position 5:

SELECT *
FROM penguins
LIMIT 10
OFFSET 5;

Aggregation

Select the average of the bill_length_mm column:

SELECT avg(bill_length_mm)
FROM penguins;

Select the maximum of the bill_length_mm column, grouped by species:

SELECT
  species,
  max(bill_length_mm)
FROM penguins
GROUP BY species;

Casting

Cast the bill_length_mm column to VARCHAR:

SELECT CAST(bill_length_mm AS VARCHAR)
FROM penguins;

Cast the body_mass_g column to DOUBLE:

SELECT body_mass_g::DOUBLE
FROM penguins;

Common Table Expressions

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

WITH chinstrap_penguins AS (
  SELECT *
  FROM penguins
  WHERE species = 'Chinstrap'
)
SELECT *
FROM chinstrap_penguins;

Window Functions

Generate a row_number column to enumerate rows:

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

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

SELECT
  row_number() OVER (ORDER BY bill_length_mm),
  *
FROM penguins;

CSV

Read a CSV file and auto-infer options:

SELECT *
FROM 'penguins.csv';

Read a CSV file from a URL and auto-infer options:

SELECT *
FROM 'http://blobs.duckdb.org/data/penguins.csv';

Read a CSV file with custom options:

SELECT *
FROM read_csv('http://blobs.duckdb.org/data/penguins.csv', nullstr = 'NA');

TSV

Read a TSV file:

SELECT *
FROM read_csv('penguins.tsv', delim = '\t');

Write a table to a TSV file:

COPY penguins TO 'penguins.tsv' (DELIMITER '\t');

JSON

Read a JSON file:

SELECT *
FROM 'penguins.json';

Write a table to a JSON file:

COPY penguins TO 'penguins.json';

Parquet

Read a single Parquet file:

SELECT *
FROM 'my_file.parquet';

Read multiple Parquet files as a single table:

SELECT *
FROM read_parquet(['file1.parquet', 'file2.parquet']);

Read all Parquet files that match the glob pattern:

SELECT *
FROM 'my_files/*.parquet';

Write the results of a query to a Parquet file:

COPY (
  SELECT *
  FROM penguins
) TO 'penguins.parquet' (FORMAT parquet);

Keyboard shortcuts

DuckDB by Example