# SQL Ordering & Aggregate Functions (ORDER BY, SUM, COUNT)

- **Track:** Cambridge IGCSE™ Computer Science (0478 / 0984) (CAMBRIDGE IGCSE 0478)
- **Module:** Section 9: Relational Databases & SQL
- **Estimated Duration:** 10 minutes
- **Access:** Open Theory (All-Access for Interactive IDE & Autotests)

---

## Theory & Conceptual Background

SELECT chooses fields, FROM chooses a table and WHERE filters records. ORDER BY sorts the resulting records. ASC sorts ascending; DESC sorts descending. Do not confuse sorting with filtering.

```sql
SELECT Name, Score
FROM Results
WHERE Score >= 50
ORDER BY Score DESC;
```

## Worked aggregates

Suppose three selected records have scores 4,7,9. SUM(Score) returns 20. COUNT(*) returns 3. These answer different questions: how much in total, and how many records. A WHERE condition can restrict which records contribute.

```sql
SELECT SUM(Score)
FROM Results
WHERE Team = 'Blue';
```

For Blue scores 4 and 9 and a Red score 7, the filtered sum is 13. Do not add the Red score merely because it is in the same table.

## Independent practice

Write a query counting all records, then change it to count only scores at least 50. Explain the difference between returning selected rows and returning one aggregate. GROUP BY and multi-table queries are optional extension and are not needed here.

---

## Interactive Exercises & Autotest Specifications

### Exercise 1: Exam-Style Check: Behavior of ORDER BY DESC in SQL (Multiple choice, 25 XP)

**Instructions:**
A school administrator runs the following SQL query on the `Students` table:

```sql
SELECT StudentName, ExamScore
FROM Students
ORDER BY ExamScore DESC;
```

Which row will appear as the first result in the returned table?

**Options:**
- **A**: The student who achieved the highest numerical ExamScore.
- **B**: The student who achieved the lowest numerical ExamScore.
- **C**: The student whose StudentName begins with letter 'A'.
- **D**: The student record that was entered into the database first chronologically.


**Autotest Expectations:**
- **Correct result of descending order identified**: `selection_match` -> Expected: `opt_highest_score`

### Exercise 2: Independent Practice: Filtered total (Sql, 30 XP)

**Instructions:**
Return the sum of Score for Blue-team records.

Use the Results table: Name (TEXT), Team (TEXT), Score (INTEGER).


**Starter Code:**
```text
{"schema": {"table_name": "Results", "columns": [{"name": "Name", "type": "TEXT"}, {"name": "Team", "type": "TEXT"}, {"name": "Score", "type": "INTEGER"}]}, "dataset": [["Inez", "Blue", 72], ["Omar", "Red", 49], ["Suri", "Blue", 90], ["Tao", "Red", 50], ["Uma", "Blue", 38]], "template": "-- Write your query\n"}
```


### Exercise 3: Independent Practice: Sort selected rows (Sql, 30 XP)

**Instructions:**
Return Name and Score for Blue-team records, highest Score first.

Use the Results table: Name (TEXT), Team (TEXT), Score (INTEGER).


**Starter Code:**
```text
{"schema": {"table_name": "Results", "columns": [{"name": "Name", "type": "TEXT"}, {"name": "Team", "type": "TEXT"}, {"name": "Score", "type": "INTEGER"}]}, "dataset": [["Inez", "Blue", 72], ["Omar", "Red", 49], ["Suri", "Blue", 90], ["Tao", "Red", 50], ["Uma", "Blue", 38]], "template": "-- Write your query\n"}
```


### Exercise 4: Independent Practice: Count qualifying records (Sql, 30 XP)

**Instructions:**
Return the number of records with Score at least 50.

Use the Results table: Name (TEXT), Team (TEXT), Score (INTEGER).


**Starter Code:**
```text
{"schema": {"table_name": "Results", "columns": [{"name": "Name", "type": "TEXT"}, {"name": "Team", "type": "TEXT"}, {"name": "Score", "type": "INTEGER"}]}, "dataset": [["Inez", "Blue", 72], ["Omar", "Red", 49], ["Suri", "Blue", 90], ["Tao", "Red", 50], ["Uma", "Blue", 38]], "template": "-- Write your query\n"}
```