Studyledge

Databases and SQL: Question 8

Syllabus 9.4

Structured 6 marks

Greenfield Weather Station records readings taken at two sites in a single database table called READING, shown below.

ReadingID StationName TemperatureC RainfallMm Sunny
RD01 North Ridge 18.0 2.0 True
RD02 North Ridge 21.0 0.0 True
RD03 South Bay 25.0 5.5 False
RD04 South Bay 20.0 12.0 False
RD05 North Ridge 15.0 8.0 False

(a) A student runs this SQL statement:

SELECT AVG(TemperatureC)
FROM READING
WHERE StationName = "North Ridge";

Using the table, work out the value produced by this statement, showing your working. [3]

(b) Write an SQL statement that will output the average TemperatureC of every reading where RainfallMm is greater than 5. [3]

Show worked solution Hide worked solution

Worked solution

Part (a): Tracing the SQL statement

Step 1. Apply the WHERE clause. Keep only rows where StationName is “North Ridge”:

ReadingIDTemperatureC
RD0118.0
RD0221.0
RD0515.0

(RD03 and RD04 are excluded because their StationName is “South Bay”.)

Step 2, apply AVG(TemperatureC). Add the matching values together, then divide by how many rows matched:

18.0 + 21.0 + 15.0 = 54.0

54.0 ÷ 3 = 18.0

The statement returns 18.0.

Part (b): Writing a new SQL statement

The requirement filters rows before averaging, so a WHERE clause is needed alongside AVG:

SELECT AVG(TemperatureC)
FROM READING
WHERE RainfallMm > 5;

Checking against the table: RD03 (RainfallMm 5.5), RD04 (RainfallMm 12.0) and RD05 (RainfallMm 8.0) all satisfy RainfallMm > 5; RD01 (2.0) and RD02 (0.0) do not. Their TemperatureC values are 25.0, 20.0 and 15.0:

25.0 + 20.0 + 15.0 = 60.0

60.0 ÷ 3 = 20.0

The statement returns 20.0.

Final answers

  • (a) SELECT AVG(TemperatureC) FROM READING WHERE StationName = "North Ridge";, returns 18.0
  • (b) SELECT AVG(TemperatureC) FROM READING WHERE RainfallMm > 5;, returns 20.0