Computer Science · Grade 11

Active learning ideas

Advanced SQL Queries and Joins

Active learning helps students grasp SQL joins and advanced queries by immediately applying abstract concepts to real tables. Manipulating data visually through pair work, group challenges, and relay races reduces confusion between JOIN types and aggregation logic, which static readings often leave unclear.

• Ontario Curriculum Expectations
• CS.HS.D.5
• CS.HS.S.1

• 40–50 min
• Pairs → Whole Class
• 3 activities

Activity 01

• Collaborative Problem-Solving
• 45 min · Pairs

Format Name: Join Type Exploration

Students work in pairs with a pre-defined multi-table database (e.g., customers, orders, products). They are given specific scenarios and must write queries using INNER, LEFT, and RIGHT joins to retrieve the requested data, comparing the results of each join type.

Differentiate between various types of SQL joins (INNER, LEFT, RIGHT) and their use cases.

Facilitation Tip

During Pair Programming, have each pair predict the row count and columns before running their JOIN, then compare predictions to actual output to confront join type misconceptions.

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

Activity 02

• Collaborative Problem-Solving
• 40 min · Individual

Format Name: Subquery Challenge

Present students with a complex data retrieval problem that requires subqueries (e.g., finding customers who have ordered a specific product). Students individually write and test their subqueries, then share and critique their solutions with a small group.

Analyze how to optimize SQL queries for performance on large datasets.

Facilitation Tip

For Aggregation Challenges, distribute partially written queries so students focus on completing GROUP BY and HAVING clauses rather than starting from scratch.

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

Activity 03

• Collaborative Problem-Solving
• 50 min · Small Groups

Format Name: Performance Tuning Race

Provide students with a large dataset and an inefficient query. In small groups, they must identify the bottleneck, apply optimization techniques (like indexing or rewriting the query), and measure the performance improvement, competing to achieve the fastest execution time.

Construct a complex SQL query to extract specific insights from a multi-table database.

Facilitation Tip

In the Query Optimization Relay, use a timer and require students to explain their optimization choices aloud to the next team, reinforcing metacognitive habits.

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

A few notes on teaching this unit

Teach joins by starting with Venn diagrams drawn on the board, then immediately translating to SQL and running the query to show the diagram’s match. Avoid long lectures on indexing; instead, let students hit performance walls during timed challenges and then discover indexing together. Research shows that debugging slow queries together builds deeper understanding than listening to theory about execution plans.

By the end of these activities, students should confidently choose the correct JOIN based on table relationships and write GROUP BY queries with aggregation that return accurate, expected row counts. They will also explain why certain query structures perform better than others in class discussions and written reflections.

---

Watch Out for These Misconceptions

• During Pair Programming: Join Types Practice, watch for pairs who assume INNER JOIN, LEFT JOIN, and RIGHT JOIN always return the same results.

  Ask pairs to draw Venn diagrams before coding, then run the query and count rows to see how LEFT JOIN includes unmatched rows from the left table, correcting the overgeneralization immediately.

• During Aggregation Challenges, watch for students who believe NULL values are treated as zero in SUM or COUNT.

  Include a table with NULLs in the challenge, and have students test SUM and COUNT side by side, then wrap columns in COALESCE to see the difference in results.

• During Query Optimization Relay, watch for students who default to subqueries for every complex task, assuming they are always faster.

  Time the same query written with a JOIN and as a subquery, and display execution plans to let students observe how JOINs leverage indexes more effectively on large datasets.

---

Methods used in this brief

• Collaborative Problem-Solving