KS4 – Databases and SQL Summative assessment – Answers
Summative assessment – Answers
Databases and SQL
CaptureID Registered_owner Car_reg Speed
1 Sara Bibi JN03HNM 83
2 Danny Judd YM15PTO 70
3 Cara Lichfield LG01KZK 75
4 Abeni Barmore UT02SKK 68
5 Baki Kaatz XB18NVA 67
6 Cara Lichfield LG01KZK 72
The table above (tblSpeeds) is data collected from a speed camera on a motorway.
The camera captures the car registration plate and the speed at which the car was
travelling. The car reg is used to find the registered owner of the car, and the data is
stored in a flat file database.
Q1. Which of the fields above would be most suitable to be the primary key? [1]
A. CaptureID
B. Registered_owner
C. Car_reg
D. Speed
Q2. Justify your answer to question 1. [1]
CaptureID is the field that is guaranteed to be unique. All of the other data in this
table is not guaranteed to be unique.
Q3. What would be the most appropriate data type for car_reg?
A. TEXT
Page 1 Last updated: 18-06-21
KS4 – Databases and SQL Summative assessment – Answers
B. INTEGER
C. REAL
D. NULL
Q4. What would be the most appropriate data type for Speed?
A. TEXT
B. INTEGER
C. REAL
D. NULL
Q5. The data is in a flat file database. Describe one problem that can arise from having a
flat file database. [2]
Answers relating to either redundancy or inconsistency with a valid description.
Q6. Write down the exact output for the following query: [1]
1 SELECT Registered_owner
2 FROM tblSpeeds
3 WHERE Speed > 75;
Sara Bibi
Page 2 Last updated: 18-06-21
KS4 – Databases and SQL Summative assessment – Answers
Q7. The authorities want to offer a speed awareness course to those drivers recorded as
travelling between 71 and 80. Write a query to extract the appropriate records from
tblSpeeds. You must show all the fields in your output. [3]
1 SELECT *
2 FROM tblSpeeds
3 WHERE Speed BETWEEN 71 AND 80;
One mark for each correct line
Q8. The camera has captured a new car travelling down the motorway. Write an SQL
script to insert the following data into tblSpeeds. [4]
Registered_owner Car_reg Speed
Allen Heard CM20YGD 70
1 INSERT INTO tblSpeeds (Registered_owner, Car_reg, Speed)
2 VALUES ("Allen Heard","CM20YGD",70);
1 mark for INSERT INTO
1 mark for adding the correct fields to insert into (not including CaptureID)
1 mark for VALUES
1 mark for correctly adding the values with speech marks around the strings and no
speech marks around the integer
Q9. The registered owner of vehicle YM15PTO has written to say that they are no longer
the registered owner. Write a script to update the registered_owner field in
tblSpeeds for this vehicle to ‘Asif Shah’. [3]
Page 3 Last updated: 18-06-21
KS4 – Databases and SQL Summative assessment – Answers
1 UPDATE tblSpeeds
2 SET registered_owner = "Asif Shah"
3 WHERE car_reg = "YM15PTO";
1 mark for each correct line
Q10. It has been proven that the camera needed calibrating and an incorrect speed was
recorded for CaptureID number 6.
Write an SQL query that will delete this record from tblSpeeds. [2]
1 DELETE FROM tblSpeeds
2 WHERE CaptureID = 6;
1 mark for each correct line
Resources are updated regularly — the latest version is available at: ncce.io/tcc.
This resource is licensed under the Open Government Licence, version 3. For more information on this
licence, see ncce.io/ogl.
Page 4 Last updated: 18-06-21
