SQL Practice Exercises: SELECT, WHERE and GROUP BY

The best way to learn SQL is to write queries and check the result. Here are fifteen exercises on one small products table. Try each one yourself first, then open the solution. All the solutions were run on a real MySQL server.

Create the table once:

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

The table has eight products: id, name, category, price, stock and added_on.

Exercise 1: expensive products

List the name and price of products that cost more than 100, cheapest first.

Show a possible solution

query.sql

SQL

SELECT name, price FROM products WHERE price > 100 ORDER BY price;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Marker Set    | 145.00 |
| Water Bottle  | 220.00 |
| Laptop Sleeve | 450.00 |
| Desk Lamp     | 650.00 |
| Backpack      | 999.00 |
+---------------+--------+
5 rows in set

Exercise 2: two categories

List the names of the products in the stationery or the home category, in alphabetical order. Use IN.

Show a possible solution

query.sql

SQL

SELECT name, category FROM products WHERE category IN ('stationery', 'home') ORDER BY name;

Output

OUTPUT

+--------------+------------+
| name         | category   |
+--------------+------------+
| Desk Lamp    | home       |
| Gel Pen      | stationery |
| Marker Set   | stationery |
| Notebook     | stationery |
| Sticky Notes | stationery |
| Water Bottle | home       |
+--------------+------------+
6 rows in set

Exercise 3: out of stock

Which products are out of stock?

Show a possible solution

query.sql

SQL

SELECT name FROM products WHERE stock = 0;

Output

OUTPUT

+------------+
| name       |
+------------+
| Marker Set |
+------------+
1 row in set

Exercise 4: price with tax

Show each product’s name, its price, and its price plus 18% tax, rounded to two decimals. Name the last column price_with_tax. Show only the 3 cheapest.

Show a possible solution

query.sql

SQL

SELECT name, price, ROUND(price * 1.18, 2) AS price_with_tax FROM products ORDER BY price LIMIT 3;

Output

OUTPUT

+--------------+-------+----------------+
| name         | price | price_with_tax |
+--------------+-------+----------------+
| Gel Pen      | 15.50 |          18.29 |
| Sticky Notes | 35.00 |          41.30 |
| Notebook     | 80.00 |          94.40 |
+--------------+-------+----------------+
3 rows in set

Exercise 5: added in March

List the products added in March 2026. Write the condition so that an index on added_on could be used (no function on the column).

Show a possible solution

query.sql

SQL

SELECT name, added_on FROM products WHERE added_on >= '2026-03-01' AND added_on < '2026-04-01' ORDER BY added_on;

Output

OUTPUT

+--------------+------------+
| name         | added_on   |
+--------------+------------+
| Desk Lamp    | 2026-03-03 |
| Water Bottle | 2026-03-20 |
+--------------+------------+
2 rows in set

Exercise 6: search by pattern

Find products whose name starts with S or contains pen (any case).

Show a possible solution

query.sql

SQL

SELECT name FROM products WHERE name LIKE 'S%' OR name LIKE '%pen%' ORDER BY name;

Output

OUTPUT

+--------------+
| name         |
+--------------+
| Gel Pen      |
| Sticky Notes |
+--------------+
2 rows in set

Exercise 7: a price range

List the products that cost between 100 and 500 (both included), with their prices.

Show a possible solution

query.sql

SQL

SELECT name, price FROM products WHERE price BETWEEN 100 AND 500 ORDER BY price;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Marker Set    | 145.00 |
| Water Bottle  | 220.00 |
| Laptop Sleeve | 450.00 |
+---------------+--------+
3 rows in set

Exercise 8: stock labels

For every product, show its name and a label: out when the stock is 0, low when it is below 30, otherwise ok. Use CASE.

Show a possible solution

query.sql

SQL

SELECT name, stock,
       CASE WHEN stock = 0 THEN 'out' WHEN stock < 30 THEN 'low' ELSE 'ok' END AS stock_status
FROM products
ORDER BY stock;

Output

OUTPUT

+---------------+-------+--------------+
| name          | stock | stock_status |
+---------------+-------+--------------+
| Marker Set    |     0 | out          |
| Laptop Sleeve |    18 | low          |
| Backpack      |    25 | low          |
| Desk Lamp     |    40 | ok           |
| Water Bottle  |    75 | ok           |
| Notebook      |   120 | ok           |
| Sticky Notes  |   210 | ok           |
| Gel Pen       |   300 | ok           |
+---------------+-------+--------------+
8 rows in set

Exercise 9: categories

List each category once, in alphabetical order.

Show a possible solution

query.sql

SQL

SELECT DISTINCT category FROM products ORDER BY category;

Output

OUTPUT

+------------+
| category   |
+------------+
| bags       |
| home       |
| stationery |
+------------+
3 rows in set

Exercise 10: products per category

For each category, show the number of products and their average price (rounded to two decimals).

Show a possible solution

query.sql

SQL

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS average_price FROM products GROUP BY category ORDER BY category;

Output

OUTPUT

+------------+----------+---------------+
| category   | products | average_price |
+------------+----------+---------------+
| bags       |        2 |        724.50 |
| home       |        2 |        435.00 |
| stationery |        4 |         68.88 |
+------------+----------+---------------+
3 rows in set

Exercise 11: only big categories

Show only the categories that have more than two products.

Show a possible solution

query.sql

SQL

SELECT category, COUNT(*) AS products FROM products GROUP BY category HAVING COUNT(*) > 2;

Output

OUTPUT

+------------+----------+
| category   | products |
+------------+----------+
| stationery |        4 |
+------------+----------+
1 row in set

HAVING filters groups after they are counted. WHERE could not be used here, because it runs before the grouping.

Exercise 12: stock value

Calculate the value of the stock (price times stock) per category, with the biggest first.

Show a possible solution

query.sql

SQL

SELECT category, SUM(price * stock) AS stock_value FROM products GROUP BY category ORDER BY stock_value DESC;

Output

OUTPUT

+------------+-------------+
| category   | stock_value |
+------------+-------------+
| home       |    42500.00 |
| bags       |    33075.00 |
| stationery |    21600.00 |
+------------+-------------+
3 rows in set

Exercise 13: cheapest and most expensive

For each category, show the lowest and the highest price.

Show a possible solution

query.sql

SQL

SELECT category, MIN(price) AS cheapest, MAX(price) AS priciest FROM products GROUP BY category ORDER BY category;

Output

OUTPUT

+------------+----------+----------+
| category   | cheapest | priciest |
+------------+----------+----------+
| bags       |   450.00 |   999.00 |
| home       |   220.00 |   650.00 |
| stationery |    15.50 |   145.00 |
+------------+----------+----------+
3 rows in set

Exercise 14: above the average

List the products whose price is above the average price of all products.

Show a possible solution

query.sql

SQL

SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price DESC;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Backpack      | 999.00 |
| Desk Lamp     | 650.00 |
| Laptop Sleeve | 450.00 |
+---------------+--------+
3 rows in set

The subquery returns one number (the average), and the outer query compares each price with it.

Exercise 15: newest per category

Show the newest product in each category (the one with the latest added_on).

Show a possible solution

query.sql

SQL

SELECT p.category, p.name, p.added_on
FROM products p
WHERE p.added_on = (SELECT MAX(added_on) FROM products WHERE category = p.category)
ORDER BY p.category;

Output

OUTPUT

+------------+---------------+------------+
| category   | name          | added_on   |
+------------+---------------+------------+
| bags       | Laptop Sleeve | 2026-04-02 |
| home       | Water Bottle  | 2026-03-20 |
| stationery | Sticky Notes  | 2026-04-18 |
+------------+---------------+------------+
3 rows in set

The subquery is correlated: it runs for each outer row, with that row’s category. The window-function version is in Practice 3.

How to practise

Predict the result before you run the query. Surprises are where you learn.
Change a solution slightly (another column, another condition) and predict again.
When a query is wrong, read the error message, then fix one thing at a time.
Come back to the lessons: WHERE, GROUP BY, HAVING, Subqueries.

lightbulb

Write the query in the order you think

Start with FROM (where is the data?), then WHERE (which rows?), then GROUP BY (what groups?), then SELECT (what columns?), then ORDER BY (Execution Order).