SQL Exercises with Solutions: 15 Practice Problems

SELECT name, unit_price
FROM Products
ORDER BY unit_price DESC;

Naming the columns instead of using * keeps the output readable and is what you would do in real reporting. ORDER BY sorts ascending by default, so DESC is required for "most expensive first".

Returns all 7 products, Beacon Analytics (1450) first.

SELECT DISTINCT country
FROM Customers
ORDER BY country;

DISTINCT applies to the whole selected row, not just one column, so with a single column it does exactly what you want. Adding ORDER BY makes the list readable rather than storage-ordered.

Returns 7 rows from 8 customers: two of them are in the same country, which is exactly what DISTINCT collapses.

SELECT first_name, last_name, salary
FROM Employees
WHERE dept_id = 10 AND active = 1;

Both conditions must hold, so AND is right. Writing active = 1 rather than a bare active is the portable form; SQL Server, for example, will not accept a bare column in WHERE.

Returns the active engineers only.

SELECT tier, COUNT(*) AS customers
FROM Customers
GROUP BY tier
ORDER BY customers DESC, tier;

GROUP BY collapses rows that share a tier into one row each, and COUNT(*) counts the rows inside each group. Every non-aggregated column in SELECT must appear in GROUP BY.

Returns one row per tier.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM Departments d
LEFT JOIN Employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY headcount DESC;

LEFT JOIN keeps every department. The subtle part is COUNT(e.emp_id) rather than COUNT(*): COUNT of a column skips NULLs, so an empty department correctly counts 0, while COUNT(*) would count the single NULL-filled row and report 1.

Returns all 5 departments with their headcounts.

SELECT d.dept_name, ROUND(AVG(e.salary), 0) AS avg_salary, COUNT(*) AS headcount
FROM Employees e
JOIN Departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;

WHERE cannot filter on COUNT(*), because WHERE runs before rows are grouped. HAVING is the filter that runs after grouping, which is the entire reason it exists.

Returns the multi-person departments, highest average first.

SELECT o.order_id, c.company, p.name AS product, o.quantity, o.order_total
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
JOIN Products p ON p.product_id = o.product_id
ORDER BY o.order_id;

Joins chain naturally: each JOIN adds one more table with its own ON condition. Aliases (o, c, p) keep the query readable and are required once the same column name exists in more than one table.

Returns all 15 orders with names instead of ids.

SELECT first_name, last_name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees)
ORDER BY salary DESC;

The inner query returns exactly one value, so it can sit on the right of a comparison. This is a scalar subquery. It is evaluated once, not per row.

Returns the above-average earners.

SELECT c.company, c.country
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.customer_id = c.customer_id AND o.status = 'Shipped'
)
ORDER BY c.company;

NOT EXISTS is the clearest way to say "no matching row exists". It is also safe with NULLs, unlike NOT IN, which returns no rows at all if the subquery produces a single NULL. That happens in every major database, including the SQLite that runs this site’s playground, and the WHERE clause guide shows it happening.

Returns customers with no shipped order.

SELECT name, unit_price,
       CASE WHEN unit_price < 200 THEN 'Budget'
            WHEN unit_price < 900 THEN 'Mid'
            ELSE 'Premium'
       END AS price_band
FROM Products
ORDER BY unit_price;

CASE stops at the first matching WHEN, so the second condition only needs its upper bound. ELSE catches everything left; without it, unmatched rows would come back NULL.

Returns all 7 products with a band.

SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM Orders;

This is the conditional-aggregation pattern, and it is how most one-row dashboards are built. Each SUM counts only the rows matching its condition, and all of them scan the table once.

Returns exactly one row with three numbers.

SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mth,
       COUNT(*) AS orders, ROUND(SUM(order_total), 0) AS revenue
FROM Orders
WHERE status = 'Shipped'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY yr, mth;

You can group by an expression, not just a column. Filtering in WHERE before grouping is both correct and faster than filtering afterwards.

YEAR() and MONTH() are MySQL and SQL Server functions. SQLite has neither, so this site’s playground adds them for convenience; in plain SQLite you would write CAST(strftime('%Y', order_date) AS INTEGER), and in PostgreSQL EXTRACT(YEAR FROM order_date). The dialect differences guide lists the other spellings that change.

Returns one row per month that had a shipped order.

SELECT e.first_name || ' ' || e.last_name AS employee,
       COUNT(*) AS orders, ROUND(SUM(o.order_total), 0) AS revenue
FROM Orders o
JOIN Employees e ON e.emp_id = o.emp_id
WHERE o.status = 'Shipped'
GROUP BY e.first_name, e.last_name
ORDER BY revenue DESC
LIMIT 1;

Everything you have learned in one query: a join to get names, a filter, an aggregate, and a limit. Note that GROUP BY lists the underlying columns, not the concatenated alias. Two spellings here are not universal: MySQL writes the name as CONCAT(e.first_name, ' ', e.last_name), because by default its || means OR, and SQL Server uses + or CONCAT() and puts TOP 1 after SELECT instead of LIMIT 1 at the end.

Returns a single row: the top seller.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY dept_id
)
SELECT d.dept_name, ROUND(a.avg_salary, 0) AS avg_salary
FROM dept_avg a
JOIN Departments d ON d.dept_id = a.dept_id
WHERE a.avg_salary > 100000
ORDER BY a.avg_salary DESC;

A CTE gives the intermediate result a name, so the final query reads like a sentence instead of a nest of brackets. It is the same work a subquery would do, expressed in the order you think. The threshold does not have to be a literal: replace 100000 with (SELECT AVG(salary) FROM Employees) and the query finds the departments above the company-wide average instead, which in the sample data is still just Finance.

Returns the departments averaging above 100,000.

WITH shipped AS (
  SELECT o.order_id, o.order_total, p.category
  FROM Orders o
  JOIN Products p ON p.product_id = o.product_id
  WHERE o.status = 'Shipped'
)
SELECT category,
       COUNT(*) AS orders,
       ROUND(SUM(order_total), 0) AS revenue,
       ROUND(AVG(order_total), 0) AS avg_order
FROM shipped
GROUP BY category
ORDER BY revenue DESC;

Separating "which rows count" from "what to compute" into two steps is how real reports stay maintainable. Change the filter once, in the CTE, and every metric below follows.

Returns one row per category that had a shipped order.