O-LevelComputer ScienceDatabases2210/23 · Oct/Nov 2024 · Q117 Marks

Building materials are sold in bags. A new database table called BuildStock has been set up to store details about the materials for sale. Part of this table is given. MtNo Name InStock WeightKg PricePerBag NumberBags MT01 Builders sand Yes 50 4.50 50 MT02 Sharp sand Yes 25 3.50 21 MT03 Red sand No 50 2.75 0 MT04 Cement No 25 6.85 0 MT05 Chippings Yes 50 35.00 50 MT06 Cobbles No 75 67.35 0 MT07 Pebbles small Yes 50 34.50 3 MT08 Pebbles medium Yes 25 25.50 10 MT12 Pebbles large Yes 75 62.75 20 MT15 Washed gravel Yes 50 12.75 12 MT16 Pea gravel Yes 100 15.95 24

(a)Write the output from this structured query language (SQL) statement. SELECT MtNo, Name FROM BuildStock WHERE WeightKg = 75 ORDER BY PricePerBag;[3]
(b)(i)Complete this SQL statement to display only the names of all the materials that are out of stock. SELECT FROM WHERE[2]
(b)(ii)Explain how another SQL statement using a different field could be used to display the same information.[2]

Mark scheme

Marking points from the official mark scheme (Oct/Nov 2024). The mark or mark type is shown in brackets.

[3] (a) One mark for each line (max two)
(a) One mark for order of lines
(a) MT12 Pebbles large
(a) MT06 Cobbles
[2] (b)(i) One mark for correct SELECT and FROM clauses, one mark for correct WHERE clause
(b)(i) SELECT Name
(b)(i) FROM BuildStock
(b)(i) WHERE NOT InStock // InStock = FALSE / NO /"No" // WHERE NumberBags = 0;
[2] (b)(ii) Two marks for either point
(b)(ii) WHERE clause can check the InStock field (1) for FALSE / No (1)
(b)(ii) or
(b)(ii) WHERE clause can check the NumberBags field (1) for 0 (1)

On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.