6 A database table, PLANT, is used to keep a record of plants sold by a nursery. The table has these fields: • NAME – name of plant • FLOWER – whether the plant flowers (True) or not (False) • POSITION – shade, partial shade or sun • SIZE – small, medium or large • PRICE – price in $ • NUMBERSOLD – how many sold A query-by-example grid has been completed to display only the price, name and number sold of small plants that do not flower. Field: NAME PRICE NUMBERSOLD SIZE FLOWER POSITION Table: PLANT Sort: Show: 3 3 3 Criteria: = "shade" or: Identify the errors in the query-by-example grid. … … … … … … Rewrite the corrected query-by-example grid. Field: Table: Sort: Show: Criteria: or: [5]

6 Any three from: • TABLE row not completed • POSITION column not required // POSITION criteria not required • No criteria set in the size column • No criteria set in the flower column Field: SIZE PRICE FLOWER NUMBERSOLD NAME Table: PLANT PLANT PLANT PLANT PLANT Sort: Show:      Criteria: ="small" False or: OR Field: SIZE PRICE FLOWER NUMBERSOLD NAME POSITION Table: PLANT PLANT PLANT PLANT PLANT PLANT Sort: Show:       Criteria: ="small" = False or: One mark for correct rows Field, Table and Show One mark for correct Criteria row 5

6 A pet supplier uses the database table, STOCK, to keep records of its products for pets. The fields are: Field name Description ProductID code to identify the product ProductName name of product ProductDescription information about the product Animal type of animal the product is for, e.g. cat, bird, horse ProductType type of product, e.g. food, toy, medicine InStock whether the product is in stock or not (a) (i) Identify the field that could have a Boolean data type. … [1] (ii) Identify the field that should be used as the primary key. … [1] (b) Complete the query-by-example grid to output the products intended for a cat that are in stock. Display only the primary key and the name of the products. The output should be sorted by the primary key. Field: Table: Sort: Show: Criteria: or: [4]

6(a)(i) InStock 1 6(a)(ii) ProductID 1 6(b) One mark for correct fieldnames One mark for correct table names and show fields One mark for correct sort One mark for correct search criteria in all columns Field: ProductID ProductName Animal InStock Table: STOCK STOCK STOCK STOCK Sort: Ascending Show:      Criteria: =“cat” =Yes or: 4

5 A marine wildlife rescue centre uses a database table, MARINE, to keep records of its creatures. Creature Class Quantity Ready for release Offspring Manta Ray Fish 3 Y N Short-tailed Albatross Bird 4 Y N Emperor Penguin Bird 50 Y Y Bluefin Tuna Fish 2 N N Manatee Mammal 4 Y N Hawksbill Turtle Reptile 10 Y Y Hammerhead Shark Fish 3 Y N Yellow-eyed Penguin Bird 4 Y N Kemp’s Ridley Sea Turtle Reptile 1 Y N (a) State how many fields and how many records are shown in this table. Number of fields … Number of records … [2] (b) Show the output that would be given by this query-by-example. Field: Creature Class Ready for release Table: MARINE MARINE MARINE Sort: Show: 3 3 Criteria: ="Bird" or: … … … [2] (c) Complete the query-by-example grid to display the creatures, in ascending order of quantity, that have no offspring and are ready for release. Display only the creature field. Field: Table: Sort: Show: Criteria: or: [4]

5(a) 5 fields 9 records 2 5(b) One mark for each correct column max two, Or two correct rows one mark Short-tailed Albatross Y Emperor Penguin Y Yellow-eyed Penguin Y 2 5(c) Field: Creature Quantity Offspring Ready for release Table: MARINE MARINE MARINE MARINE Sort: Ascending Show:     Criteria: =N =Y or: One mark for each correct field and table rows One mark for sort row One mark for show row One mark for correct criteria 4

6 A garden centre sells garden tools and stores details of these in a database table named TOOLS. Code is the primary key in the TOOLS table. Code Description Price ($) Quantity_Stock Quantity_Ordered GFLG Garden Fork 50.00 1 50 GSLG Garden Spade 50.00 11 0 GHLG Garden Hoe 45.00 8 0 HFSM Hand Fork 9.99 42 0 HSSM Hand Spade 9.99 40 0 HWSM Hand Weeder 9.99 11 0 HS20 Hose (20 metres) 45.00 10 0 HS35 Hose (35 metres) 60.00 2 0 HS50 Hose (50 metres) 75.00 20 60 YBLG Yard Brush 24.99 100 0 LMHD Lawn Mower 99.99 5 0 LMBT Lawn Mower (Battery) 249.99 7 0 LMPT Lawn Mower (Petrol) 349.99 10 25 TRBT Edge Trimmer (Battery) 79.99 15 0 TRPT Edge Trimmer (Petrol) 59.99 20 0 SHSM Shears 40.00 40 0 HCSM Hedge Clippers 40.00 45 0 (a) State the purpose of the primary key in the TOOLS table. … … … [1] (b) List the output from the data shown in the table TOOLS that would be given by this query-by- example. Field: Code Description Price ($) Quantity_Stock Quantity_Ordered Table: TOOLS TOOLS TOOLS TOOLS TOOLS Sort: Descending Show: ü ü ü Criteria: >40 >0 >0 or: … … … … … … [3] (c) Complete the query-by-example grid to output the tools where the quantity in stock is below 25. Only show the Code, Description and Quantity_Stock fields in ascending order of Code. Field: Table: Sort: Show: Criteria: or: [3]

6(a) To uniquely identify a product (in TOOLS) 1 6(b) HS50 Hose (50 metres) 60 GFLG Garden Fork 50 LMPT Lawn Mower (Petrol) 25 One mark for correct data One mark for correct format One mark for data in correct order 3 6(c) Field: Code Description Quantity_Stock Table: TOOLS TOOLS TOOLS Sort: Ascending Show:      Criteria: <25 or: One mark for each completely correct column (max three) 3

6 A database table, JUICE, is used to keep a record of cartons of fresh fruit juice available for sale. Juice code Fruit 1 Fruit 2 Size Volume (ml) Stock level LMO10 Mango Orange Large 1000 18 MOO05 Orange Orange Medium 500 8 SAM02 Apple Mango Small 200 25 SAA02 Apple Apple Small 200 50 SPP02 Pineapple Pineapple Small 200 10 MMM05 Mango Mango Medium 500 12 LMM10 Mango Mango Large 1000 5 MGG05 Guava Guava Medium 500 5 SMO02 Mango Orange Small 200 7 MOP05 Orange Pineapple Medium 500 12 LAA10 Apple Apple Large 1000 32 SGO02 Guava Orange Small 200 10 LPP10 Pineapple Pineapple Large 1000 3 LOO10 Orange Orange Large 1000 25 SOO02 Orange Orange Small 200 40 (a) Identify a suitable field to use as the primary key. State a reason for your choice. Field … Reason … … [2] (b) Complete the query-by-example grid to display only the stock level and size of all cartons containing only apple juice. Field: Table: Sort: Show: Criteria: or: [4]

6(a) One mark for field and one mark for reason Field Juice code Reason only unique identifier 2 6(b) Field: Fruit 1 Fruit 2 Size Stock level Table: JUICE JUICE JUICE JUICE Sort: Show:     Criteria: ="Apple" ="Apple" or: One mark for each correct column 4

Cambridge O Level Computer Science · 2210

O-Level Computer Science (2210) Databases past paper questions

Revise O-Level Computer Science (2210) — Databases with real Cambridge past-paper questions grouped by topic, each shown with its marking scheme. Working one topic at a time targets exactly the skill each question tests.

22 free questions from 2016–2021, each with its marking scheme. Open the full topical bank to filter by subtopic, paper, and year.