SQL practice problems with instant checking
47 SQL exercises from easy to interview level, on one small online-shop database. Write your query in the page and press Check my answer: it runs in your browser and tells you exactly what is different from the correct result. Hints, the expected result and a solution are there when you need them.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Easy (18)
- List all products: Show the
name,categoryandpriceof every product. - Customers in Australia: Show the
nameandcityof customers in Australia, in alphabetical order of name. - The cheapest product: Show the
nameandpriceof the single cheapest product. - How many customers?: Return one number,
customers: how many customers there are. - Which countries?: List each
countrythat has customers, once, alphabetically. - Expensive electronics: Show the
nameandpriceof Electronics products costing more than 100, most expensive first. - Names starting with A: Show the
nameof customers whose name starts with the letter A. - Customers with no city: Show the
nameandcountryof customers whose city is not recorded. - Cancelled orders: Show the
order_idandorder_dateof cancelled orders, most recent first. - Products between $20 and $60: Show the
nameandpriceof products priced from 20 to 60 inclusive. - Three most expensive: Show the
nameandpriceof the three most expensive products, most expensive first. - Average product price: Return
avg_price: the average product price, rounded to 2 decimals. - Orders in March 2025: Show the
order_idof every order placed in March 2025. - Prices with GST: Show each product's
nameandgst_price: price plus 10%, rounded to 2 decimals. - Names in capitals: Show each product
namein capital letters, in a column calledshout. - Customers per country: Show each
countryand its number ofcustomers, the largest first. - Products per category: Show each
category, its number ofproductsand itsavg_price(2 decimals). - Orders per status: Show each order
statusand how manyordershave it.
Medium (17)
- Orders with customer names: Show
order_id,order_dateand the customer'snamefor every order. - Customers who never ordered: Show the
nameof every customer with no orders at all. - Number of orders per customer: Show every customer's
nameand their number oforders(0 for none), most orders first, then by name. - Total of each order: Show each
order_idand itstotal(sum of quantity × unit_price), largest first. - Revenue per category: Show each product
categoryand itsrevenue(sum of quantity × unit_price over all order items), highest first. - Best-selling product: Show the
nameof the product with the most units sold and itsunits. - Countries with 3 or more customers: Show each
countrywith at least 3 customers, and the number ofcustomers. - Orders per month: Show each
month(as 2025-01) and the number oforders, in month order. - Products above the average price: Show the
nameandpriceof products that cost more than the average price. - Customers with orders but nothing delivered: Show the
nameof customers who have placed at least one order, but none of their orders has the status delivered. - Cancellation rate: Return
cancelled_pct: the percentage of orders that were cancelled, rounded to 1 decimal. - Total spent per customer: Show each customer's
nameandspent(total of their delivered orders), for customers who spent anything, highest first. - Products by price band: Count products per
band: 'budget' (under 50), 'mid' (50 to under 300) and 'premium' (300 and over), with the count inproducts. - Each customer's first order: Show each customer's
nameand the date of theirfirst_order, for customers who have ordered. - Orders with 3 or more products: Show the
order_idand the number of differentproductsof orders containing at least 3 different products. - Customers who share a city: Show pairs of customers in the same city:
customer_a,customer_b,city, each pair once. - All products of each category in one line: Show each
categoryandproducts: its product names joined with ', ', in alphabetical order of name.
Hard (12)
- Most expensive product in each category: Show
category,nameandpriceof the most expensive product in each category. - Running total of orders: For each
month, showordersand therunning_totalof orders so far, in month order. - Change from the previous month: For each
month, showordersandchange: orders minus the previous month's orders (NULL for the first month), in month order. - Second most expensive product: Show the
nameandpriceof the product with the second highest price. - Average order value: Return
avg_order_value: the average total of delivered orders, rounded to 2 decimals. - Rank customers by spending: Show each ordering customer's
name,spent(all orders) andspend_rank(1 = highest, using RANK). - Each category's share of revenue: Show each
category, itsrevenueandshare_pct: its percentage of all revenue, rounded to 1 decimal. - Repeat customers: Show the
nameof customers who placed orders in at least two different months. - Days between a customer's orders: For customer 1, show each
order_dateanddays_since_previous(whole days since their previous order; NULL for the first), in date order. - Biggest buyer per category: For each product
category, show the customernamewho bought the most units of it, and theunits. (Break ties by name, alphabetically.) - Every month, even empty ones: Show every
monthof 2025 (2025-01 to 2025-12) with its number oforders, including months with 0, in order. - Price quartiles: Show each product's
name,priceandquartile(1 = cheapest quarter, using NTILE(4) by price).