These eight original questions use one small database. Write the rows you expect before opening each answer.
The set belongs to SQL reasoning in a fictional database. A teacher can go through your working in the one-hour trial class (from RM50), a taught lesson on the Computer Science topic you choose.
The database
Table Pupil: (PupilID, Name, Form)
| PupilID | Name | Form |
|---|---|---|
| 1 | Aiman | 4 |
| 2 | Bee Ling | 4 |
| 3 | Chandran | 5 |
| 4 | Dalia | 5 |
| 5 | Emir | 5 |
Table Score: (ScoreID, PupilID, Subject, Mark)
| ScoreID | PupilID | Subject | Mark |
|---|---|---|---|
| 1 | 1 | Maths | 70 |
| 2 | 1 | Science | 55 |
| 3 | 2 | Maths | 82 |
| 4 | 3 | Maths | 48 |
| 5 | 3 | Science | 64 |
| 6 | 3 | Art | 90 |
| 7 | 4 | Science | NULL |
Questions
Question 1. How many rows does SELECT Name FROM Pupil WHERE Form = 5 return?
Answer
Test each row. Chandran, Dalia and Emir are in Form 5, so the result has 3 rows.
Question 2. How many rows does SELECT * FROM Score WHERE Mark >= 60 return?
Answer
Marks of 70, 82, 64 and 90 pass. The 55 and 48 fail. The NULL in ScoreID 7 is unknown, so that row is left out. The result has 4 rows: ScoreID 1, 3, 5 and 6.
Question 3. How many rows does SELECT * FROM Score WHERE Mark < 60 OR Mark >= 60 return? Is it all 7?
Answer
It returns 6 rows, not 7. Every number is either below 60 or at least 60, but NULL is not a number. The condition is unknown for ScoreID 7, so that row is dropped.
Question 4. How many rows does SELECT Name, Subject, Mark FROM Pupil INNER JOIN Score ON Pupil.PupilID = Score.PupilID return?
Answer
Count the pairs: Aiman 2, Bee Ling 1, Chandran 3, Dalia 1, Emir 0. The total is 2 + 1 + 3 + 1 + 0 = 7 rows. Emir is missing because he has no score, yet the result still equals the Score table because every score has one pupil.
Question 5. Give the result of SELECT PupilID, AVG(Mark) FROM Score GROUP BY PupilID.
Answer
There is one row per PupilID, so 4 rows.
- PupilID 1: (70 + 55) ÷ 2 = 62.5
- PupilID 2: 82
- PupilID 3: (48 + 64 + 90) ÷ 3 = 202 ÷ 3, which is about 67.33
- PupilID 4: its only mark is NULL, so the average is NULL
Question 6. The query in Question 5 is changed to end with HAVING AVG(Mark) > 65. Which PupilID values remain?
Answer
HAVING filters the groups after averaging. PupilID 2 (82) and PupilID 3 (about 67.33) pass. PupilID 1 (62.5) fails, and PupilID 4 (NULL) is unknown, so it fails too. The result is 2 rows.
Question 7. For Subject = ‘Science’, what do COUNT(*) and COUNT(Mark) return?
Answer
Three Science rows exist: ScoreID 2, 5 and 7. COUNT(*) counts rows, so it returns 3. COUNT(Mark) skips the NULL, so it returns 2.
Question 8. Give the result of SELECT Name, COUNT(ScoreID) FROM Pupil LEFT JOIN Score ON Pupil.PupilID = Score.PupilID GROUP BY Name.
Answer
The LEFT JOIN keeps every pupil. The counts are Aiman 2, Bee Ling 1, Chandran 3, Dalia 1 and Emir 0. COUNT(ScoreID) gives 0 for Emir because his ScoreID is NULL after the join. COUNT(*) would wrongly give 1, since the joined row exists.
If you got these wrong
- Questions 1 to 3 test row filtering and NULL: see predicting which rows a WHERE condition will include.
- Question 4 tests pair counting: see why a join returns more rows than expected.
- Questions 5 and 6 test groups: see row filtering versus group filtering.
- Questions 7 and 8 test empty groups: see testing an aggregate query with empty groups and missing values.