Computing · Secondary 4

Active learning ideas

SQL: Aggregate Functions

Active learning works for SQL aggregate functions because students need to see how calculations change with different data groupings. Hands-on execution reveals why COUNT(column) and COUNT(*) differ, and why mixing aggregates with non-aggregates fails without GROUP BY. These moments of direct observation stick longer than theory alone.

• MOE Syllabus Outcomes
• MOE: Data Management - S4
• MOE: SQL Programming - S4

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

Activity 01

• Problem-Based Learning
• 30 min · Pairs

Pair Query Relay: Aggregate Scenarios

Provide a dataset of student scores. Pairs alternate writing and running queries to answer prompts like 'Find AVG score per subject' or 'COUNT students above 80'. Switch roles after each query, then share correct results with the class.

Explain how aggregate functions summarize data in meaningful ways.

Facilitation Tip

During Pair Query Relay, have students alternate writing queries and explaining their next step before running it, building verbal reasoning skills.

What to look for

Provide students with a small table of sample sales data (e.g., ProductID, Quantity, Price). Ask them to write SQL queries to: 1. Count the total number of sales transactions. 2. Calculate the average price of items sold. 3. Find the maximum quantity sold in a single transaction.

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

Activity 02

• Problem-Based Learning
• 45 min · Small Groups

Small Group: Sales Database Analysis

Give groups a sales database. They write queries using SUM for totals, AVG for averages per region, and GROUP BY for category summaries. Groups create a one-page report with results and present to class.

Analyze scenarios where each aggregate function would be most appropriate.

Facilitation Tip

In Small Group Sales Database Analysis, assign each group a different function to master, then rotate findings so all students see each function in action.

What to look for

Present a scenario: 'A school wants to know the average score for each subject from a 'Scores' table containing StudentID, Subject, and Score columns.' Ask students to write the SQL query using AVG and GROUP BY to answer this. Review responses for correct syntax and function usage.

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

Activity 03

• Problem-Based Learning
• 25 min · Whole Class

Whole Class: Function Match-Up

Display scenarios on the board, such as 'highest temperature' or 'total goals scored'. Class votes on best aggregate, then tests queries on a shared demo database projected for all to see.

Construct SQL queries using aggregate functions to answer specific analytical questions.

Facilitation Tip

For Function Match-Up, ask students to justify why each function pair belongs together before revealing the answer, reinforcing conceptual connections.

What to look for

Pose the question: 'Imagine you are analyzing website traffic data. Which aggregate function would you use to find out the peak number of daily visitors last month, and why?' Facilitate a brief class discussion comparing MIN, MAX, and AVG in this context.

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

Activity 04

• Problem-Based Learning
• 35 min · Individual

Individual: Custom Dataset Challenge

Students import their own small dataset, like sports stats. They independently write three aggregate queries to summarize it, then pair up to validate and refine each other's work.

Explain how aggregate functions summarize data in meaningful ways.

Facilitation Tip

During the Individual Custom Dataset Challenge, provide starter code with intentional errors for students to fix, reinforcing careful syntax habits.

What to look for

Provide students with a small table of sample sales data (e.g., ProductID, Quantity, Price). Ask them to write SQL queries to: 1. Count the total number of sales transactions. 2. Calculate the average price of items sold. 3. Find the maximum quantity sold in a single transaction.

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

A few notes on teaching this unit

Teach this topic by starting with ungrouped aggregates to show single-row results, then introduce GROUP BY gradually with visual comparisons. Use error-first demos to show students why SQL rejects mixing non-aggregates without GROUP BY. Research shows students learn aggregates best when they see the data shrink and expand with each GROUP BY clause, so emphasize the shape of results tables over memorizing syntax.

Successful learning shows when students confidently choose the right aggregate for a question, write correct GROUP BY clauses, and explain why NULLs affect COUNT(column). They should debug their own queries and help peers adjust syntax during group work.

---

Watch Out for These Misconceptions

• During Pair Query Relay, watch for students who assume COUNT(column) and COUNT(*) always return the same number.

  Ask pairs to insert a row with a NULL in the counted column, re-run both queries, and compare outputs to see the difference firsthand.

• During Function Match-Up, watch for students who believe aggregates can be mixed freely with non-aggregates.

  Provide a table with a GROUP BY clause that mixes a non-aggregate column, run it to show the error, then have students fix it by either adding GROUP BY or removing the non-aggregate.

• During Small Group Sales Database Analysis, watch for students who think GROUP BY applies only to numeric columns.

  Prompt groups to GROUP BY a text column like product category, run the query, and observe how SUM or AVG behaves with categorical data.

---

Methods used in this brief

• Problem-Based Learning