What it shows
One items table answers questions on stock value by category, items running low and best selling price using WHERE, GROUP BY and ORDER BY. It shows how a shop can manage stock with simple queries.
The science
SQL asks a database questions. WHERE filters rows, ORDER BY sorts them and aggregate functions such as SUM, COUNT and AVG calculate over many rows. GROUP BY makes one result row for each group, such as each category. UPDATE changes existing rows, so a sale can reduce stock without retyping everything.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- DB Browser for SQLite (free) or a free online SQLite page
- A made-up list of 15 grocery items with category, quantity and price
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
Create a database and run CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, category TEXT, qty INTEGER, price REAL);.
Insert 15 items with commands like INSERT INTO items (name, category, qty, price) VALUES ("Rice 1 kg", "Grains", 40, 60); using made-up prices.
See everything cheaper than Rs 50 with SELECT name, price FROM items WHERE price < 50 ORDER BY price;.
Find items running low with SELECT name, qty FROM items WHERE qty < 10;.
Work out stock value by category with SELECT category, SUM(qty * price) AS value FROM items GROUP BY category ORDER BY value DESC;.
Find the average price and the number of items in each category using AVG(price) and COUNT(*) with GROUP BY category.
Record a sale of 5 units with UPDATE items SET qty = qty - 5 WHERE name = "Rice 1 kg"; and check with SELECT.
Delete a discontinued item with DELETE FROM items WHERE id = 15; after you run the same WHERE with SELECT first to be sure of what will go.
What you should see
Queries list cheap items, low stock, stock value per category and averages, and an UPDATE after a sale changes the quantity that the next query shows.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What does GROUP BY do?
Makes one row for each distinct group and lets aggregates work per group.
2What does WHERE do?
Keeps only the rows that satisfy a condition.
3Why test a DELETE with SELECT first?
To see which rows will be removed before doing it.
4What does AS do?
Gives a calculated column a readable name.
Take it further
Add a sales table and a JOIN to show the profit per item as sold quantity times the price difference.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Programming
Databases and SQL Study Book
The complete study notes, practice questions and glossary for Databases and SQL