Practice Challenges: Put It All Together

You've now covered SQL from first principles through window functions and database design. This final article is a set of real analytical questions to answer against the practice database — try to solve each one yourself in the Code Playground before checking the suggested approach.

Challenge 1 — Basics

Question: List every employee's full name and job title, sorted alphabetically by last name.

SELECT first_name, last_name, job_title
FROM employees
ORDER BY last_name;

Challenge 2 — Filtering and aggregation

Question: What is the average salary in each department, rounded to the nearest whole number, for departments with more than 3 employees?

SELECT department_id, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

Challenge 3 — Joins

Question: For every order, show the customer's name, the order date, and the total value of that order (quantity × unit price, summed across all items).

SELECT c.customer_name, o.order_date, SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.customer_name, o.order_date, o.id;

Challenge 4 — Outer joins

Question: Which products have never appeared in any order?

SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

Challenge 5 — Subqueries

Question: Find every employee who earns more than their own manager.

SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Challenge 6 — Window functions

Question: For each customer, find their single most expensive order.

SELECT * FROM (
  SELECT c.customer_name, o.id AS order_id, SUM(oi.quantity * oi.unit_price) AS order_total,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rn
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.customer_name, c.id, o.id
) ranked
WHERE rn = 1;

Challenge 7 — CASE and aggregation together

Question: For each department, count how many employees earn above 100,000 versus at or below it.

SELECT department_id,
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS above_100k,
  COUNT(CASE WHEN salary <= 100000 THEN 1 END) AS at_or_below_100k
FROM employees
GROUP BY department_id;

Challenge 8 — CTEs

Question: Using a CTE, find departments whose average salary is above the company-wide average salary.

WITH company_avg AS (
  SELECT AVG(salary) AS avg_salary FROM employees
),
dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id
)
SELECT d.name, da.avg_salary
FROM dept_avg da
JOIN departments d ON d.id = da.department_id
WHERE da.avg_salary > (SELECT avg_salary FROM company_avg);

What's next?

Congratulations — if you can solve all eight of these comfortably, you have a genuinely strong SQL foundation used daily by data analysts, backend engineers, and BI developers. From here, the best next step is practicing on progressively larger and messier real-world datasets, and exploring engine-specific features (like PostgreSQL's JSON columns, or SQL Server's MERGE statement) as you encounter them on the job.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;