SQL Practice Exercises

Every exercise, at a glance

The full list of what you will be asked, in the order the paths run. Every question is checked by running your query against the sample database and comparing the rows it returns with the expected ones, so any query that produces the right result is accepted. Pick any one to start there.

Foundations 10 in this path · Beginner

Reading data out of a single table: choosing columns, filtering rows, sorting and limiting results.

• Look at the whole product catalogue Easy

  The sales team wants to see everything we sell. Return every column of every row in the Products table.

  Tables: Products

• Just the names and prices Easy

  For a price list, return only the name and unit_price of every product — nothing else.

  Tables: Products

• Find the expensive products Easy

  Finance wants a list of products that cost more than 400. Return all columns for those products only.

  Tables: Products

• Who earns the most? Easy

  Return the first_name, last_name and salary of every employee, with the highest earner first and the lowest earner last.

  Tables: Employees

• The three priciest items Easy

  Return the name and unit_price of just the three most expensive products, most expensive first.

  Tables: Products

• Which countries do we sell to? Easy

  Marketing wants a de-duplicated list of the countries our customers are based in. Return each country exactly once, in a single column named country.

  Tables: Customers

• Active engineers only Easy

  HR needs the first_name and last_name of employees who are in department 10 and are currently active (active = 1). Both conditions must hold.

  Tables: Employees

• Our best-tier customers Easy

  Return the company and tier of every customer whose tier is either Gold or Platinum.

  Tables: Customers

• Companies beginning with A Medium

  Return the company name of every customer whose company name starts with the letter A.

  Tables: Customers

• Who reports to nobody? Medium

  Department heads have no manager, so their manager_id is empty. Return the first_name and last_name of every employee with no manager.

  Tables: Employees

Grouping & Joins 10 in this path · Intermediate

Summarising many rows into one, and combining tables that reference each other.

• How many customers do we have? Easy

  Return a single row with a single column named total_customers holding the number of rows in the Customers table.

  Tables: Customers

• Average salary, rounded Easy

  Return the average employee salary rounded to the nearest whole number, in one column named avg_salary.

  Tables: Employees

• Cheapest and dearest Easy

  In one row, return the lowest unit_price as min_price and the highest as max_price from the Products table.

  Tables: Products

• Revenue actually shipped Medium

  Cancelled and pending orders should not count as revenue. Return the sum of order_total for orders with status 'Shipped', in one column named shipped_revenue.

  Tables: Orders

• Headcount per department Medium

  Return each dept_id alongside the number of employees in it, with the count in a column named headcount.

  Tables: Employees

• Biggest departments first Medium

  Return dept_id and headcount as before, but sort so the department with the most employees comes first.

  Tables: Employees

• Only the multi-person departments Medium

  Return dept_id and headcount for departments that contain more than two employees.

  Tables: Employees

• Put a name to the department Medium

  Employees only store a dept_id. Return each employee's first_name and last_name next to their dept_name by joining to the Departments table.

  Tables: Employees, Departments

• Every department, even the quiet ones Hard

  Return every dept_name together with how many employees it has, named headcount. Departments with no employees at all must still appear, showing a count of 0.

  Tables: Departments, Employees

• Label each order by size Hard

  Return order_id, order_total, and a column named size_band that reads 'Large' when order_total is 8000 or more, 'Medium' when it is at least 3000 but under 8000, and 'Small' otherwise.

  Tables: Orders

Advanced Queries 8 in this path · Advanced

Queries inside queries, tables joined to themselves, and multi-step business questions.

• Better paid than average Medium

  Return the first_name, last_name and salary of every employee earning more than the average salary in their own department.

  Tables: Employees

• Customers with a shipped order Medium

  Every customer has ordered something, but not every order ships. Return the company name of each customer who has had at least one order reach 'Shipped' status.

  Tables: Customers, Orders

• Customers still waiting Medium

  Now the exact opposite: return the company name of each customer who has never had an order ship — everything they ordered is still pending or was cancelled.

  Tables: Customers, Orders

• Name the manager Hard

  Return each employee's first_name alongside their manager's first_name in a column named manager_name. Employees with no manager should be left out.

  Tables: Employees

• One combined contact list Medium

  Build a single two-column list of everyone we deal with. Return each employee's first_name and each customer's contact under a shared column named person_name, plus a column named source containing 'Employee' or 'Customer' as appropriate.

  Tables: Employees, Customers

• Revenue by product category Hard

  Return each product category alongside the total order_total it has generated from shipped orders only, in a column named category_revenue, highest revenue first.

  Tables: Orders, Products

• Who is closing the deals? Hard

  Return the first_name and last_name of each employee together with how many orders they handled, in a column named orders_handled, counting shipped orders only, and listing the busiest employee first. Only include employees who handled at least one shipped order.

  Tables: Employees, Orders

• Big spenders Hard

  Return company and the total spend as total_spent for every customer whose combined order_total across all their orders exceeds 10000, largest spender first.

  Tables: Customers, Orders

Expressions & Reports 14 in this path · Intermediate

CASE logic, text and date functions, CTEs, and the multi-step report queries real jobs actually ask for.

• Label the price bands Easy

  Marketing wants each product labelled. Return name, unit_price, and a column called price_label that says premium for products costing 500 or more and standard for everything else.

  Tables: Products

• Support levels by tier Easy

  Support triages customers by tier. Return company, tier, and support_level: Platinum customers get 'Top priority', Gold customers get 'Standard support', and everyone else gets 'Self-serve'.

  Tables: Customers

• One-row status report Medium

  Management wants the order pipeline on a single row: three columns named shipped, pending and cancelled, each counting the orders with that status.

  Tables: Orders

• Revenue won and lost Hard

  For each employee who has handled orders, return first_name, shipped_revenue (total order_total of their Shipped orders) and cancelled_revenue (total of their Cancelled orders), highest shipped_revenue first.

  Tables: Employees, Orders

• Mailing labels Easy

  HR needs mailing labels: a single column full_name (first name, a space, last name) plus email, for every employee, alphabetical by full_name.

  Tables: Employees

• Case-insensitive search Easy

  A colleague types 'an' into the customer search box and expects every company whose name contains those two letters, whether they are stored in upper or lower case - so 'Andes' counts as well as 'Meridian'. Return company.

  Tables: Customers

• Strip the domain Medium

  For the Engineering department (dept_id 10), return each employee's email and a column mailbox holding the email with '@example.com' removed.

  Tables: Employees

• The longest product names Medium

  The catalogue layout breaks on long names. Return name and name_length for every product whose name is 18 characters or longer.

  Tables: Products

• Who reports to whom Medium

  List every employee's first_name alongside reports_to - their manager's first name, or the text 'No manager' for employees at the top of the tree.

  Tables: Employees

• Orders by month Medium

  How does order volume spread across the year? Return order_month (the month number from order_date) and orders (how many orders that month), latest month first.

  Tables: Orders

• Big orders, with names Medium

  Define a CTE called big_orders that selects orders with order_total above 3000, then return order_id, company and order_total for those orders.

  Tables: Orders, Customers

• Well-paid departments Hard

  Using a CTE that computes each department's average salary, return dept_name and avg_salary for the departments whose average exceeds 100000.

  Tables: Employees, Departments

• Two CTEs, one report Hard

  Build a per-salesperson report using two CTEs: one counting each employee's Shipped orders, one totalling each employee's revenue across all orders. Return first_name, shipped_orders and revenue, highest revenue first.

  Tables: Employees, Orders

• Category revenue report Hard

  The quarterly report needs revenue by product category, ignoring cancelled orders: return category, orders (count) and revenue (sum of order_total), biggest revenue first.

  Tables: Orders, Products

Frequently asked questions