
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.

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
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.

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
- Which category has the most stock value? Sort question 6's result and keep one row.
- Show products whose name contains the word "book", in any capitalisation. (In SQLite,
LIKEignores capital letters for plain English letters.) - Add a column
discount INTEGER DEFAULT 0withALTER TABLE, give two products a discount withUPDATE, and list the price after discount. UseUPDATEcarefully: always with aWHERE.
Check your understanding

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.


