Technologies · Year 10

Active learning ideas

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.

• ACARA Content Descriptions
• AC9DT10P02

• 25–45 min
• Pairs → Whole Class
• 4 activities

Activity 01

• 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.

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.

What to look for

Present students with a simplified database schema (e.g., Students, Courses, Enrollments). Ask them to write an SQL query using an INNER JOIN to list all students enrolled in a specific course, and another query using a LEFT JOIN to list all students and the courses they are enrolled in (including those not enrolled in any).

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

Activity 02

• 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.

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.

What to look for

Provide students with a scenario: 'A company wants to find the average order value for each customer who has placed more than 5 orders.' Ask them to write the SQL query using GROUP BY, HAVING, and an aggregate function (AVG, COUNT) to solve this. They should also briefly explain why HAVING is used instead of WHERE in this case.

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

Activity 03

• 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.

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.

What to look for

Give pairs of students a complex SQL query with nested subqueries. One student writes the query, and the other predicts the output step-by-step, explaining their reasoning. They then execute the query to compare results and discuss any discrepancies.

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

Activity 04

• 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.

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.

What to look for

Present students with a simplified database schema (e.g., Students, Courses, Enrollments). Ask them to write an SQL query using an INNER JOIN to list all students enrolled in a specific course, and another query using a LEFT JOIN to list all students and the courses they are enrolled in (including those not enrolled in any).

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

A few notes on teaching this unit

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.

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

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

  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.

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

  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.

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

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

---

Methods used in this brief

• Problem-Based Learning