What you need

A current browser on any device. The SQL Query Playground runs inside the page, so the data never leaves the device and nobody needs an account.
The built-in sample table. Click Try sample data and the table is loaded as data: 30 orders from fictional companies, with the columns order_id, date, customer, company, country, channel, product, units, unit_price, revenue and status.

Running the lesson

45 min

5 min
Everyone opens the playground, loads the sample and runs SELECT * FROM data LIMIT 10. One row is one order.
25 min
Learners work through the six tasks below in pairs. Each task names the SQL it practises.
10 min
Compare queries. Different queries can give the same right answer, which is worth pointing out.
5 min
Exit question: why does task 6 need HAVING and not WHERE?

Tasks

Which orders came from Germany? Show the order number, customer, company and revenue. (WHERE)
List every order with more than 400 in revenue, the largest first. (WHERE, ORDER BY)
Which companies have "trading" or "supply" in their name? Each company once. (DISTINCT, LIKE)
How many orders are paid, refunded and pending? (GROUP BY, COUNT)
How much paid revenue did each sales channel bring in? (WHERE, GROUP BY, SUM)
Which customers placed more than two orders? (GROUP BY, HAVING)

Show the solutions

These are the results on the built-in sample data.

1. Orders from Germany

Task 1

SELECT order_id, customer, company, revenue
FROM data
WHERE country = 'Germany'

6 orders, from Sam Reed (Northwind Trading) and Lena Fischer (Umbrella Retail).

2. Orders over 400

Task 2

SELECT order_id, company, revenue
FROM data
WHERE revenue > 400
ORDER BY revenue DESC

8 orders, from 798.00 (orders 1016 and 1026) down to 447.00.

3. Trading and supply companies

Task 3

SELECT DISTINCT company
FROM data
WHERE company LIKE '%trading%' OR company LIKE '%supply%'

Acme Supply Co and Northwind Trading. LIKE ignores upper and lower case here.

4. Orders by status

Task 4

SELECT status, COUNT(*) AS orders
FROM data
GROUP BY status

25 paid, 3 refunded and 2 pending.

5. Paid revenue by channel

Task 5

SELECT channel, ROUND(SUM(revenue), 2) AS revenue
FROM data
WHERE status = 'paid'
GROUP BY channel
ORDER BY revenue DESC

Direct 4563, Marketplace 2474 and Partner 2432.

6. Customers with more than two orders

Task 6

SELECT customer, COUNT(*) AS orders
FROM data
GROUP BY customer
HAVING orders > 2

Six customers, each with three orders.

Why HAVING and not WHERE

WHERE looks at one row at a time, before anything is grouped. At that point there is no "number of orders per customer" yet, only single orders. HAVING runs after GROUP BY, when each customer has become one row with a count, so that is where a condition on the count belongs.

Going further

Use your own data: the playground loads any CSV file, for example open data from a statistics office.
The playground queries one table. Joining two tables, subqueries and writing data need a full database system, which makes a good next lesson.

Analyse sales data with SQL in the browser

What you will learn