Databases and SQL: Question 5
Syllabus 9.4
A small aquarium keeps details of every fish tank it maintains in a single database table called TANK, shown below.
| TankID | Species | WaterTempC | FishCount | Overcrowded |
|---|---|---|---|---|
| T01 | Clownfish | 25.5 | 18 | False |
| T02 | Neon Tetra | 24.0 | 32 | True |
| T03 | Guppy | 23.5 | 22 | True |
| T04 | Angelfish | 26.0 | 15 | False |
| T05 | Neon Tetra | 24.5 | 28 | True |
| T06 | Clownfish | 25.0 | 20 | False |
An aquarium assistant runs this SQL statement:
SELECT COUNT(TankID)
FROM TANK
WHERE FishCount > 20;
What value does this statement return?
Show worked solution Hide worked solution
Worked solution
Step 1: Apply the WHERE clause
Check each tank’s FishCount against the condition “greater than 20” (not “20 or more”):
| TankID | FishCount | FishCount > 20? |
|---|---|---|
| T01 | 18 | No |
| T02 | 32 | Yes |
| T03 | 22 | Yes |
| T04 | 15 | No |
| T05 | 28 | Yes |
| T06 | 20 | No. 20 is not greater than 20 |
Step 2: Apply COUNT(TankID)
COUNT counts how many rows matched the condition, not the FishCount values themselves. The rows that matched are T02, T03 and T05, three rows in total.
Step 3: Confirm the match
COUNT(TankID) therefore returns 3. (Adding the FishCount values of the matching rows, 32 + 22 + 28 = 82, would be what a SUM statement returns instead, and counting every row without the WHERE clause would give 6.)
Final answer
The statement returns 3, option A.