What you need

  • Computer and internet: a laptop or desktop and a stable connection
  • System: Windows 10 or 11, macOS or Linux
  • Tools: SQLite, MySQL or MariaDB
  • Before this: SQL: SELECT and WHERE

Download only from each tool's official site and check the address before installing.

The situation

A small shop keeps its products and orders in a database. The owner has six questions and asks you to answer each with one SQL query. This exercise practises everything from SQL: SELECT and WHERE.

Ewoi

Every query here only reads data with SELECT, so you cannot break anything. Experiment freely.

Set up the shop database

Work in the same ~/ewoi-learning/sql folder as the lesson (on Windows, in the Ubuntu terminal). Start SQLite with a new database file, shop.db (or use MySQL or MariaDB):

mkdir -p ~/ewoi-learning/sql
cd ~/ewoi-learning/sql
sqlite3 shop.db

Turn on readable output:

.headers on
.mode column

Create and fill the products table:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(40) NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products (id, name, category, price, stock) VALUES
  (1, 'Phone charger', 'electronics', 850, 40),
  (2, 'Earphones', 'electronics', 1200, 0),
  (3, 'Water bottle', 'home', 650, 25),
  (4, 'School bag', 'school', 2400, 12),
  (5, 'Exercise book (pack)', 'school', 300, 200),
  (6, 'Power bank', 'electronics', 3500, 7),
  (7, 'Lunch box', 'home', 900, 0),
  (8, 'Calculator', 'school', 1500, 18);

Prices are whole Kenyan shillings, stored as numbers. Check the data arrived with SELECT COUNT(*) FROM products;, which should print 8.

The six questions

Write one query for each. Check the expected number of rows before looking at any hints.

# The owner asks Rows expected
1 Which products are out of stock? Show the name only. 2
2 Which school products cost less than 2,000? Show name and price. 2
3 List all electronics, most expensive first. 3
4 What are the three cheapest products? 3
5 How many products are in each category? 3
6 What is the total value of stock (price times stock) for each category? 3

Ask your AI assistant

I am practising SQL. My table is products(id, name, category, price, stock). For the question "[paste one question]", here is my query: [paste it]. Do not give me the answer. Tell me whether it is correct, and if not, give me one hint.

Hints

Hint for 1 and 2Use WHERE. For question 2 you need two conditions joined with AND.
Hint for 3 and 4ORDER BY sorts; DESC means largest first; LIMIT keeps the first few rows.
Hint for 5 and 6GROUP BY category makes one result row per category. COUNT counts rows; SUM adds up values, and you can SUM a calculation such as price * stock.

Solutions

Try every question first. Then compare:

SELECT name FROM products WHERE stock = 0;

SELECT name, price FROM products WHERE category = 'school' AND price < 2000;

SELECT name, price FROM products WHERE category = 'electronics' ORDER BY price DESC;

SELECT name, price FROM products ORDER BY price ASC LIMIT 3;

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

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

Your answers can differ and still be right; for example, ORDER BY price without ASC sorts smallest first too. What matters is the rows you get back.

For question 6 you should see: electronics 58,500 (850 x 40 + 0 + 3,500 x 7), home 16,250 and school 115,800.

Ewoi

If your numbers differ, do not panic. Run SELECT * FROM products WHERE category = 'home'; and do the maths by hand for one category. Checking a small part is how developers debug data.

Save your work

Leave SQLite with .quit. Put your six queries, each ending with ;, in a file named shop-answers.sql in your editor, and check them all at once:

sqlite3 -header -column shop.db < shop-answers.sql

You should see six result tables matching the row counts above. Then save the SQL that rebuilds the shop and commit both files (the folder is already a Git repository from the lesson, and .gitignore already keeps shop.db out):

sqlite3 shop.db .dump > shop.sql
git add shop.sql shop-answers.sql
git commit -m "Answer the shop database questions"
git status

git status should say nothing to commit, working tree clean.

Go further

  1. Which category has the most stock value? Sort question 6's result and keep one row.
  2. Show products whose name contains the word "book", in any capitalisation. (In SQLite, LIKE ignores capital letters for plain English letters.)
  3. Add a column discount INTEGER DEFAULT 0 with ALTER TABLE, give two products a discount with UPDATE, and list the price after discount. Use UPDATE carefully: always with a WHERE.

Check your understanding

1. Which query lists only products that are out of stock?

WHERE keeps only the rows where the condition is true.

2. What does GROUP BY category do?

GROUP BY groups rows that share a value; summary functions then work on each group.

3. Why must an UPDATE almost always have a WHERE?

An UPDATE without WHERE changes every row. Check the WHERE with a SELECT first.

Get the code on GitHub

Found this useful?

Every lesson stays free. Tap the thumbs up so we know what works, or say thanks with a soda or a coffee: it pays for hosting and new lessons.

Buy me a sodaBuy me a coffee

Want more on this?

Tell us which part was not clear, ask for a follow-up lesson, or request a lesson on any topic. We read every request.

I would like
Only to tell you when it is ready.

WhatsApp

Join the channelChat with us

Free lessons and updates in the channel. Questions about learning or training? Chat with us.