Advanced SQL Queries

Active learning works because joining tables and filtering groups are abstract operations that become concrete when students manipulate real data. Hands-on activities turn SQL syntax into visible results, helping students see how joins expand or contract rows and how aggregates reshape information.

• Year 10
• Technologies
• 4 activities
• 25 min–45 min

Learning Objectives

1. Construct SQL queries that retrieve data from three or more related tables using various join types.
2. Compare and contrast the functionality of the GROUP BY and HAVING clauses in filtering and summarizing grouped data.
3. Analyze the execution flow of complex SQL queries containing nested subqueries to predict the final output.
4. Synthesize data from multiple tables to answer specific business questions using aggregate functions and conditional logic.

Problem-Based Learning

⏱ 35 min·Pairs

Pairs: Query Relay Challenge

Partners alternate adding one clause (SELECT, FROM, JOIN, WHERE, GROUP BY) to a base query on shared devices. The other partner tests it immediately and suggests fixes. Groups present their complete query and results to the class.

Prepare & details

Construct an SQL query to retrieve data from multiple tables.

Facilitation Tip: During the Query Relay Challenge, hand students partial queries on cards so they must complete and pass the next step, forcing peer explanation of each clause.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 45 min·Small Groups

Small Groups: Join Puzzle Stations

Set up stations with sample databases on customer orders and products. Groups write JOIN queries to answer tasks like total sales per category. Rotate stations, then compare queries for efficiency.

Prepare & details

Compare the use of 'GROUP BY' and 'HAVING' clauses.

Facilitation Tip: At Join Puzzle Stations, place table printouts on walls so students physically move to match keys, making one-to-many relationships visible.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 30 min·Whole Class

Whole Class: Subquery Prediction Contest

Display a complex query with nested subquery on the board. Students predict output individually on paper, then discuss in whole class before executing. Award points for accurate predictions.

Prepare & details

Predict the output of a complex SQL query involving nested subqueries.

Facilitation Tip: In the Subquery Prediction Contest, require students to write their predicted output before running any query, then compare live results to their predictions.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Problem-Based Learning

⏱ 25 min·Individual

Individual: Aggregate Debug Lab

Provide broken queries with GROUP BY and HAVING errors on worksheets or online editors. Students identify issues, rewrite, and verify outputs against sample data. Share one fix with a partner.

Prepare & details

Construct an SQL query to retrieve data from multiple tables.

Facilitation Tip: For the Aggregate Debug Lab, provide queries with incorrect GROUP BY or HAVING clauses and ask students to trace the error using colored pens on printed schema sheets.

Setup: Groups at tables with access to research materials

Materials: Problem scenario document, KWL chart or inquiry framework, Resource library, Solution presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Teaching This Topic

Teach joins by starting with Venn diagrams on paper, then moving to schema diagrams that label primary and foreign keys. Emphasize that HAVING comes after GROUP BY by having students write queries on sticky notes and physically reorder them. Avoid letting students rely solely on memorizing syntax; instead, have them explain each clause’s role in plain language before coding.

What to Expect

Students will confidently combine tables with different joins, filter groups with HAVING, and explain why subquery placement matters. They will predict query outputs before execution and debug incorrect results using the database schema.

Watch Out for These Misconceptions

Common Misconception

During the Query Relay Challenge, watch for students who assume all JOINs produce duplicates because they see extra rows after an INNER JOIN.

What to Teach Instead

Have pairs trace each row from the left table to its matches in the right table using printed data samples, marking duplicates only when multiple matches exist in a one-to-many relationship.

Common Misconception

During Join Puzzle Stations, watch for students who confuse HAVING with WHERE when filtering groups.

What to Teach Instead

Ask groups to physically separate data cards into groups, then apply a HAVING condition by removing entire groups, contrasting this with WHERE which filters individual cards before grouping.

Common Misconception

During the Subquery Prediction Contest, watch for students who think subqueries always run slower than joins regardless of context.

What to Teach Instead

Run side-by-side timing activities with sample data sets of varying sizes, having students record execution times and discuss when joins outperform subqueries.

Assessment Ideas

Quick Check

After the Query Relay Challenge, give pairs a new schema and ask them to write an INNER JOIN and a LEFT JOIN query, then explain the difference in row counts before executing.

Exit Ticket

After Join Puzzle Stations, provide a scenario requiring GROUP BY and HAVING, asking students to write the query and briefly justify their use of HAVING over WHERE.

Peer Assessment

During the Subquery Prediction Contest, have pairs swap predicted outputs and reasoning, then execute the query together to reconcile any differences in their step-by-step predictions.

Extensions & Scaffolding

• Challenge: Ask students to rewrite a nested subquery as a join and compare execution plans using EXPLAIN.
• Scaffolding: Provide a template with labeled join conditions and aggregate placeholders for students to fill in.
• Deeper: Introduce window functions to calculate running totals or rankings within groups, then predict results using sample data.

Key Vocabulary

INNER JOIN	Combines rows from two tables based on a matching condition, returning only rows where the join condition is met in both tables.
LEFT JOIN	Returns all rows from the left table and the matched rows from the right table; if there is no match, the result is NULL on the right side.
Subquery	A query nested inside another SQL query, often used to filter data or provide values for the outer query.
Aggregate Function	Functions like COUNT, SUM, AVG, MIN, MAX that perform a calculation on a set of values and return a single value.
HAVING Clause	Filters groups created by the GROUP BY clause based on a specified condition, similar to WHERE but for groups.