SQL Practice Problems
Concept
Fifty-one progressively harder problems. All use the course sample database (departments, employees, customers, products, orders, order_items) unless another table is defined in the problem. Solutions use standard SQL / PostgreSQL syntax; dialect notes appear where needed.
For each problem: Problem, Tables, Expected output, Hint, Solution. Try each problem before reading the hint and solution.
Why It Matters
Fluency comes from repetition. The expected outputs let you check your answers exactly.
Syntax
Use whatever you have learned so far; later levels expect window functions and CTEs.
Level 1 — Basic SELECT, WHERE, ORDER BY, INSERT, UPDATE, DELETE
Problem: List all departments ordered alphabetically by name. Tables: departments Expected output:
Hint: ORDER BY a text column. Solution: Problem: List employees hired after 31 December 2020, oldest hire first. Tables: employees Expected output:
Hint: Compare with a DATE literal. Solution: Problem: Show Electronics products priced below 20000. Tables: products Expected output:
Hint: Two conditions joined with AND. Solution: Problem: List customers from India sorted by name, with their city. Tables: customers Expected output:
Hint: Filter on country. Solution: Problem: List orders that are neither delivered nor cancelled. Tables: orders Expected output:
Hint: NOT IN with a literal list is safe here (no NULLs). Solution:
Problem: List employees whose first name starts with a vowel. Tables: employees Expected output:
Hint: Use LEFT/SUBSTRING with IN, or several LIKE conditions. Solution:
Problem: Find customers with no email address. Tables: customers Expected output: Hint: Not = NULL. Solution:
Problem: Show the two highest-paid employees. Tables: employees Expected output:
Hint: ORDER BY DESC with a row limit. Solution: Problem: Show products whose stock value (price × stock) exceeds 300000, highest first. Tables: products Expected output:
Hint: Repeat the expression in WHERE (aliases are not visible there). Solution:
Problem: Insert a new department "Quality" located in Chennai with ID 5, then count departments. Tables: departments Expected output:
Hint: Always list the columns. Solution: Problem: Set the stock of "Standing Desk" to 10. Tables: products Expected output: 1 row updated; SELECT stock FROM products WHERE product_id = 106 returns 10. Hint: Preview with SELECT first; filter by the key. Solution:
Problem: Delete the order items that belong to cancelled orders. Tables: order_items, orders Expected output: 1 row deleted (order 1004, product 102). Hint: Use a subquery on orders. Solution:
Problem: List the distinct job titles that contain the word "Manager". Tables: employees Expected output:
Hint: DISTINCT + LIKE. Solution: Problem: List employees earning between 80000 and 100000 (inclusive) who are not in department 1. Employees without a department count as "not in department 1". Tables: employees Expected output:
Hint: dept_id <> 1 alone drops NULLs. Solution: Problem: Show page 2 of products (page size 2) ordered by price ascending. Tables: products Expected output:
Hint: OFFSET = (page − 1) × size. Solution:
SELECT dept_id, dept_name, location FROM departments ORDER BY dept_name;SELECT first_name, hire_date FROM employees WHERE hire_date > DATE '2020-12-31' ORDER BY hire_date;SELECT product_name, price FROM products
WHERE category = 'Electronics' AND price < 20000
ORDER BY price;SELECT name, city FROM customers WHERE country = 'India' ORDER BY name;SELECT order_id, status FROM orders WHERE status NOT IN ('DELIVERED', 'CANCELLED') ORDER BY order_id;SELECT first_name FROM employees
WHERE UPPER(SUBSTRING(first_name, 1, 1)) IN ('A', 'E', 'I', 'O', 'U') -- Oracle/SQLite: SUBSTR
ORDER BY emp_id;SELECT customer_id, name FROM customers WHERE email IS NULL;SELECT first_name, salary FROM employees ORDER BY salary DESC, emp_id FETCH FIRST 2 ROWS ONLY;
-- MySQL/SQLite: LIMIT 2; SQL Server: SELECT TOP (2) ...SELECT product_name, price * stock AS stock_value
FROM products
WHERE price * stock > 300000
ORDER BY stock_value DESC;INSERT INTO departments (dept_id, dept_name, location) VALUES (5, 'Quality', 'Chennai');
SELECT COUNT(*) FROM departments;Level 2 — GROUP BY, HAVING, Functions, JOIN
Problem: Show job titles held by more than one employee. Tables: employees Expected output:
Hint: GROUP BY + HAVING. Solution: Problem: Total stock units per category. Tables: products Expected output:
Hint: SUM with GROUP BY. Solution: Problem: Average salary per department name (departments with employees only), highest first. Tables: employees, departments Expected output:
Hint: INNER JOIN then GROUP BY; add a tie-breaker. Solution:
Problem: Show every customer with their number of orders, including customers with none. Tables: customers, orders Expected output:
Hint: LEFT JOIN and count the orders' key, not *. Solution:
Problem: Orders above 10000 with the customer's name and city. Tables: orders, customers Expected output:
Hint: INNER JOIN on customer_id. Solution: Problem: For Engineering (dept 1), display names as LASTNAME, Firstname with the last name uppercase. Tables: employees Expected output:
Hint: UPPER + concatenation. Solution: Problem: Count customers per email domain (ignore missing emails). Tables: customers Expected output:
Hint: Take the substring after @. Solution: Problem: Number of orders per month in 2026. Tables: orders Expected output:
Hint: EXTRACT(MONTH...) or MONTH(). Solution: Problem: Products that have never been ordered (use a JOIN, not a subquery). Tables: products, order_items Expected output:
Hint: LEFT JOIN anti-join. Solution: Problem: Departments whose total salary exceeds 150000. Tables: employees, departments Expected output:
Hint: HAVING SUM(...). Solution: Problem: List employees whose manager works in a different department. Tables: employees (self join) Expected output:
Hint: Kiran (NULL department) is excluded by <> — discuss whether that is desired. Solution:
Problem: Units sold per product for DELIVERED orders only. Tables: order_items, orders, products Expected output:
Hint: Filter orders by status in the join or WHERE. Solution:
Problem: Count employees per salary band: A (≥ 150000), B (≥ 90000), C (otherwise). Tables: employees Expected output:
Hint: GROUP BY the CASE expression. Solution: Problem: Classify every customer by non-cancelled spend: High (≥ 100000), Medium (≥ 10000), Low (> 0), None (no non-cancelled orders). Tables: customers, orders Expected output:
Hint: Put the status filter in the ON clause of the LEFT JOIN. Solution:
SELECT job_title, COUNT(*) AS n FROM employees GROUP BY job_title HAVING COUNT(*) > 1;SELECT category, SUM(stock) AS units FROM products GROUP BY category ORDER BY category;SELECT d.dept_name, ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC, d.dept_name;SELECT c.name, COUNT(o.order_id) AS n_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY n_orders DESC, c.name;SELECT o.order_id, c.name, c.city, o.total_amount
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE o.total_amount > 10000
ORDER BY o.order_id;SELECT UPPER(last_name) || ', ' || first_name AS display_name -- MySQL: CONCAT(UPPER(last_name), ', ', first_name)
FROM employees WHERE dept_id = 1 ORDER BY emp_id;SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain, COUNT(*) AS n
FROM customers
WHERE email IS NOT NULL
GROUP BY SUBSTRING(email FROM POSITION('@' IN email) + 1);
-- MySQL: SUBSTRING_INDEX(email, '@', -1); SQL Server: SUBSTRING(email, CHARINDEX('@', email) + 1, 100)SELECT EXTRACT(MONTH FROM order_date) AS month_no, COUNT(*) AS orders
FROM orders
WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01'
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month_no;SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL;SELECT d.dept_name, SUM(e.salary) AS total_salary
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING SUM(e.salary) > 150000
ORDER BY total_salary DESC;Level 3 — Subqueries, CTEs, Window Functions
Problem: Employees earning more than the company average. Tables: employees Expected output:
Hint: Scalar subquery. Solution: Problem: Second-highest salary (a single value). Tables: employees Expected output: 150000.00 Hint: MAX below the MAX. Solution:
Problem: Employees with the third-highest distinct salary. Tables: employees Expected output: Hint: DENSE_RANK. Solution:
Problem: Exactly one highest-paid employee per department (including the "no department" group), lowest emp_id on ties. Tables: employees Expected output:
Hint: ROW_NUMBER with PARTITION BY and a tie-breaker. Solution:
Problem: Customers who have bought both a Laptop and a Monitor (in any orders). Tables: customers, orders, order_items, products Expected output:
Hint: Group by customer and count distinct matching products, or use INTERSECT. Solution:
Problem: Running total of order amounts by order date. Tables: orders Expected output:
Hint: SUM() OVER (ORDER BY...). Solution: Problem: Each non-cancelled order's percentage share of its month's revenue. Tables: orders Expected output:
Hint: Partition the window by month. Solution: Problem: For each customer's repeat orders, show the previous order date and days since it. Tables: orders Expected output:
Hint: LAG in a CTE, then filter out rows where LAG is NULL. Solution:
Problem: Count employees at each level of the management hierarchy. Tables: employees Expected output:
Hint: Recursive CTE starting from manager_id IS NULL. Solution:
Problem: Departments whose average salary exceeds the company average (use CTEs). Tables: employees, departments Expected output:
Hint: One CTE for company average, one for department averages. Solution:
Problem: Products priced above the average price of their own category. Tables: products Expected output:
Hint: Window AVG partitioned by category, filtered in an outer query. Solution:
Problem: Customers who have at least one order and all of whose orders are DELIVERED. Tables: customers, orders Expected output:
Hint: EXISTS for "at least one" + NOT EXISTS for "no non-delivered". Without the first condition, Elena (no orders) would qualify vacuously. Solution:
Problem: Rank products by non-cancelled revenue within their category (include unsold products with 0). Tables: products, order_items, orders Expected output:
Hint: Aggregate with a LEFT JOIN chain whose status filter is in ON, then DENSE_RANK. Solution:
SELECT first_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;SELECT MAX(salary) AS second_highest FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);SELECT first_name, salary
FROM (SELECT first_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t
WHERE dr = 3;WITH r AS (
SELECT dept_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
FROM employees
)
SELECT dept_id, first_name, salary FROM r WHERE rn = 1 ORDER BY dept_id NULLS LAST;SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.product_name IN ('Laptop', 'Monitor')
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product_name) = 2
ORDER BY c.name;SELECT order_id, order_date, total_amount,
SUM(total_amount) OVER (ORDER BY order_date, order_id ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders
ORDER BY order_date, order_id;SELECT order_id, EXTRACT(MONTH FROM order_date) AS month_no, total_amount,
ROUND(100.0 * total_amount / SUM(total_amount) OVER (PARTITION BY EXTRACT(MONTH FROM order_date)), 2) AS pct_of_month
FROM orders
WHERE status <> 'CANCELLED'
ORDER BY order_id;WITH x AS (
SELECT customer_id, order_id, order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
FROM orders
)
SELECT customer_id, order_id, order_date, prev_order_date,
order_date - prev_order_date AS days_between -- MySQL: DATEDIFF(order_date, prev_order_date)
FROM x
WHERE prev_order_date IS NOT NULL
ORDER BY customer_id;WITH RECURSIVE org AS (
SELECT emp_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT lvl, COUNT(*) AS employees FROM org GROUP BY lvl ORDER BY lvl;WITH company AS (SELECT AVG(salary) AS a FROM employees),
dept AS (SELECT dept_id, AVG(salary) AS dept_avg FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id)
SELECT d.dept_name, ROUND(dept.dept_avg, 2) AS dept_avg
FROM dept
JOIN departments d ON d.dept_id = dept.dept_id
CROSS JOIN company
WHERE dept.dept_avg > company.a;Level 4 — Advanced Analytics, Performance, Complex Joins, Recursion
Problem: Monthly non-cancelled revenue for January–April 2026, including months with no orders, with the change from the previous month. Tables: orders Expected output:
Hint: Generate a month scaffold, LEFT JOIN, then LAG. Solution:
Problem: Find continuous periods where an ECU temperature sensor read above 90 °C. Tables: Expected output:
Hint: Gaps and islands: reading_id - ROW_NUMBER() over the hot readings is constant within a run (reading IDs are consecutive). Solution: (If IDs are not consecutive, first number all readings with ROW_NUMBER over ts and use that number instead of reading_id.)
Problem: Pareto analysis: which products together account for the first 80% of non-cancelled revenue (include the product that crosses 80%)? Tables: order_items, orders, products Expected output:
Hint: Cumulative sum ordered by revenue; keep rows whose cumulative total *before* them is below 80%. Solution:
Problem (performance): This query is slow on a 50-million-row orders table. Rewrite it and propose an index.
Tables: orders Expected output: The same rows, produced with an index range scan instead of a full scan (verify with EXPLAIN). Hint: Make predicates sargable; select only needed columns; match the index to filter + sort. Solution:
The equality column (status) comes first, the range/sort column (order_date) second, so the database can seek to ('PENDING', 2026-12-31) and read rows already in the required order — no sort step.
Problem (performance): Rewrite this query to avoid executing a correlated subquery per row, producing the same result.
Tables: employees Expected output: 8 rows; e.g., Asha 250000 / 147500, Priya 60000 / 90000, Kiran 80000 / NULL (the correlated version returns NULL for the NULL department). Hint: A window function computes all department averages in one pass — but note NULL handling differs. Solution:
PARTITION BY dept_id groups NULLs together (Kiran would get 80000), while the correlated subquery's e2.dept_id = e.dept_id never matches NULL. The CASE preserves the original semantics — an example of checking equivalence carefully when optimizing.
Problem (complex join): For every department (including empty ones) show headcount, total salary, the highest earner's name, and the number of distinct job titles. Tables: departments, employees Expected output:
Hint: Aggregate employees per department in one CTE, pick the top earner in another, LEFT JOIN both to departments. Solution:
Problem (recursive): For each manager, count all direct and indirect reports. Tables: employees Expected output:
Hint: Recursive CTE producing (ancestor, descendant) pairs — a closure table on the fly. Solution:
Problem (cohort analysis): Group customers by the month of their first order; for each cohort, count customers and how many ordered again in a later month. Tables: orders Expected output:
Hint: First-order month per customer via MIN; then EXISTS for an order in a later month. Solution: (Ananya: first Jan, ordered Feb; Ben: first Jan, ordered Mar → both retained.)
Problem: Compute the median salary. Tables: employees Expected output: 95000 Hint: Ordered-set aggregate PERCENTILE_CONT (PostgreSQL, Oracle, SQL Server as a window function); in MySQL, use ROW_NUMBER and COUNT. Solution:
Sorted salaries: 60000, 80000, 90000, 95000, 95000, 120000, 150000, 250000 → median = (95000 + 95000) / 2 = 95000.
WITH RECURSIVE months AS (
SELECT DATE '2026-01-01' AS m
UNION ALL
SELECT CAST(m + INTERVAL '1 month' AS DATE) FROM months WHERE m < DATE '2026-04-01'
),
rev AS (
SELECT months.m AS month, COALESCE(SUM(o.total_amount), 0) AS revenue
FROM months
LEFT JOIN orders o
ON o.order_date >= months.m AND o.order_date < months.m + INTERVAL '1 month'
AND o.status <> 'CANCELLED'
GROUP BY months.m
)
SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM rev
ORDER BY month;CREATE TABLE sensor_readings (reading_id INT PRIMARY KEY, ts TIMESTAMP, temp_c DECIMAL(5,1));
INSERT INTO sensor_readings VALUES
(1, TIMESTAMP '2026-03-10 10:00', 85), (2, TIMESTAMP '2026-03-10 10:01', 92),
(3, TIMESTAMP '2026-03-10 10:02', 95), (4, TIMESTAMP '2026-03-10 10:03', 89),
(5, TIMESTAMP '2026-03-10 10:04', 91), (6, TIMESTAMP '2026-03-10 10:05', 93),
(7, TIMESTAMP '2026-03-10 10:06', 94), (8, TIMESTAMP '2026-03-10 10:07', 88);WITH hot AS (
SELECT reading_id, ts, temp_c,
reading_id - ROW_NUMBER() OVER (ORDER BY reading_id) AS grp
FROM sensor_readings
WHERE temp_c > 90
)
SELECT MIN(ts) AS start_ts, MAX(ts) AS end_ts, COUNT(*) AS readings, MAX(temp_c) AS max_temp
FROM hot
GROUP BY grp
ORDER BY start_ts;WITH rev AS (
SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id AND o.status <> 'CANCELLED'
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_name
),
cum AS (
SELECT product_name, revenue,
SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) AS running,
SUM(revenue) OVER () AS total
FROM rev
)
SELECT product_name, revenue, ROUND(100.0 * running / total, 2) AS cumulative_pct
FROM cum
WHERE running - revenue < 0.8 * total
ORDER BY revenue DESC;SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
AND UPPER(status) = 'PENDING'
ORDER BY order_date DESC;-- Store status in a consistent case (enforced by a CHECK constraint) so no UPPER() is needed
CREATE INDEX ix_orders_status_date ON orders (status, order_date DESC);
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE status = 'PENDING'
AND order_date >= DATE '2026-01-01'
AND order_date < DATE '2027-01-01'
ORDER BY order_date DESC;SELECT e.first_name, e.salary,
(SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e;SELECT first_name, salary,
CASE WHEN dept_id IS NULL THEN NULL
ELSE AVG(salary) OVER (PARTITION BY dept_id) END AS dept_avg
FROM employees;WITH agg AS (
SELECT dept_id, COUNT(*) AS headcount, SUM(salary) AS total_salary,
COUNT(DISTINCT job_title) AS distinct_titles
FROM employees
GROUP BY dept_id
),
top1 AS (
SELECT dept_id, first_name,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
FROM employees
)
SELECT d.dept_name,
COALESCE(a.headcount, 0) AS headcount,
COALESCE(a.total_salary, 0) AS total_salary,
t.first_name AS top_earner,
COALESCE(a.distinct_titles, 0) AS distinct_titles
FROM departments d
LEFT JOIN agg a ON a.dept_id = d.dept_id
LEFT JOIN top1 t ON t.dept_id = d.dept_id AND t.rn = 1
ORDER BY d.dept_id;WITH RECURSIVE pairs AS (
SELECT manager_id AS ancestor, emp_id AS descendant
FROM employees WHERE manager_id IS NOT NULL
UNION ALL
SELECT p.ancestor, e.emp_id
FROM pairs p JOIN employees e ON e.manager_id = p.descendant
)
SELECT m.first_name AS manager, COUNT(*) AS total_reports
FROM pairs
JOIN employees m ON m.emp_id = pairs.ancestor
GROUP BY m.emp_id, m.first_name
ORDER BY total_reports DESC;Expected Result
If your answers match the expected outputs for all 51 problems, you have covered the complete range of query-writing skills in this course.
Common Mistakes
Checking only that a query runs, not that its output matches the expected table.
Ignoring NULL rows (Problems 14, 26, 33, 47).
Forgetting zero-row groups (Problems 19, 29, 42, 43, 48).
Fan-out when combining several one-to-many joins (Problem 48).
Best Practices
Re-solve problems a week later without looking.
Solve each Level 3–4 problem in at least two ways (subquery vs window vs join).
Run EXPLAIN on your solutions once you add larger test data.
Interview Relevance
Problems 8, 14, 19, 24, 30–37, 41, 43–45, and 49–51 are direct copies of common interview questions.
Practice Questions
Extend Problem 37 to show the average days between orders per customer.
Extend Problem 44 to report the duration of each hot period in minutes.
Modify Problem 45 to show the Pareto analysis by customer instead of product.
Solve Problem 41 using GROUP BY and HAVING instead of EXISTS.
Solve Problem 49 with a closure table stored permanently.