20 - SQL¶

Previous: 19 - Polars and DuckDB | Index: All guides | Next: 21 - Matplotlib

Quick reference for SQL queries (standard SQL, notes for PostgreSQL / SQLite / MySQL where they differ) plus using SQL from Python and pandas.

Last verified: 2026-09-27. For newer changes, check the Official docs links in the Introduction.

Introduction¶

Before you start¶

You should know: what a table with rows and columns is (any spreadsheet will do). Knowing pandas (18) helps you compare, but is not required.

The problem it solves: a company's data (customers, orders, payments) must be stored safely, shared by many programs and people at once, kept consistent (no order for a customer who does not exist), and searched quickly even with billions of rows. Files like CSV or Excel cannot do any of that reliably.

Before SQL: each database had its own programming interface, and to get data you wrote step-by-step code that walked through records. SQL (1970s, from IBM research on the relational model) lets you describe what you want and leaves how to the database, which is why the same language still works in PostgreSQL, SQLite, SQL Server and cloud warehouses 50 years later.

Think of it like: asking a librarian instead of searching the shelves yourself. You say "all books by this author after 2010, newest first"; the librarian knows the catalogue and fetches them the fastest way.

What is SQL?¶

SQL (Structured Query Language) is the standard language for working with relational databases: systems that store data in tables linked by keys. With SQL you describe what data you want ("average salary per department, highest first") and the database works out how to get it efficiently. The same core language works in PostgreSQL, MySQL, SQLite, SQL Server, Snowflake, BigQuery and more, with small differences.

Why use it?¶

Where business data lives: customers, orders, transactions are almost always in a SQL database.
Handles huge data: databases filter and aggregate millions of rows before sending you the result.
Declarative and readable: a query says what you want, not how to loop.
Reliable: transactions, constraints and permissions keep data correct and safe.
Must-have skill for data analysts, data scientists and backend developers.

Key terms¶

Term	Meaning
Database	A collection of tables managed by a database system
Table / row / column	Data as a grid: records and fields
Primary key	Unique ID of each row
Foreign key	Column that links to another table's primary key
Query	A `SELECT` statement that reads data
JOIN	Combine rows from tables by a key
Index	Structure that makes lookups on a column faster
DDL / DML	Statements that define structure (CREATE) / change data (INSERT)

Where it fits: load query results into 18 - Pandas; run databases with 43 - Docker. Vector search in Postgres: 30 - pgvector; SQL on files: 19 - DuckDB.

Official docs¶

Where to read the latest, authoritative documentation:

Resource	Link
PostgreSQL documentation	https://www.postgresql.org/docs/
SQLite documentation	https://www.sqlite.org/docs.html
MySQL documentation	https://dev.mysql.com/doc/
SQLAlchemy	https://docs.sqlalchemy.org/

Contents¶

Concepts
Query Order
SELECT Basics
WHERE Filters
Sorting and Limiting
Aggregate Functions
GROUP BY and HAVING
JOINs
CASE (If / Else)
NULL Handling
String Functions
Date Functions
Subqueries
CTEs (WITH)
Window Functions
UNION, INTERSECT, EXCEPT
Create Tables
Insert, Update, Delete
Alter and Drop
Indexes and Views
Transactions
SQLite and PostgreSQL CLI
SQL from Python and Pandas
SQL vs Pandas
Troubleshooting
Try It

1. Concepts¶

The building blocks of relational databases. Data lives in tables; keys link tables together. Read once; the rest of the guide uses these terms and example tables.

Term	Meaning
Table	Rows and columns (like a DataFrame)
Primary key	Column that uniquely identifies a row (`id`)
Foreign key	Column that points to a primary key in another table
Query	A `SELECT` statement that reads data
Schema	Structure: tables, columns, types

Example tables used below:

employees(id, name, department_id, salary, hire_date, manager_id)
departments(id, name, city)

Keywords are not case-sensitive (SELECT = select); writing them in UPPERCASE is a convention. End statements with ;.

2. Query Order¶

The order clauses are written vs the order the database runs them. Written SELECT first, but executed FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

Use this when understanding why an alias fails in WHERE, or where a filter belongs.

Written order:

SELECT    columns
FROM      table
JOIN      other_table ON ...
WHERE     row filter
GROUP BY  columns
HAVING    group filter
ORDER BY  columns
LIMIT     n;

Execution order: FROM / JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. That is why you cannot use a SELECT alias inside WHERE.

3. SELECT Basics¶

Reading columns from a table. SELECT columns FROM table, with aliases, calculations and DISTINCT.

Use it in every query starts here.

SELECT * FROM employees;                            -- all columns
SELECT name, salary FROM employees;                 -- some columns
SELECT name AS employee_name FROM employees;        -- column alias
SELECT DISTINCT department_id FROM employees;       -- unique values
SELECT name, salary * 12 AS yearly FROM employees;  -- calculated column
SELECT COUNT(*) FROM employees;                     -- number of rows

Comments: -- one line and /* block */.

4. WHERE Filters¶

Keeping only rows that match conditions. WHERE with comparisons, AND / OR, IN, BETWEEN, LIKE, IS NULL.

Use it in any question about a subset: "orders from 2024", "customers in Berlin".

SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE department_id = 2 AND salary >= 40000;
SELECT * FROM employees WHERE department_id = 1 OR department_id = 3;
SELECT * FROM employees WHERE NOT department_id = 2;
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;   -- inclusive
SELECT * FROM employees WHERE name LIKE 'A%';       -- starts with A
SELECT * FROM employees WHERE name LIKE '%son';     -- ends with son
SELECT * FROM employees WHERE name LIKE '_a%';      -- 2nd letter is a
SELECT * FROM employees WHERE name ILIKE 'a%';      -- case-insensitive (PostgreSQL)
SELECT * FROM employees WHERE manager_id IS NULL;   -- never use = NULL

Operators: = <> (or !=) > < >= <=. Text values use single quotes: 'Berlin'.

5. Sorting and Limiting¶

Ordering results and returning only some rows. ORDER BY col [DESC], then LIMIT n OFFSET m.

Use it for top N lists, latest records, paging results in an app.

SELECT * FROM employees ORDER BY salary;                    -- ascending
SELECT * FROM employees ORDER BY salary DESC;               -- descending
SELECT * FROM employees ORDER BY department_id, salary DESC;
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;       -- top 5
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;     -- page 3 (rows 21 to 30)

SQL Server: SELECT TOP 5 .... Oracle / standard: FETCH FIRST 5 ROWS ONLY.

6. Aggregate Functions¶

Calculating one value from many rows. COUNT, SUM, AVG, MIN, MAX over the selected rows.

Use it for totals and averages for a whole table or filtered subset.

SELECT
    COUNT(*)              AS rows_total,
    COUNT(manager_id)     AS with_manager,      -- ignores NULL
    COUNT(DISTINCT department_id) AS departments,
    SUM(salary)           AS total,
    AVG(salary)           AS average,
    MIN(salary)           AS lowest,
    MAX(salary)           AS highest,
    ROUND(AVG(salary), 2) AS avg_rounded
FROM employees;

7. GROUP BY and HAVING¶

Aggregating per group and filtering groups. GROUP BY makes one row per group; HAVING filters on the aggregated values.

Use it to answer questions like "Sales per region", "departments with more than 10 employees".

-- One row per department
SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Filter groups (HAVING) vs filter rows (WHERE)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'         -- rows first
GROUP BY department_id
HAVING AVG(salary) > 50000              -- then groups
ORDER BY avg_salary DESC;

Every column in SELECT must be in GROUP BY or inside an aggregate function.

8. JOINs¶

Combining rows from two or more tables by a key. JOIN ... ON a.key = b.key; the join type decides what happens to non-matching rows.

Use it for data spread across tables: employees + departments, orders + customers.

SELECT e.name, d.name AS department
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

Join	Returns
`INNER JOIN`	Only rows with a match in both tables
`LEFT JOIN`	All rows from left, matched rows from right (NULL if none)
`RIGHT JOIN`	All rows from right, matched rows from left
`FULL OUTER JOIN`	All rows from both (not in MySQL)
`CROSS JOIN`	Every combination

-- Employees without a department (anti-join)
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

-- Self join: employee and their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Several joins
SELECT e.name, d.name, p.title
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.owner_id = e.id;

9. CASE (If / Else)¶

If / else logic inside a query. CASE WHEN condition THEN value ... ELSE value END.

Use it for categorising values (salary bands), conditional counts / sums.

SELECT name, salary,
    CASE
        WHEN salary >= 80000 THEN 'high'
        WHEN salary >= 50000 THEN 'medium'
        ELSE 'low'
    END AS salary_band
FROM employees;

-- Conditional count
SELECT
    COUNT(CASE WHEN salary > 50000 THEN 1 END) AS above_50k,
    SUM(CASE WHEN department_id = 1 THEN salary ELSE 0 END) AS dept1_total
FROM employees;

10. NULL Handling¶

Working with missing values. IS NULL to test, COALESCE for defaults, NULLIF to avoid division by zero.

Use it for optional fields, left joins that produce NULLs, safe ratios.

WHERE manager_id IS NULL
WHERE manager_id IS NOT NULL
SELECT COALESCE(manager_id, 0) FROM employees;      -- first non-NULL value
SELECT NULLIF(bonus, 0) FROM employees;             -- NULL if bonus = 0 (avoid divide by zero)
SELECT salary / NULLIF(hours, 0) FROM employees;

Any comparison with NULL (= NULL, <> NULL) is unknown, not true. Aggregates (except COUNT(*)) ignore NULL.

11. String Functions¶

Changing and extracting text. Functions like UPPER, TRIM, SUBSTRING, CONCAT (names vary slightly per database).

Use it for cleaning names, building labels, matching inconsistent text.

UPPER(name) ; LOWER(name)
LENGTH(name)                            -- LEN() in SQL Server
TRIM(name)
SUBSTRING(name, 1, 3)                   -- first 3 chars (SUBSTR in SQLite)
REPLACE(name, 'a', 'b')
CONCAT(first_name, ' ', last_name)      -- or first_name || ' ' || last_name
POSITION('a' IN name)                   -- INSTR(name, 'a') in SQLite / MySQL
LEFT(name, 3) ; RIGHT(name, 3)

12. Date Functions¶

Extracting parts of dates and date arithmetic. Database-specific functions (EXTRACT, DATE_TRUNC, STRFTIME, DATE_ADD).

Use it for monthly reports, filtering the last 30 days, grouping by year.

-- PostgreSQL
CURRENT_DATE ; NOW()
EXTRACT(YEAR FROM hire_date)
DATE_TRUNC('month', hire_date)                  -- first day of month
hire_date + INTERVAL '30 days'
AGE(CURRENT_DATE, hire_date)
TO_CHAR(hire_date, 'YYYY-MM')

-- SQLite
DATE('now') ; DATETIME('now')
STRFTIME('%Y', hire_date)                       -- year
DATE(hire_date, '+30 days')

-- MySQL
CURDATE() ; NOW() ; YEAR(hire_date)
DATE_ADD(hire_date, INTERVAL 30 DAY)
DATE_FORMAT(hire_date, '%Y-%m')

13. Subqueries¶

A query inside another query. Put (SELECT ...) in WHERE, FROM or SELECT.

Use it for comparing to an aggregate ("above average"), or filtering by another table.

-- In WHERE: above average salary
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- With IN
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE city = 'Berlin');

-- EXISTS
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

-- In FROM (derived table)
SELECT AVG(headcount) FROM (
    SELECT department_id, COUNT(*) AS headcount
    FROM employees GROUP BY department_id
) AS t;

14. CTEs (WITH)¶

Named temporary result sets defined before the main query. WITH name AS (SELECT ...) then use name like a table.

Use it for complex queries in readable steps; replaces deeply nested subqueries.

Named temporary result; easier to read than nested subqueries.

WITH dept_stats AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, s.avg_salary
FROM employees e
JOIN dept_stats s ON e.department_id = s.department_id
WHERE e.salary > s.avg_salary;

Several CTEs: WITH a AS (...), b AS (...) SELECT ...

15. Window Functions¶

Calculations across related rows while keeping every row. function() OVER (PARTITION BY ... ORDER BY ...).

Use it for rankings, top N per group, running totals, comparing with the previous row.

Calculate across related rows without collapsing them (unlike GROUP BY).

SELECT name, department_id, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC)                            AS dense_rnk,
    AVG(salary)  OVER (PARTITION BY department_id)                      AS dept_avg,
    SUM(salary)  OVER (ORDER BY hire_date)                              AS running_total,
    LAG(salary)  OVER (ORDER BY hire_date)                              AS prev_salary,
    LEAD(salary) OVER (ORDER BY hire_date)                              AS next_salary,
    NTILE(4)     OVER (ORDER BY salary)                                 AS quartile
FROM employees;

Top N per group:

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

Function	Ties
`ROW_NUMBER()`	1, 2, 3, 4 (always unique)
`RANK()`	1, 2, 2, 4 (gap after tie)
`DENSE_RANK()`	1, 2, 2, 3 (no gap)

Moving average: AVG(sales) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

16. UNION, INTERSECT, EXCEPT¶

Combining results of two queries vertically. UNION (unique rows), UNION ALL (all rows), INTERSECT, EXCEPT.

Use it for merging similar tables (customers + suppliers), finding rows in one list but not another.

SELECT city FROM customers
UNION                       -- combine, remove duplicates
SELECT city FROM suppliers;

UNION ALL                   -- combine, keep duplicates (faster)
INTERSECT                   -- in both
EXCEPT                      -- in first but not second (MINUS in Oracle)

Both queries need the same number of columns with compatible types.

17. Create Tables¶

Defining a new table and its columns. CREATE TABLE with column types and constraints (PRIMARY KEY, NOT NULL, REFERENCES).

Use it for setting up a new database or storing processed results.

CREATE TABLE departments (
    id    SERIAL PRIMARY KEY,               -- INTEGER PRIMARY KEY AUTOINCREMENT in SQLite
    name  VARCHAR(100) NOT NULL UNIQUE,
    city  VARCHAR(100)
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    salary        NUMERIC(10, 2) CHECK (salary >= 0),
    hire_date     DATE DEFAULT CURRENT_DATE,
    manager_id    INTEGER REFERENCES employees(id)
);

CREATE TABLE IF NOT EXISTS logs (id SERIAL PRIMARY KEY, msg TEXT);
CREATE TABLE top_earners AS SELECT * FROM employees WHERE salary > 80000;

Common types: INTEGER, BIGINT, NUMERIC(p, s), REAL / FLOAT, VARCHAR(n), TEXT, BOOLEAN, DATE, TIMESTAMP, JSON / JSONB (PostgreSQL).

18. Insert, Update, Delete¶

Adding, changing and removing rows. INSERT INTO, UPDATE ... SET ... WHERE, DELETE FROM ... WHERE.

Use it for loading data, correcting records, removing test data.

INSERT INTO departments (name, city) VALUES ('Data', 'Berlin');
INSERT INTO departments (name, city) VALUES ('HR', 'Munich'), ('IT', 'Hamburg');
INSERT INTO archive SELECT * FROM employees WHERE hire_date < '2015-01-01';

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 2;
UPDATE employees SET salary = 60000, department_id = 3 WHERE id = 7;

DELETE FROM employees WHERE id = 7;
TRUNCATE TABLE logs;                    -- delete all rows fast

-- Upsert (PostgreSQL / SQLite)
INSERT INTO departments (id, name) VALUES (1, 'Data')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

UPDATE or DELETE without WHERE changes EVERY row. Run the same WHERE with SELECT first.

19. Alter and Drop¶

Changing or removing a table's structure. ALTER TABLE adds / drops / renames columns; DROP TABLE removes the table.

Use this when the schema needs a new column, or an old table is no longer used.

ALTER TABLE employees ADD COLUMN email VARCHAR(200);
ALTER TABLE employees DROP COLUMN email;
ALTER TABLE employees RENAME COLUMN name TO full_name;
ALTER TABLE employees RENAME TO staff;
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);   -- PostgreSQL

DROP TABLE logs;
DROP TABLE IF EXISTS logs;

20. Indexes and Views¶

Speed-ups for queries and saved queries. An index is a lookup structure on columns; a view is a stored SELECT.

Use it for slow filters / joins on large tables (index); reusable reports (view).

CREATE INDEX idx_emp_dept ON employees(department_id);      -- faster filters / joins
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
DROP INDEX idx_emp_dept;

CREATE VIEW dept_summary AS
SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id;

SELECT * FROM dept_summary;             -- use like a table

EXPLAIN SELECT * FROM employees WHERE department_id = 2;          -- query plan
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 2;  -- plan + real timing (PostgreSQL)

21. Transactions¶

Several changes that succeed or fail together. BEGIN, run statements, then COMMIT or ROLLBACK.

Use it for money transfers, multi-table updates where partial changes would corrupt data.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;             -- save both
-- ROLLBACK;        -- or undo both

22. SQLite and PostgreSQL CLI¶

Command-line clients for SQLite and PostgreSQL. sqlite3 file.db or psql -h host -U user -d db; dot / backslash commands inspect the database.

Use it for quick checks on a server or container without a GUI tool.

SQLite (file-based, no server):

sqlite3 mydb.db                 open / create database
.tables                         list tables
.schema employees               show CREATE statement
.headers on                     show column names
.mode column                    aligned output (also: csv, table, markdown)
.import data.csv employees      import CSV (use .mode csv first)
.output result.csv              send output to file
.quit                           exit

PostgreSQL (psql):

psql -h localhost -U postgres -d mydb     connect
\l                              list databases
\c mydb                         switch database
\dt                             list tables
\d employees                    describe table
\dn                             list schemas
\du                             list users
\x                              toggle expanded output
\i script.sql                   run SQL file
\copy employees TO 'emp.csv' CSV HEADER    export CSV
\q                              quit

Postgres in Docker: see 43 - Docker, section "Useful Ready-Made Containers".

23. SQL from Python and Pandas¶

Running SQL from Python and moving data between SQL and pandas. sqlite3 / SQLAlchemy connections; pd.read_sql and df.to_sql.

Use it for pulling data from a database into an analysis, or saving results back.

import sqlite3

import pandas as pd
from sqlalchemy import create_engine

# sqlite3 (standard library)
conn = sqlite3.connect("mydb.db")
cur = conn.cursor()
cur.execute("SELECT name FROM employees WHERE salary > ?", (50000,))   # ? = parameter
rows = cur.fetchall()
conn.commit()
conn.close()

# pandas + SQLAlchemy (pip install sqlalchemy psycopg2-binary)
engine = create_engine("sqlite:///mydb.db")
# engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/mydb")

df = pd.read_sql("SELECT * FROM employees", engine)
df = pd.read_sql("SELECT * FROM employees WHERE salary > :min", engine, params={"min": 50000})
df.to_sql("employees_clean", engine, if_exists="replace", index=False)   # or "append"

Always pass values as parameters (?, :name, %s), never with f-strings; string formatting allows SQL injection.

Keep connection strings (with passwords) in environment variables, not in code.

24. SQL vs Pandas¶

The pandas equivalent of each SQL operation. Find the SQL clause on the left, use the pandas code on the right.

Use this when you know how to do it in SQL but not in pandas (or the reverse).

SQL	Pandas
`SELECT a, b FROM t`	`df[["a", "b"]]`
`WHERE a > 5`	`df[df["a"] > 5]`
`WHERE a IN (1, 2)`	`df[df["a"].isin([1, 2])]`
`WHERE a IS NULL`	`df[df["a"].isna()]`
`ORDER BY a DESC`	`df.sort_values("a", ascending=False)`
`LIMIT 5`	`df.head(5)`
`SELECT DISTINCT a`	`df["a"].unique()` / `df.drop_duplicates("a")`
`COUNT(*)`	`len(df)`
`GROUP BY a` + `AVG(b)`	`df.groupby("a")["b"].mean()`
`HAVING AVG(b) > 10`	`g = df.groupby("a")["b"].mean(); g[g > 10]`
`INNER JOIN ... ON`	`pd.merge(df1, df2, on="key")`
`LEFT JOIN`	`pd.merge(df1, df2, on="key", how="left")`
`UNION ALL`	`pd.concat([df1, df2])`
`CASE WHEN`	`np.where(...)` / `np.select(...)`
`ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)`	`df.groupby("a")["b"].rank(method="first")`
`SUM(b) OVER (ORDER BY d)`	`df.sort_values("d")["b"].cumsum()`

See 18 - Pandas.

25. Troubleshooting¶

Error	Fix
`column must appear in the GROUP BY clause`	Add the column to `GROUP BY` or wrap it in an aggregate
`column "x" does not exist` (with alias)	Aliases cannot be used in `WHERE`; repeat the expression or use a CTE
`column reference "id" is ambiguous`	Prefix with the table alias: `e.id`
`aggregate functions are not allowed in WHERE`	Use `HAVING`
`WHERE x = NULL` returns nothing	Use `IS NULL`
Join returns too many rows	Join key is not unique; duplicated matches multiply rows
`division by zero`	`x / NULLIF(y, 0)`
Integer division gives 0	Cast: `x * 1.0 / y` or `CAST(x AS NUMERIC) / y`
Text with `'` breaks the query	Double it: `'O''Brien'` (better: use parameters)
`relation "table" does not exist`	Wrong database / schema, or name case: PostgreSQL lowercases unquoted names
Query is slow	`EXPLAIN`, add an index on filter / join columns, avoid `SELECT *`

26. Try It¶

Short exercises to practise this guide. Try each task yourself first, then open the solution.

Use it right after reading the guide, or later as a quick self-test.

Exercise 1: Top 3 per department¶

Return the three highest-paid employees in every department.

Solution

WITH ranked AS (
    SELECT name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

Exercise 2: Filter groups¶

List departments whose average salary is above 50,000.

Solution

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

Exercise 3: Missing matches¶

Find employees whose department_id has no matching department.

Solution

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Exercise 4: Running total¶

Show daily sales with a running total ordered by date.

Solution

SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales;

Previous: 19 - Polars and DuckDB | Index: All guides | Next: 21 - Matplotlib