Mama Nia's duka has been running for a few months now.
The shelves are stocked. The products are categorised. The suppliers are logged. Everything is in the database - tidy, structured, exactly where it should be.
This week I walked into the classroom and told the cohort: "We're going to start by deleting things."
A few nervous looks. One student - Kamau - actually moved his laptop slightly further away from the edge of the desk, as if SQL could fall off.
That reaction is why I always start the table manipulation session with DELETE before anything else. Once students are comfortable removing data on purpose, with control, they stop being afraid of the database and start being curious about it. That shift is everything.
Part 1: DELETE - Removing Rows You No Longer Need
Mama Nia has decided to stop selling two products. Mafuta ya Taa isn't moving. Biskuti keeps going stale before it sells. Time to remove them from the system.
-- Remove a discontinued product
DELETE FROM duka.duka_products
WHERE product_name = 'Mafuta ya Taa';
-- Remove another
DELETE FROM duka.duka_products
WHERE product_name = 'Biskuti';
Two things I told the class before running either of these.
First - always run a SELECT with the same WHERE clause first. Before you delete, see exactly what you're about to remove:
SELECT * FROM duka.duka_products
WHERE product_name = 'Mafuta ya Taa';
If that SELECT returns what you expected - one row, correct product - then you delete. If it returns five rows or nothing at all, you've caught a problem before it became permanent.
Second - DELETE with no WHERE clause deletes every row in the table. The table structure stays. The data is gone. This is not a theoretical warning. Every experienced SQL developer has either done this once or knows someone who has. Always write the WHERE clause first, before the DELETE.
The DELETE ran. Two rows gone. The table still existed, just two products lighter. Kamau moved his laptop back to its normal position.
Part 2: DROP TABLE - When the Whole Table Needs to Go
Different situation. We created a draft table for a New Year promotion - new_year_sale_draft- and now that planning phase is over. The whole table needs to go.
-- First, create it
CREATE TABLE new_year_sale_draft(
product_name VARCHAR(50),
discount_percent INT
);
-- Then remove it entirely
DROP TABLE duka.new_year_sale_draft;
The question I wait for every cohort came right on time - Aisha asked: "What's the difference between DROP and TRUNCATE?"
TRUNCATE removes all rows instantly but keeps the table structure, like wiping a whiteboard clean without removing the whiteboard. DROP removes everything - the whiteboard, the frame, the wall mount.
I put the three-way comparison on the board:
The table makes the distinction concrete: DELETE (WHERE) removes some rows, TRUNCATE wipes all rows, DROP removes the data and the structure. One column - Removes structure? - is the entire difference between TRUNCATE and DROP. Students who see that column stop confusing the two.
Part 3: Core SQL Keywords - Asking Better Questions
Now the main event. The same duka_products table. The same data. Seven keywords that let you ask increasingly sophisticated questions.
SELECT - Choose Your Columns
The most basic question: what do you want to see?
-- Mama Nia just wants to see product names and prices
SELECT product_name, price
FROM duka.duka_products;
product_name | price
----------------------|-------
Unga wa Ngano | 145
Sukari | 110
Mafuta ya Kupikia | 220
...
Never use SELECT * in production queries, I told them. It pulls every column whether you need it or not. Name what you want. Your future self, and anyone reading your queries later, will thank you.
WHERE - Filter Before You Analyse
Which products cost more than KES 100?
SELECT product_name, price
FROM duka.duka_products
WHERE price > 100
ORDER BY price;
Which products need restocking - stock below 30 units?
SELECT product_name, stock_level
FROM duka_products
WHERE stock_level < 30;
The analogy I use: WHERE is the bouncer. It checks every row at the door before anything else happens. Only rows that pass the condition get into the query results.
ORDER BY - Sort the Results
Mama Nia wants to see her most expensive products first:
SELECT product_id, product_name, price
FROM duka.duka_products
ORDER BY price DESC;
And her lowest-stock items first - so she knows what to reorder soonest:
SELECT product_id, product_name, stock_level
FROM duka.duka_products
ORDER BY stock_level ASC;
ASC is the default. DESC flips it. Students who forget which is which remember "DESC sounds like descending, like going downstairs" - which is either helpful or annoying depending on who I ask.
LIMIT - Cap the Results
Top 3 most expensive products:
SELECT product_name, price
FROM duka_products
ORDER BY price DESC
LIMIT 3;
And a more specific version - the 2 priciest products specifically within Grains & Cereals:
SELECT product_id, product_name, price, product_category
FROM duka.duka_products
WHERE product_category = 'Grains & Cereals'
ORDER BY price DESC
LIMIT 2;
This is the WHERE + ORDER BY + LIMIT combination that shows up constantly in real reporting. Filter first, sort second, cap third.
GROUP BY- Summarise by Category
Now the queries that start to feel like real analysis.
How many products does Mama Nia carry in each category, and what's the average price per category?
SELECT product_category, COUNT(*) AS num_products, AVG(price) AS avg_price
FROM duka.duka_products
GROUP BY product_category
ORDER BY product_category;
product_category | num_products | avg_price
--------------------|--------------|----------
Cooking Essentials | 4 | 187.50
Grains & Cereals | 5 | 132.00
Household Goods | 3 | 95.00
...
Then the one that generated the most energy in the room - which supplier is sitting on the most stock value?
SELECT supplier, COUNT(*) AS num_products,
SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY supplier
ORDER BY total_stock_value DESC;
When that ran and a specific supplier came out top, Njeri said: "So Mama Nia should probably have a closer relationship with that supplier — they're holding the most of her money."
That's the moment. When a SQL query produces a genuine business insight, not just a correct output.
HAVING - Filter the Groups Themselves
This is WHERE's more specific sibling - and the one that confuses beginners most.
WHERE filters individual rows before grouping. HAVING filters groups after grouping. They look similar. They do very different things. The timing is everything.
Which categories have at least 2 different products?
SELECT product_category, COUNT(*) AS num_products
FROM duka.duka_products
GROUP BY product_category
HAVING COUNT(*) >= 2
ORDER BY product_category;
Notice: HAVING COUNT(*) >= 2, not HAVING num_products >= 2. The alias num_products doesn't exist yet when HAVING runs - it only exists in the final output. I put this on the board explicitly because it's the exact mistake everyone makes the first time:
-- This will error
HAVING num_products >= 2
-- This works
HAVING COUNT(*) >= 2
Which categories average over KES 100 per product?
SELECT product_category, AVG(price) AS avg_price
FROM duka.duka_products
GROUP BY product_category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;
Same pattern - repeat the aggregate function in HAVING, not the alias.
The Capstone - Everything Together
The session closer. One query that uses everything:
Which categories are holding more than KES 10,000 worth of stock?
SELECT product_category, SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY product_category
HAVING SUM(price * stock_level) > 10000
ORDER BY total_stock_value DESC;
And the inverse - which supplier-category combinations are holding less than KES 10,000?
SELECT product_category, supplier, SUM(price * stock_level) AS total_stock_level
FROM duka.duka_products
GROUP BY product_category, supplier
HAVING SUM(price * stock_level) < 10000
ORDER BY product_category, supplier;
I left this on the board for the entire capstone section:
This is the sequence SQL actually runs your query - not the order you write it. Understanding this sequence explains why aliases don't work in HAVING, why WHERE runs before grouping, and why LIMIT always caps the final sorted output. Every confusion about SQL clauses traces back to not knowing this order.
Practice Problems
Easy:
-- 1. Show all products from the 'Household Goods' category, ordered by price
-- 2. Which products have a stock level above 50? Show name and stock level only.
-- 3. Show the top 5 cheapest products
Medium:
-- Find the total stock value per supplier (price * stock_level)
-- Only show suppliers whose total stock value is above 5,000
-- Order by total stock value, highest first
SELECT supplier, SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY supplier
HAVING SUM(price * stock_level) > 5000
ORDER BY total_stock_value DESC;
Challenge:
-- Mama Nia wants a restocking priority report:
-- Show categories where the average stock level is below 40
-- Include: category name, number of products, average stock level, total stock value
-- Order by average stock level ascending (most urgent first)
-- Your query here...
What I Noticed Teaching This Session
1. Starting with DELETE was the right call every time. Fear of the database is the biggest obstacle beginners carry. The moment students deliberately remove data and see the table still running correctly - just smaller - the fear converts to confidence.
2. WHERE vs HAVING needs more than one explanation. I explained it twice, showed the alias error deliberately, and still had students reach for the alias in their own queries. The only fix that works is letting them hit the error themselves, reading it, and tracing back to why. Budget time for this.
3. The supplier stock value query produced the best discussion. "Which supplier is Mama Nia most dependent on?" is a real business question. When SQL answers it, students stop thinking of queries as exercises and start thinking of them as tools. That shift is the whole point of using a realistic dataset.
4. The order of operations diagram is worth drawing every session. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT - written on the board and left visible. Every question about why something doesn't work traces back to a misunderstanding of this sequence.
What's Next
Next session: JOINs. Mama Nia's duka is about to get a second table - a suppliers table with contact details and payment terms. And we'll start connecting the two.
-- A preview of what's coming
SELECT p.product_name, p.price, s.contact_person, s.payment_terms
FROM duka_products p
JOIN suppliers s ON p.supplier = s.supplier_name
WHERE p.stock_level < 30;
Two tables. One query. The data starts to feel like a real system. See you then.
*I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
Follow along or drop your questions in the comments.*
Top comments (0)