A science class records plant growth experiment data in a table called EXPERIMENT. ReadingID PlantID Condition Day Height LeafCount EX01 P1 Sunlight 1 5.0 4...
Question 1 Report
A science class records plant growth experiment data in a table called EXPERIMENT.
| ReadingID | PlantID | Condition | Day | Height | LeafCount |
|---|---|---|---|---|---|
| EX01 | P1 | Sunlight | 1 | 5.0 | 4 |
| EX02 | P2 | Shade | 1 | 5.0 | 4 |
| EX03 | P3 | Sunlight | 1 | 4.5 | 3 |
| EX04 | P1 | Sunlight | 7 | 12.0 | 8 |
| EX05 | P2 | Shade | 7 | 7.5 | 5 |
| EX06 | P3 | Sunlight | 7 | 11.0 | 7 |
| EX07 | P1 | Sunlight | 14 | 18.0 | 12 |
| EX08 | P2 | Shade | 14 | 9.0 | 6 |
(a) Write an SQL query to find the average Height for each Condition on Day 7. [3]
Answer Details
(a) Finding the average height by condition on day 7: [3]
SELECT Condition, AVG(Height)
FROM EXPERIMENT
WHERE Day = 7
GROUP BY Condition[1] for AVG(Height), [1] for WHERE Day = 7, [1] for GROUP BY Condition.
The WHERE clause first filters to only Day 7 readings, then GROUP BY splits them by Condition:
- Day 7 Sunlight readings: P1 (12.0) and P3 (11.0). Average = (12.0 + 11.0) / 2 = 11.5
- Day 7 Shade readings: P2 (7.5). Average = 7.5 / 1 = 7.5
Expected output:
| Condition | AVG(Height) |
|---|---|
| Sunlight | 11.5 |
| Shade | 7.5 |
This result shows that plants grown in sunlight averaged 4.0 cm taller than the shade plant on day 7, which is a meaningful scientific observation. Note that the WHERE filtering happens before the GROUP BY aggregation.
Download The App On Google Playstore
Everything you need to excel in your exams
