SQL Practice Lab: SELECT, WHERE, GROUP BY and JOIN

Turn the SQL basics into something you can run. In this practice lab, you will select columns, filter rows, sort results, calculate totals and connect two tables. All names and transactions below are fictional.

You need: a browser and about 20 minutes. Open SQLite Fiddle in another tab, or use your own empty SQLite practice database. Run the setup once, then run each query separately. Use a practice database rather than a database containing real information.

1. Set up two small tables

Copy the following SQL into the practice editor and run it once. The first table stores customers. The second stores orders and links them to customers using customer_id.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES
  (1, 'Amina', 'Nairobi'),
  (2, 'Brian', 'Mombasa'),
  (3, 'Chao', 'Nairobi');
INSERT INTO orders VALUES
  (101, 1, 1200),
  (102, 2, 800),
  (103, 1, 600),
  (104, 3, 1500);

Check the input before asking questions about it:

If you see “table already exists”, the setup has already run. Continue to the queries below. To start again, use a new empty practice database; do not repeatedly run the INSERT statements.

2. Choose columns with SELECT

Show each customer’s name and city:

SELECT name, city
FROM customers
ORDER BY name;

Expected result: Amina / Nairobi, Brian / Mombasa, then Chao / Nairobi. SELECT chooses the columns. FROM chooses the table. ORDER BY makes the row order explicit.

SELECT city FROM customers ORDER BY city;

3. Filter and sort the rows

Find orders worth at least 1,000 units, with the largest first:

SELECT order_id, amount
FROM orders
WHERE amount >= 1000
ORDER BY amount DESC, order_id;

WHERE keeps matching rows. DESC sorts from largest to smallest. The second sort column breaks ties predictably.

SELECT order_id, amount
FROM orders
WHERE amount < 1000
ORDER BY amount ASC, order_id;

4. Calculate totals with GROUP BY

One customer may have several orders. Group those rows to calculate a total per customer:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC, customer_id;

SUM adds the amounts in each group. AS gives the calculated column a readable name. Every selected value here is either the grouping key or an aggregate calculation.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

5. Connect the tables with JOIN

A customer ID is useful to a database, but a name is easier for a person to read. Match each order to its customer:

SELECT o.order_id, c.name, o.amount
FROM orders AS o
JOIN customers AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

o and c are short table aliases. ON defines the relationship. JOIN here means INNER JOIN, so it returns matching records. Amina appears twice because she has two orders; this is expected.

6. Final challenge: combine the ideas

Find each Nairobi customer’s total spending. Show name and total_spent, with the highest total first. Try it before opening the answer.

SELECT c.name, SUM(o.amount) AS total_spent
FROM customers AS c
JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE c.city = 'Nairobi'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC, c.name;

What to remember

• SELECT chooses columns; FROM chooses the input.
• WHERE filters rows.
• ORDER BY controls display order.
• GROUP BY combines rows for aggregate calculations.
• JOIN connects related tables through an explicit condition.

Reference: SQLite SELECT documentation. The examples use SQLite; details can differ in other database systems.