Lecture 3: DML Operations & JOINs

Duration: 3 hours | Theory: 15 min | Practice: 165 min

Overview

This is a query marathon. Students load clubdata.sql (slide 3) and write 30-40 SELECT queries. Slides 2-4 cover basics, slides 5-7 show JOIN types, slide 8+ are exercises. Goal: get students comfortable reading table structures and writing multi-table queries without hand-holding.

Setup (20 min)

Slide 3 shows the clubdata.sql import. Walk through together:

SOURCE /path/to/clubdata.sql;
USE clubdata;
SHOW TABLES;
DESCRIBE facilities;
DESCRIBE bookings;
DESCRIBE members;

Have students run SELECT * FROM facilities LIMIT 5; for each table to see sample data. This exploration is important - they need to know what data exists before querying it.

Slides 2-4: Basic SELECT (15 min live demo)

Slide 2 explains SELECT/FROM/WHERE. Slide 4 shows a simple query. Don't just show slides - type these live:

-- All facilities
SELECT * FROM facilities;

-- Just names and costs
SELECT name, membercost, guestcost FROM facilities;

-- Filtered: expensive maintenance
SELECT name, monthlymaintenance
FROM facilities
WHERE monthlymaintenance > 500;

-- Multiple conditions
SELECT name FROM facilities
WHERE membercost = 0 AND guestcost < 10;

After each query, ask students: "How many rows do you expect?" Run it, compare results. This builds SQL intuition.

Slides 5-7: JOINs (45 min)

Slide 5 introduces JOIN syntax. Slide 6 shows the Visual SQL Joins diagram (circles). Most important slide in the lecture - display it while coding examples.

Start with INNER JOIN only:

-- Bookings with facility names
SELECT b.bookid, b.starttime, f.name
FROM bookings b
JOIN facilities f ON b.facid = f.facid
LIMIT 10;

Students always forget the ON clause. Let them make this mistake, see the Cartesian product, then fix it. "See this? 10,000 rows when you expected 100? That's why we need ON."

Then three-table JOINs:

-- Bookings with facility AND member names
SELECT f.name, m.surname, b.starttime
FROM bookings b
JOIN facilities f ON b.facid = f.facid
JOIN members m ON b.memid = m.memid
WHERE m.surname = 'Smith';

Slide 7 is LEFT JOIN. Use this query to show the difference:

-- Members who NEVER booked
SELECT m.firstname, m.surname
FROM members m
LEFT JOIN bookings b ON m.memid = b.memid
WHERE b.bookid IS NULL;

Key teaching moment: "LEFT JOIN keeps ALL members. WHERE b.bookid IS NULL finds members with NO matching bookings."

Student Practice (100+ min)

Now students work independently. Give them these challenges (no specific slide, just verbally or written on board):

Easy (10 queries, 30 min)

1. All facilities with membercost > 0
2. Bookings made in July 2012
3. Members whose surname starts with 'S'
4. Facilities with "Table" in the name
5. Top 5 most expensive facilities (guestcost)
6. Members who joined in 2012
7. Bookings with more than 3 slots
8. Facilities sorted by membercost descending
9. Count total facilities
10. Count total bookings

Medium (10 queries, 40 min)

11. Bookings with facility names
12. Bookings by 'David Jones'
13. All Tennis Court bookings with member names
14. Members who booked facility ID 0
15. Facilities with monthlymaintenance > $500
16. Members who never booked (LEFT JOIN)
17. Facilities never booked (LEFT JOIN from facilities)
18. Bookings where members paid (memid != 0)
19. Count bookings per facility
20. Members with more than 5 bookings

Hard (10 queries, 30 min)

21. Total revenue per facility (use CASE for member/guest cost)
22. Facility with highest revenue
23. Bookings in September 2012 with facility and member names
24. Average slots per facility
25. Members who booked Tennis Courts in August 2012
26. Revenue per month for each facility
27. Find facility most popular (most bookings)
28. List members and count their unique facilities booked
29. Bookings on weekends (DAYOFWEEK function)
30. Average booking duration per member

Walk around, help when stuck. Most common errors:

• Using = instead of LIKE for pattern matching
• Forgetting JOIN ON condition (Cartesian product)
• Using = NULL instead of IS NULL
• Ambiguous column names (forgot table aliases)

Wrap-up (10 min)

Have students share their solution for the revenue query (question 21). Multiple approaches are valid - show 2-3 different solutions if students found different ways.

Preview Lecture 4: "Next time we'll do aggregations - SUM, COUNT, GROUP BY. You'll answer questions like 'which member has spent the most money?'"

Homework

Write queries on their library database from Lecture 2: Find books by author, members who joined recently, books never borrowed, most borrowed book. Also recommend pgexercises.com for extra practice.