All exercises use this shared schema. Create it before you start:
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT REFERENCES departments(id),
salary DECIMAL(10,2),
hire_date DATE,
job_title VARCHAR(100),
manager_id INT REFERENCES employees(id)
);
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
total DECIMAL(10,2),
created_at DATE
);
-- Sample inserts
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR'),(4,'Marketing');
INSERT INTO employees VALUES
(1,'Alice',1,120000,'2020-03-15','Senior Engineer',NULL),
(2,'Bob',1,95000,'2021-07-01','Engineer',1),
(3,'Carol',2,80000,'2019-11-01','Sales Rep',NULL),
(4,'Dave',2,85000,'2023-02-20','Sales Rep',3),
(5,'Eve',2,90000,'2022-05-10','Sales Lead',3),
(6,'Frank',3,70000,'2021-01-05','HR Manager',NULL),
(7,'Grace',1,105000,'2022-09-01','Engineer',1),
(8,'Henry',4,75000,'2023-01-10','Marketing Exec',NULL),
(9,'Iris',2,78000,'2022-04-15','Sales Rep',5),
(10,'Jack',1,88000,'2023-08-01','Junior Engineer',2);Beginner Exercises
Exercise 1 – Employees in Engineering
Task: Select all employees in the Engineering department. Show their name, salary, and hire date.
JOIN the employees table to departments, then filter with WHERE.
SELECT e.name, e.salary, e.hire_date
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering'
ORDER BY e.salary DESC;Exercise 2 – Top 5 Highest-Paid Employees
Task: Find the top 5 highest-paid employees. Show name, department name, and salary.
ORDER BY salary DESC, then LIMIT 5.
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY e.salary DESC
LIMIT 5;Exercise 3 – Employee Count per Department
Task: Count the number of employees in each department. Show department name and headcount, sorted by headcount descending.
GROUP BY department_id after joining departments. Use COUNT(*).
SELECT d.name AS department, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY headcount DESC;Exercise 4 – Employees Hired in 2023
Task: Find all employees hired in the year 2023. Show their name and hire date.
Use EXTRACT(YEAR FROM hire_date) = 2023 in PostgreSQL or YEAR(hire_date) = 2023 in MySQL.
-- PostgreSQL
SELECT name, hire_date FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2023
ORDER BY hire_date;
-- MySQL
SELECT name, hire_date FROM employees
WHERE YEAR(hire_date) = 2023
ORDER BY hire_date;Exercise 5 – Distinct Job Titles
Task: List all distinct job titles across the company, sorted alphabetically.
SELECT DISTINCT job_title
FROM employees
ORDER BY job_title;Intermediate Exercises
Exercise 6 – Employees Earning Above Department Average
Task: Find employees whose salary is above the average salary of their own department. Show name, department, salary, and the department average.
Use a correlated subquery or a CTE that first calculates the average salary per department, then join it back to employees.
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.name, d.name AS department, e.salary,
ROUND(da.avg_salary, 2) AS dept_average
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN dept_avg da ON da.department_id = e.department_id
WHERE e.salary > da.avg_salary
ORDER BY d.name, e.salary DESC;Exercise 7 – Rank Employees by Salary Within Each Department
Task: Show each employee's name, department, salary, and their salary rank within their department (1 = highest paid). Use a window function.
Use RANK() or DENSE_RANK() with PARTITION BY department_id ORDER BY salary DESC.
SELECT
e.name,
d.name AS department,
e.salary,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY d.name, salary_rank;Exercise 8 – Customers with No Orders
Task: Find all customers who have never placed an order. (For this exercise, populate the customers and orders tables with a few rows of sample data.)
Use a LEFT JOIN from customers to orders, then filter WHERE orders.id IS NULL.
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;Exercise 9 – Departments with More Than 3 Employees (using CTE)
Task: Write a CTE that counts employees per department, then SELECT from it to return only departments with more than 3 employees.
WITH dept_counts AS (
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
)
SELECT d.name AS department, dc.headcount
FROM dept_counts dc
JOIN departments d ON d.id = dc.department_id
WHERE dc.headcount > 3
ORDER BY dc.headcount DESC;Exercise 10 – Give Sales a 10% Raise (Conditional UPDATE)
Task: Give all employees in the Sales department who were hired before 2022 a 10% salary raise.
Use UPDATE with a subquery or join to identify the correct employees. Always run a SELECT first to verify which rows will be affected.
-- Verify first
SELECT e.name, e.salary, e.hire_date
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Sales' AND e.hire_date < '2022-01-01';
-- Then update (MySQL syntax)
UPDATE employees e
JOIN departments d ON d.id = e.department_id
SET e.salary = e.salary * 1.10
WHERE d.name = 'Sales'
AND e.hire_date < '2022-01-01';
-- PostgreSQL syntax
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales')
AND hire_date < '2022-01-01';Advanced Exercises
Exercise 11 – Running Total of Monthly Revenue
Task: Using the orders table, compute the total revenue per month AND the running total of revenue up to and including that month. Show month, monthly revenue, and cumulative revenue.
First aggregate to month with DATE_TRUNC / DATE_FORMAT, then use SUM() OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
WITH monthly AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS monthly_revenue
FROM orders
GROUP BY 1
)
SELECT
month,
monthly_revenue,
SUM(monthly_revenue) OVER (
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM monthly
ORDER BY month;Exercise 12 – Second-Highest Salary
Task: Find the second-highest salary in the employees table. Your solution should work even if there are duplicate salary values at the top.
Use DENSE_RANK() to assign ranks and then filter for rank = 2. Avoid LIMIT/OFFSET — it's unreliable with duplicates.
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2;Exercise 13 – Recursive CTE: Employee Hierarchy
Task: Use a recursive CTE to build the full management hierarchy starting from the top-level manager (employees with no manager_id). Show each employee's name, their manager's name, and their depth in the hierarchy.
Anchor: WHERE manager_id IS NULL. Recursive part: JOIN employees on manager_id = previous level's id. Add a depth counter.
WITH RECURSIVE org_chart AS (
-- Anchor: top-level managers
SELECT id, name, manager_id, 1 AS depth,
name::TEXT AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: each employee under a manager
SELECT e.id, e.name, e.manager_id, oc.depth + 1,
oc.path || ' > ' || e.name
FROM employees e
JOIN org_chart oc ON oc.id = e.manager_id
)
SELECT
REPEAT(' ', depth - 1) || name AS employee,
depth,
path
FROM org_chart
ORDER BY path;Exercise 14 – Pivot: Headcount by Department and Hire Year
Task: Create a pivot table showing employee headcount broken down by hire year (columns: 2019, 2020, 2021, 2022, 2023) with one row per department.
Use conditional aggregation: SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2020 THEN 1 ELSE 0 END) for each year column.
SELECT
d.name AS department,
SUM(CASE WHEN EXTRACT(YEAR FROM e.hire_date) = 2019 THEN 1 ELSE 0 END) AS "2019",
SUM(CASE WHEN EXTRACT(YEAR FROM e.hire_date) = 2020 THEN 1 ELSE 0 END) AS "2020",
SUM(CASE WHEN EXTRACT(YEAR FROM e.hire_date) = 2021 THEN 1 ELSE 0 END) AS "2021",
SUM(CASE WHEN EXTRACT(YEAR FROM e.hire_date) = 2022 THEN 1 ELSE 0 END) AS "2022",
SUM(CASE WHEN EXTRACT(YEAR FROM e.hire_date) = 2023 THEN 1 ELSE 0 END) AS "2023"
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY d.name;Exercise 15 – Find and Delete Duplicate Rows
Task: The employees table might have duplicate rows (same name and department_id). Find any duplicates, then delete all but the row with the lowest id.
Use ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) to assign row numbers within each duplicate group. Delete rows where row_num > 1.
-- Step 1: Identify duplicates
WITH numbered AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY name, department_id
ORDER BY id
) AS rn
FROM employees
)
SELECT id FROM numbered WHERE rn > 1;
-- Step 2: Delete duplicates (keep the lowest id in each group)
WITH numbered AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY name, department_id
ORDER BY id
) AS rn
FROM employees
)
DELETE FROM employees
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);FAQ
The easiest options: (1) db-fiddle.com — paste the schema and query, choose PostgreSQL or MySQL, run in browser. (2) sqlfiddle.com — similar browser-based SQL playground. (3) Install PostgreSQL locally (free, runs on Windows/Mac/Linux) and use pgAdmin or psql. (4) DBeaver is a free universal database client that connects to both PostgreSQL and MySQL — great for local practice.
Yes — recursive CTEs using WITH RECURSIVE are supported from MySQL 8.0 onwards. Earlier versions (5.7 and below) do not support them. In MySQL 8.0+, the syntax is identical to PostgreSQL for basic cases. If you're on MySQL 5.7, you would need to use a stored procedure with a loop, or use a different approach like a closure table pattern for hierarchical data.
Run the provided solution and compare output with yours. For exercises without a fixed expected row count (like the hierarchy or pivot exercises), verify the structure is correct: all rows are present, values match expectations. For UPDATE/DELETE exercises, always SELECT first with the same WHERE condition to see which rows would be affected before running the destructive statement.
Practice writing SQL by hand — not copy-pasting. Read a question, think about the approach, write the query from scratch. Check it against the solution. Review the interview questions page to understand the conceptual answers. Then do timed practice on platforms like LeetCode (SQL section), HackerRank (SQL challenges), or Mode Analytics' SQL tutorial. Focus on JOINs, aggregation + HAVING, window functions (ROW_NUMBER, RANK, LAG), and CTEs — these appear in almost every data/backend SQL interview.