Duration: 4 hours | Theory: 10 min | Practice: 230 min
This is where students prove they can handle a real database. Northwind is an e-commerce database they've never seen before. The entire lecture is independent work - students explore, query, and optimize without hand-holding. Your job is to circulate and ask questions, not give answers.
Get the Northwind MySQL script from GitHub (mywind). Test it loads cleanly. Students will waste 30 minutes if the script has errors. Also prepare an ER diagram of Northwind to share after they explore (slide 2 mentions tools, but doesn't show the schema).
Slides 2-5 show DBeaver/MySQL Workbench setup. Note: slides 3-4 say "PostgreSQL" but this should be MySQL for Northwind. Either update the slides or clarify verbally that students should connect to MySQL, not Postgres.
Walk students through loading Northwind together:
CREATE DATABASE northwind;
USE northwind;
SOURCE /path/to/northwind.sql;
SHOW TABLES;
-- Should see: customers, orders, order_details, products, categories, suppliers, employees, shippersNow comes the critical part: exploration. Don't show them the schema yet. Give them 15 minutes to explore:
-- What tables exist?
SHOW TABLES;
-- What's in each table?
DESCRIBE customers;
DESCRIBE orders;
DESCRIBE order_details;
-- Sample data
SELECT * FROM customers LIMIT 5;
SELECT * FROM products LIMIT 5;
-- How do tables connect?
SHOW CREATE TABLE orders;
SHOW CREATE TABLE order_details;After 15 minutes, ask: "What does this database model?" (E-commerce company). "How do orders connect to products?" (Through order_details - junction table). "Who handles orders?" (Employees). Only after this discussion, show the ER diagram.
Slide 6 lists queries 1-10. Do the first 3 together, then let students try 4-10 independently. Walk around, help when stuck.
-- 1. List of customers
SELECT * FROM customers;
-- 2. Number of different products
SELECT COUNT(*) FROM products;
-- 3. Count of employees
SELECT COUNT(*) FROM employees;
-- 4. Total overall revenue (trickier - requires order_details)
SELECT SUM(quantity * unit_price) AS total_revenue
FROM order_details;
-- 5. Total revenue for one specific year (requires JOIN)
SELECT SUM(od.quantity * od.unit_price) AS revenue_1997
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
WHERE YEAR(o.order_date) = 1997;Query 4 trips students up - they try to get revenue from orders table. Ask: "Where is the price information stored?" until they find order_details.
Query 5 requires a JOIN. If they forget, ask: "How do you connect order_details to order dates?" They should remember JOINs from Lecture 3.
Slides 7-9 list queries 11-30. Students work alone or in pairs. You circulate but don't solve problems for them. When asked for help:
Only give hints, never full queries. If more than 3 students have the same issue, stop everyone and address it as a group.
-- 13. Count of products per category
SELECT c.category_name, COUNT(p.product_id) AS product_count
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
GROUP BY c.category_id, c.category_name;
-- 15. Revenue per category
SELECT c.category_name,
SUM(od.quantity * od.unit_price) AS revenue
FROM order_details od
JOIN products p ON od.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_id, c.category_name
ORDER BY revenue DESC;
-- 20. Products never ordered (LEFT JOIN + IS NULL)
SELECT p.product_name
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.order_id IS NULL;
-- 24. Employees with manager names (self-join!)
SELECT e.first_name, e.last_name, m.first_name AS manager_first, m.last_name AS manager_last
FROM employees e
LEFT JOIN employees m ON e.reports_to = m.employee_id;Query 24 (self-join) is the hardest. If nobody figures it out after 20 minutes, demo it briefly.
Slide 10 covers insertions. This teaches data integrity and foreign keys.
-- 31. Insert a product with its category
-- First check valid category_id
SELECT * FROM categories;
INSERT INTO products (product_name, supplier_id, category_id, unit_price, units_in_stock)
VALUES ('Organic Honey', 3, 2, 12.50, 50);
-- 32. Create an order (what is required?)
-- Students need to figure this out themselves
DESCRIBE orders;
INSERT INTO orders (customer_id, employee_id, order_date)
VALUES ('ALFKI', 3, CURDATE());
-- Get the order_id just created
SELECT LAST_INSERT_ID();
-- 35. Create order with multiple order_details
INSERT INTO order_details (order_id, product_id, unit_price, quantity, discount)
VALUES
(LAST_INSERT_ID(), 1, 18.00, 10, 0),
(LAST_INSERT_ID(), 5, 21.35, 5, 0.1);Students will hit foreign key errors. Good! "Why did that fail? What does the error say?" Let them read the error and fix it themselves.
Slide 12 introduces EXPLAIN and indexes. This is new material - demo it live.
-- Show a query
SELECT c.company_name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.company_name;
-- Use EXPLAIN to see execution plan
EXPLAIN SELECT c.company_name, COUNT(o.order_id)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.company_name;Explain the output: "type: ALL means full table scan - slow. type: ref means using index - fast. rows shows how many rows MySQL examines."
Then show indexes:
-- Check existing indexes
SHOW INDEX FROM orders;
-- Create index on frequently queried column
CREATE INDEX idx_order_date ON orders(order_date);
-- Now EXPLAIN again - see the difference
EXPLAIN SELECT * FROM orders WHERE order_date > '1997-01-01';Key points: Index WHERE columns, foreign keys, and JOIN columns. Don't index everything - it slows down inserts.
Foreign key violations: "Cannot add or update a child row" - they're referencing a non-existent ID. Have them SELECT from the parent table first.
Ambiguous columns: "Column 'customer_id' is ambiguous" - forgot table alias. Show them: c.customer_id vs just customer_id.
GROUP BY errors: "Expression must appear in GROUP BY" - they selected a column not in GROUP BY. Either add it to GROUP BY or wrap it in an aggregate function.
Wrong results: Ask "How many rows did you expect? Did you check with LIMIT 10 first?"
Quick debrief: "How many of you completed all 30 extraction queries? What was the hardest one?" Have someone share their solution to query 24 (self-join) or query 15 (revenue per category).
Slide 13 shows the final challenge. Assign this as homework or a project if time allows.
Analytics report: (1) Top 10 customers by revenue, (2) Monthly sales trends 1996-1998, (3) Employee performance (orders processed + revenue generated), (4) Products low on stock (<10 units). Include .sql file with commented queries and a 1-page summary of insights.