SQL practice exercises with solutions

SELECT first_name, last_name, role
FROM suspects;

Why it works: SELECT picks the columns, FROM picks the table. With no WHERE, every row comes back, which is why all 40 suspects stepped up to the evidence table.

Other databases: Identical in PostgreSQL, MySQL, SQL Server and Oracle.

Expected output (any row order):

SELECT first_name, last_name, bounty_usd
FROM suspects
WHERE bounty_usd > 150000;

Why it works: WHERE keeps only the rows where the condition is true. The suspects who stayed dim are the rows the filter threw away.

Other databases: Identical everywhere. Never write 150,000 with a comma in SQL; the comma separates columns.

Expected output (any row order):

SELECT first_name, first_seen
FROM suspects
WHERE country_id = 1
  AND first_seen >= '2021-01-01';

Why it works: AND needs both conditions to be true. ISO dates (YYYY-MM-DD) sort the same way as text and as dates, so >= works on them.

Other databases: SQLite stores dates as text. PostgreSQL/MySQL/SQL Server have real DATE types but accept '2021-01-01' the same way. Oracle prefers DATE '2021-01-01'.

Expected output (any row order):

SELECT name, port
FROM informants
WHERE port IN ('Singapore', 'Rotterdam');

Why it works: IN (...) is shorthand for port = 'Singapore' OR port = 'Rotterdam'. It reads better as the list grows.

Other databases: Identical everywhere. Text comparison is case-sensitive in PostgreSQL and Oracle, case-insensitive by default in MySQL and SQL Server.

Expected output (any row order):

SELECT first_name, last_name, role
FROM suspects
WHERE role LIKE '%Smuggler%';

Why it works: LIKE '%Smuggler%' matches Smuggler with anything before or after it. That catches Chief Smuggler, Senior Smuggler and Junior Smuggler along with plain Smuggler.

Other databases: LIKE is case-insensitive in SQLite and MySQL but case-sensitive in PostgreSQL (use ILIKE there) and in Oracle.

Expected output (any row order):

SELECT name, street_value
FROM contraband
WHERE street_value BETWEEN 20 AND 60
ORDER BY street_value;

Why it works: BETWEEN a AND b is the same as >= a AND <= b. ORDER BY sorts ascending unless you add DESC. Without ORDER BY, SQL doesn't promise any particular order.

Other databases: Identical everywhere.

Expected output (row order matters):

SELECT first_name, last_name, bounty_usd
FROM suspects
ORDER BY bounty_usd DESC
LIMIT 5;

Why it works: ORDER BY ... DESC puts the biggest values first and LIMIT cuts the list off. LIMIT applies after sorting, which is why you get the top five and not five random rows.

Other databases: PostgreSQL and MySQL use LIMIT 5. SQL Server uses SELECT TOP 5 ... and Oracle/standard SQL use FETCH FIRST 5 ROWS ONLY.

Expected output (row order matters):

SELECT DISTINCT port
FROM informants
ORDER BY port;

Why it works: DISTINCT collapses identical rows into one. Thirty informants work out of only five ports.

Other databases: Identical everywhere.

Expected output (row order matters):

SELECT COUNT(*) AS suspects,
       SUM(bounty_usd) AS total_bounty,
       ROUND(AVG(bounty_usd)) AS avg_bounty
FROM suspects;

Why it works: Aggregate functions summarize the whole table into a single row. AS gives each result column a readable name.

Other databases: ROUND(x) works in all major databases. SQL Server needs ROUND(x, 0), and its AVG of an integer column returns an integer, so cast to DECIMAL first when you need the exact average.

Expected output (any row order):

SELECT country_id, COUNT(*) AS headcount
FROM suspects
WHERE country_id IS NOT NULL
GROUP BY country_id;

Why it works: GROUP BY splits the rows into buckets and the aggregate runs once per bucket. Each tower is one bucket. NULL is never equal to anything, so you test it with IS NULL / IS NOT NULL.

Other databases: Identical everywhere.

Expected output (any row order):

SELECT s.first_name, s.last_name, c.name AS country
FROM suspects s
JOIN countries c ON c.country_id = s.country_id;

Why it works: An INNER JOIN pairs rows that match on the ON condition and drops rows with no partner. That's why the two hired guns (NULL country_id) disappear. The beams show each suspect linked to their country.

Other databases: Identical everywhere. JOIN on its own means INNER JOIN.

Expected output (any row order):

SELECT c.name, COUNT(s.suspect_id) AS headcount
FROM countries c
LEFT JOIN suspects s ON s.country_id = c.country_id
GROUP BY c.country_id, c.name;

Why it works: LEFT JOIN keeps every row from the left table even with no match, filling the right side with NULL. COUNT(column) skips NULLs, so Germany correctly gets 0. COUNT(*) would count its one NULL-filled row.

Other databases: Identical everywhere. Some databases require every non-aggregated column in GROUP BY, so grouping by both id and name is the portable habit.

Expected output (any row order):

SELECT first_name, last_name
FROM suspects
WHERE phone IS NULL;

Why it works: NULL means unknown, so NULL = NULL isn't true; it's unknown too. IS NULL is the only test that works.

Other databases: Identical everywhere. In Oracle an empty string '' also counts as NULL; elsewhere it doesn't.

Expected output (any row order):

SELECT first_name, bounty_usd
FROM suspects
WHERE bounty_usd > (SELECT AVG(bounty_usd) FROM suspects);

Why it works: A scalar subquery returns a single value, and you can use it anywhere a number would go. It runs first, then the outer query compares each bounty against it.

Other databases: Identical everywhere.

Expected output (any row order):

SELECT first_name, bounty_usd,
       CASE WHEN bounty_usd >= 130000 THEN 'Most Wanted'
            WHEN bounty_usd >= 80000 THEN 'Wanted'
            ELSE 'Watch' END AS tier
FROM suspects;

Why it works: CASE is SQL's if/else. It checks the WHEN conditions top to bottom and stops at the first true one. That's why the order of the thresholds matters.

Other databases: Identical everywhere. SQL Server also has IIF() and MySQL has IF(); CASE works in all of them.

Expected output (any row order):

SELECT strftime('%Y-%m', seized_date) AS month, COUNT(*) AS seizures
FROM seizures
WHERE seized_date >= '2024-01-01' AND seized_date < '2025-01-01'
GROUP BY month
ORDER BY month;

Why it works: You can group by a computed value. The month string sorts correctly because the year comes first and the month is zero-padded.

Other databases: PostgreSQL: to_char(seized_date, 'YYYY-MM'). MySQL: DATE_FORMAT(seized_date, '%Y-%m'). SQL Server: FORMAT(seized_date, 'yyyy-MM'). Oracle: TO_CHAR(seized_date, 'YYYY-MM').

Expected output (row order matters):

SELECT i.name, SUM(si.quantity * c.street_value) AS total_value
FROM informants i
JOIN seizures s ON s.informant_id = i.informant_id
JOIN seizure_items si ON si.seizure_id = s.seizure_id
JOIN contraband c ON c.item_id = si.item_id
GROUP BY i.informant_id, i.name
HAVING SUM(si.quantity * c.street_value) > 500
ORDER BY total_value DESC;

Why it works: WHERE filters rows before grouping. HAVING filters groups after. Four tables joined in a chain give one row per item seized, and SUM rolls them up per informant.

Other databases: Identical everywhere. PostgreSQL and SQL Server don't let you use the alias total_value in HAVING; repeating the expression is portable.

Expected output (row order matters):

SELECT name, port
FROM informants
WHERE informant_id NOT IN (SELECT informant_id FROM seizures);

Why it works: NOT IN with a subquery keeps rows whose value never appears in the list. LEFT JOIN ... IS NULL is the other common way to find rows with no partner.

Other databases: Identical everywhere. Careful: if the subquery can return a NULL, NOT IN returns nothing at all. NOT EXISTS doesn't have that trap.

Expected output (any row order):

SELECT first_name, country_id, bounty_usd,
       RANK() OVER (PARTITION BY country_id ORDER BY bounty_usd DESC) AS country_rank
FROM suspects
WHERE country_id IS NOT NULL;

Why it works: Window functions compute across related rows without collapsing them like GROUP BY does. RANK gives ties the same number (Chris and Felix both carry 118,000) and then skips the next one.

Other databases: Window functions work in PostgreSQL, SQL Server, Oracle, MySQL 8+ and SQLite 3.25+. MySQL 5.7 doesn't have them.

Expected output (any row order):

WITH ranked AS (
  SELECT s.first_name, s.bounty_usd, c.name AS country,
         ROW_NUMBER() OVER (PARTITION BY s.country_id ORDER BY s.bounty_usd DESC) AS rn
  FROM suspects s
  JOIN countries c ON c.country_id = s.country_id
)
SELECT country, first_name, bounty_usd
FROM ranked
WHERE rn = 1;

Why it works: This is the top-N-per-group pattern. Window results only exist after WHERE runs, so you compute them in a CTE or subquery and filter on the outside.

Other databases: Same in PostgreSQL, SQL Server, Oracle and MySQL 8+. PostgreSQL also has DISTINCT ON; Snowflake/BigQuery/DuckDB have QUALIFY.

Expected output (any row order):

SELECT s.first_name AS suspect, s.bounty_usd,
       b.first_name AS boss, b.bounty_usd AS boss_bounty
FROM suspects s
JOIN suspects b ON b.suspect_id = s.boss_id
WHERE s.bounty_usd > b.bounty_usd;

Why it works: A self-join treats one table as two, with different aliases. Each row pairs a suspect with their boss, so you can compare the two bounties side by side.

Other databases: Identical everywhere.

Expected output (any row order):

WITH RECURSIVE chain AS (
  SELECT suspect_id, first_name, 1 AS level
  FROM suspects WHERE boss_id IS NULL
  UNION ALL
  SELECT s.suspect_id, s.first_name, c.level + 1
  FROM suspects s
  JOIN chain c ON s.boss_id = c.suspect_id
)
SELECT first_name, level FROM chain;

Why it works: A recursive CTE has a starting query (the anchor) and a step that joins back to itself. Each pass adds the next layer of the organization. It stops when a pass finds no new rows.

Other databases: PostgreSQL, MySQL 8+ and SQLite need WITH RECURSIVE. SQL Server and Oracle write plain WITH. Oracle also has CONNECT BY.

Expected output (any row order):

SELECT country_id, first_name, first_seen,
       julianday(first_seen) - julianday(LAG(first_seen) OVER (PARTITION BY country_id ORDER BY first_seen)) AS days_since_prev
FROM suspects
WHERE country_id IS NOT NULL;

Why it works: LAG looks back one row inside the window. The first row in each country has nothing before it, so LAG returns NULL and the subtraction stays NULL.

Other databases: PostgreSQL: first_seen - LAG(first_seen) OVER (...) on DATE columns. SQL Server: DATEDIFF(day, LAG(...), first_seen). MySQL: DATEDIFF(first_seen, LAG(...)).

Expected output (any row order):

SELECT first_name, last_name
FROM suspects s
WHERE NOT EXISTS (
  SELECT 1 FROM case_suspects cs WHERE cs.suspect_id = s.suspect_id
);

Why it works: A correlated subquery depends on the outer row. NOT EXISTS is true when the inner query finds nothing for that suspect, and unlike NOT IN it handles NULLs safely.

Other databases: Identical everywhere.

Expected output (any row order):

WITH daily AS (
  SELECT s.seized_date, SUM(si.quantity * c.street_value) AS seized_value
  FROM seizures s
  JOIN seizure_items si ON si.seizure_id = s.seizure_id
  JOIN contraband c ON c.item_id = si.item_id
  WHERE s.status = 'confirmed'
    AND s.seized_date >= '2024-01-01' AND s.seized_date < '2025-01-01'
  GROUP BY s.seized_date
)
SELECT seized_date, seized_value,
       SUM(seized_value) OVER (ORDER BY seized_date) AS running_total
FROM daily
ORDER BY seized_date;

Why it works: SUM() OVER (ORDER BY ...) is a running total: each row adds up every row up to and including itself. Doing the GROUP BY in a CTE first keeps the two steps easy to read.

Other databases: Same in PostgreSQL, SQL Server, Oracle, MySQL 8+. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit when dates repeat.

Expected output (row order matters):

SELECT c.name
FROM contraband c
JOIN seizure_items si ON si.item_id = c.item_id
JOIN seizures s ON s.seizure_id = si.seizure_id
JOIN informants i ON i.informant_id = s.informant_id
WHERE i.port = 'Marseille'
EXCEPT
SELECT c.name
FROM contraband c
JOIN seizure_items si ON si.item_id = c.item_id
JOIN seizures s ON s.seizure_id = si.seizure_id
JOIN informants i ON i.informant_id = s.informant_id
WHERE i.port = 'Rotterdam';

Why it works: EXCEPT returns rows from the first query that don't appear in the second, and removes duplicates. UNION stacks results and INTERSECT keeps only the rows in both.

Other databases: Oracle calls it MINUS (EXCEPT works from 21c). MySQL added EXCEPT in 8.0.31. PostgreSQL and SQL Server use EXCEPT.

Expected output (any row order):