9. Databases
A section of Computer Science, 2210
Listing 8 of 8 questions
A database table called SoftDrinks stores details of the soft drinks sold by a small shop. Field Example data Name Cola Supplier Cambridge Beverages Container Can SizeCl NumberInStock ReorderLevel Reordered Yes State whether any of the given fields would be suitable as a primary key and give a reason for your answer. Complete the structured query language (SQL) statement to return the number of cans the shop has in stock. SELECT ( ) FROM WHERE = ;
2210_s24_qp_21
THEORY
2024
Paper 2, Variant 1
View
A television subscription service has a new database table named Contract to store details of their subscribers’ contracts. The table contains these fields: • ContractNumber – the contract number, for example CT567 • Months – the length of the contract in months, for example 6 • EndDate – the date the contract finishes, for example 30 November 2024 • News – the news service, yes or no • Movie – the movie service, yes or no • Sport – the sports service, yes or no • Junior – the children’s service, yes or no. Identify the field that will be the most appropriate primary key for this table. Complete the table to identify the most appropriate data type for these fields in Contract Field Data type ContractNumber Months EndDate Sport Explain the purpose of these structured query language (SQL) statements. Statement 1: SELECT SUM (Months) FROM Contract; Statement 2: SELECT COUNT (News) FROM Contract WHERE News; Statement 1 Statement 2 Complete this SQL statement to find the contract numbers of the subscribers that take both the news and sports services. SELECT FROM Contract WHERE AND ;
2210_s24_qp_22
THEORY
2024
Paper 2, Variant 2
View
A database table called TVRange shows the main features and prices of a range of televisions. TVCode ScreenSize Satellite SmartTV SoundBar Price$ TV90SaSmSd YES YES YES 9750.00 TV75SaSmSd YES YES YES 8500.00 TV75SaSd YES NO YES 8000.00 TV65SaSmSd YES YES YES 6000.00 TV65SmSd NO YES YES 5000.00 TV65SaSd YES NO YES 5000.00 TV55SaSmSd YES YES YES 4000.00 TV55SaSd YES NO YES 3500.00 TV55SmSd NO YES YES 3500.00 TV50SaSmSd YES YES YES 2500.00 TV50Sa YES NO NO 1750.00 TV50Sm NO YES NO 1750.00 TV40Sa YES NO NO 1200.00 TV40 NO NO NO 950.00 TV32 NO NO NO 650.00 Give the name of the field that is most suitable to be the primary key. State the reason for this choice. Field Reason The database uses the data types: • text • character • Boolean • integer • real • date/time. Complete the table to show the most appropriate data type for each field. Each data type must be different. Field Data type TVCode ScreenSize SmartTV Price$ Complete the structured query language (SQL) query to return the television (Tcode, screen size and price of all Smart TVs in the database table. SELECT TVCode, , TVRange WHERE SmartTV = ;
2210_s23_qp_21
THEORY
2023
Paper 2, Variant 1
View
A database table called Horses stores details about the horses kept at a horse sanctuary. Code Breed BreedOrigin Gender Age Arrived H002 Arabian Saudi Arabia M 28/09/2022 H004 Percheron France M 30/10/2022 H010 Friesian Netherlands M 15/11/2022 H011 Fjord Norway F 17/11/2022 H012 Clydesdale Scotland M 18/11/2022 H015 Arabian Saudi Arabia F 15/12/2022 H016 Arabian Saudi Arabia F 15/12/2022 H017 Clydesdale Scotland F 16/01/2023 H019 Percheron France M 16/01/2023 H025 Percheron France M 16/01/2023 H026 Clydesdale Scotland F 20/01/2023 H030 Clydesdale Scotland M 20/01/2023 H032 Fjord Norway M 24/03/2023 H033 Arabian Saudi Arabia F 27/04/2023 H034 Clydesdale Scotland F 14/06/2023 H035 Fjord Norway M 15/06/2023 H036 Friesian Netherlands F 20/07/2023 H037 Friesian Netherlands M 20/07/2023 State the number of records in this database table. Give the name of the field that is most suitable to be the primary key. State the reason for this choice. Field Reason The database only allows the data types: • Boolean • character • date/time • integer • real • text. Complete the table to show the most appropriate data type for each field. Each data type must be different. Field Data type Breed Gender Age Arrived Complete the structured query language (SQL) to return the code and breed of all the horses whose breed originated in Scotland. Code, Breed, FROM WHERE = "Scotland";
2210_w23_qp_22
THEORY
2023
Paper 2, Variant 2
View
A sanctuary for pheasants has set up a new database table called PheasantList to store details of the different species of bird at the sanctuary. Part of this table is given, showing: species, description, number of birds at the sanctuary, if the birds are breeding or not, and number of young born this year. Species Description NumberBirds Breeding Young Edwards blue-black with white tail Yes Japanese green dark green with pale grey tail Yes Reeves golden, white and red scaled plumage Yes Crawfords Kalij glossy blue-black plumage No Crested fireback blue-black with black tail No True silver white laced top half and black lower half Yes Siamese fireback grey plumage with crimson legs and feet No Mikado iridescent plumage with white striped wings Yes Red junglefowl many colours Yes Himalayan monal many colours with metallic green crest Yes White eared white with ear tufts Yes Brown eared brown with ear tufts Yes Ring necked long tail with white ring neck Yes Golden rainbow coloured Yes State the number of records and fields in this part of the database table. Records Fields Give the name of a field that could be used for the primary key. Explain why the sanctuary might decide not to use the field in as the primary key. A new field SpeciesID is added to the database table. This field contains a six-character code, for example Ph0001. Give a reason why this field would be a better primary key. Write the output that would be given by this structured query language (SQL) statement: SELECT Species, Description FROM PheasantList WHERE NumberBirds > 6; Complete this SQL statement to display all the species of pheasant where the birds are breeding and there were no young born this year: SELECT FROM WHERE ;
2210_w23_qp_23
THEORY
2023
Paper 2, Variant 3
View
A database table called Booking28 stores details of hotel rooms and bookings for the week beginning Monday 7 July 2025. RoomNo Type Guests Rate$ Mon Tue Wed Thu Fri Sat Sun 101D Double 99.99 T T T T F T T 102D Double 99.99 T T T F T T T 103F Family 150.00 T T T T T T T 104S Single 72.50 F T T F T T T 105S Single 72.50 F T T F T T T 106T Twin 120.00 T T T T F T T 201F Family 160.00 F F T T T T T 202D Double 120.00 T F T T T T T 203T Twin 120.00 T F T T T T T 204T Twin 125.00 T F T F T T T 205S Single 79.99 T F T T F T T 301D Double 200.00 F T T F F T T 302T Twin 200.00 T T T T F T T 303P Suite 500.00 T T T T F T T 304P Suite 700.00 F F F F T T T State the number of fields and records in this database table. Fields Records State the reason why the Type field would not be suitable as a primary key. The database uses only the data types: • alphanumeric • character • Boolean • integer • real • date/time. Complete the table to show the fields that could have the given data types. Only one field name is required in each box and each field name must be different. Field Data type alphanumeric Boolean real integer Give the output that would be produced by the structured query language (SQL) statement: SELECT RoomNo, Type, Guests, Rate$ FROM Booking28 WHERE Mon <> T;
2210_w24_qp_22
THEORY
2024
Paper 2, Variant 2
View
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 4.50 MT02 Sharp sand Yes 3.50 MT03 Red sand No 2.75 MT04 Cement No 6.85 MT05 Chippings Yes 35.00 MT06 Cobbles No 67.35 MT07 Pebbles small Yes 34.50 MT08 Pebbles medium Yes 25.50 MT12 Pebbles large Yes 62.75 MT15 Washed gravel Yes 12.75 MT16 Pea gravel Yes 15.95 Write the output from this structured query language (SQL) statement. SELECT MtNo, Name FROM BuildStock WHERE WeightKg = 75 ORDER BY PricePerBag; Complete this SQL statement to display only the names of all the materials that are out of stock. SELECT FROM WHERE Explain how another SQL statement using a different field could be used to display the same information.
2210_w24_qp_23
THEORY
2024
Paper 2, Variant 3
View
Questions Discovered
8