Worked solutions
SQL exercises with solutions
Five exercises from the bank, solved in full: the task, the tables, the query and why it works. Each query is the exercise's reference solution and runs in the browser editor on the same data.
EasyFiltering and sorting
1. Comparison Operators
From the movies table, list the title, year, rating and genre of every movie rated above 7.5 and released between 2010 and 2016, both years included. Highest rating first.
Tables
movies(id, title, year, genre, rating, votes, revenue_millions, runtime, director)
Solution
SELECT
title,
year,
rating,
genre
FROM movies
WHERE rating > 7.5 AND year BETWEEN 2010 AND 2016
ORDER BY rating DESC;
Why it works
WHERE keeps a row only when both conditions hold, and BETWEEN includes both of its ends, so 2010 and 2016 are in. ORDER BY runs after the filter, so only the rows that survived are sorted, and DESC puts the highest rating at the top.
MediumGROUP BY and HAVING
2. GROUP BY + HAVING
Find the genres that have at least 5 movies and an average rating above 6.5. Show the genre, its movie count, its average rating to two decimals and its total revenue, best-rated genre first.
Tables
movies(id, title, year, genre, rating, votes, revenue_millions, runtime, director)
Solution
SELECT
genre,
COUNT(*) AS movie_count,
ROUND(AVG(rating), 2) AS avg_rating,
ROUND(SUM(revenue_millions), 0) AS total_revenue
FROM movies
GROUP BY genre
HAVING COUNT(*) >= 5 AND AVG(rating) > 6.5
ORDER BY avg_rating DESC;
Why it works
WHERE cannot see COUNT(*) or AVG(rating): it runs before the rows are grouped. HAVING runs after GROUP BY, so it can drop a whole genre by its size and its average. The same two aggregates sit in the SELECT list, which makes the result easy to check by eye.
MediumJOIN
3. LEFT JOIN: Keep Everyone
Show every customer with their membership, the number of orders they placed and the amount they spent, including the customers who never ordered (0 for both). Biggest spender first, then by name.
Tables
customers(customer_id, name, email, signup_date, membership, total_orders)
orders(order_id, customer_id, product, category, quantity, price, total, order_date, country, status)
Solution
SELECT
c.name,
c.membership,
COUNT(o.order_id) AS order_count,
COALESCE(ROUND(SUM(o.total), 2), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.membership
ORDER BY total_spent DESC, c.name;
Why it works
LEFT JOIN keeps every customer and fills the order columns with NULL where nothing matches. COUNT(o.order_id) skips those NULLs and returns 0, where COUNT(*) would count the empty row as 1. SUM over no orders is NULL, so COALESCE turns it into 0.
MediumCTE
4. Your First CTE (WITH Clause)
In a CTE named dept_stats, work out each department's headcount and its average salary, rounded. Then list the departments from it, highest average salary first.
Tables
employees(emp_id, name, department, position, salary, hire_date, manager_id, performance_rating)
Solution
WITH dept_stats AS (
SELECT
department,
COUNT(*) AS headcount,
ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
department,
headcount,
avg_salary
FROM dept_stats
ORDER BY avg_salary DESC;
Why it works
WITH gives the grouped query a name, and the final SELECT reads from dept_stats as if it were a table. It runs the same as a subquery in FROM; the gain is that each step has a name and the query reads top to bottom. A later step that needs the same figures reuses the name instead of repeating the query.
MediumWindow function
5. Window Functions: ROW_NUMBER
Number the employees inside each department by salary, the highest paid as 1, with the name breaking ties. Show name, department, salary and row_num, sorted by department and then by row_num.
Tables
employees(emp_id, name, department, position, salary, hire_date, manager_id, performance_rating)
Solution
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS row_num
FROM employees
ORDER BY department, row_num;
Why it works
ROW_NUMBER() numbers rows without collapsing them, so every employee stays in the result. PARTITION BY department restarts the count in each department, and the ORDER BY inside OVER decides who gets 1. The name in that ORDER BY breaks salary ties, so the numbering is the same on every run.
Every other exercise shows its worked solution in the app once you have solved it. Open the exercise bank