9. Databases
A section of Computer Science, 0478
Listing 10 of 13 questions
A shop that sells books has set up a new database table called BookList to store book details. Part of this table is given. CatNo Title Fiction Author PaperBack Price StockLevel BK01 The Princes’ Story Yes B Penn Yes 4.50 BK02 The Princesses’ Story Yes B Penn Yes 4.50 BK03 Computer Science No Way Yu Yes 19.99 BK04 The Modern World No P Patel No 25.00 BK05 The Ancient World Yes P Patel No 25.00 BK06 Computer Science No R Dale Yes 27.35 BK07 The Princes’ Story Yes B Penn No 12.50 BK08 The Princesses’ Story Yes B Penn No 12.50 BK12 Famous Five Yes E Bly Yes 2.75 BK15 Secret Seven Yes E Bly Yes 2.75 BK16 The Last Knight Yes P Mann Yes 5.99 BK17 The Dark Tower Yes P Mann Yes 5.99 BK19 The Final Chase Yes P Mann Yes 5.99 BK21 Maths Today Part 1 No B Ward Yes 6.75 BK22 Maths Today Part 2 No B Ward Yes 6.75 BK23 Maths Today Part 3 No B Ward Yes 6.75 BK26 Maths Today Workbook No B Ward Yes 6.75 BK27 Knitting for Beginners No A Smith Yes 6.99 BK30 Woodwork for Beginners No A Smith Yes 6.99 BK31 Networking for Beginners No A Smith Yes 6.99 State the number of records in this part of the database table. Give the name of the field that would be used for the primary key. State the reason for choosing this field for the primary key. Complete the table to identify the most appropriate data type for each field based on the data shown in the table BookList Field Data type CatNo Title Fiction Price Write the output from this structured query language (SQL) statement. SELECT CatNo, Title, Author FROM BookList WHERE StockLevel = 0; Complete this SQL statement to display all the titles by the author B Penn. SELECT FROM WHERE ;
0478_m23_qp_22
THEORY
2023
Paper 2, Variant 2
View
A storage unit rental company wants to set up a new database table for the storage units that can be rented. The table is called StorageUnits and needs to store these details: • SizeMetres – size in square metres • Position – first, second or third floor • Hoist – whether there is a hoist available for the transfer of items • PriceMonth – the price in dollars for a month’s rental • StorageID – the code to identify each storage unit, for example S123 Give the name of the field that would be used for the primary key. State the reason for choosing this field for the primary key. Complete the table to identify the most appropriate data type for these fields in the table StorageUnits Field Data type SizeMetres Position Hoist StorageID Complete this structured query language (SQL) statement to display only the storage code, price and size in square metres of all the storage units where there is a hoist available. SELECT FROM WHERE ;
0478_m24_qp_22
THEORY
2024
Paper 2, Variant 2
View
A model shop wants to set up a database to help with stock control of the model figures available for sale. The shop wants to store this information about the model figures: Field 1 – catalogue number, for example MD1234 Field 2 – description, for example ‘small white dog’ Field 3 – number in stock, for example 5 Field 4 – the price of each model, for example 7.40 Field 5 – if the model has already been painted, yes or no. The shop needs five fields for each record. Give a suitable name and data type for each field. Field 1 name Data type Field 2 name Data type Field 3 name Data type Field 4 name Data type Field 5 name Data type Give the name of the field that should be used for the primary key. State why this field is used as the primary key. Structured query language (SQL) is used to query data stored in this database. State what these SQL commands are used for. SELECT FROM WHERE
0478_w23_qp_21
THEORY
2023
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 ; , ,
0478_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 = ;
0478_s23_qp_21
THEORY
2023
Paper 2, Variant 1
View
A database table called Site1 stores details of some holiday homes at a holiday park. The database shows the type of home, number of guests, whether it is privately owned and the weekly rate to hire it. Name Type Private Rate$ NumberGuest Bay Lodge Lodge NO Bay View Cabin NO Blue Skies Cabin NO Cliff View Cabin NO Coppice Lodge Lodge NO Green Lodge Lodge NO Henry Cabin YES Hikers’ Rest Retreat NO Poppy Cabin NO Summer Joy Retreat YES Valley View Cabin NO West Lodge Lodge YES State the number of fields and the number of records in this database table. Fields Records Describe the purpose of a primary key. The database uses the data types: • alphanumeric • character • Boolean • integer • real • date/time. Complete the table to show the most appropriate data type for each field. Field Data type Type Private Rate$ NumberGuest Give the output that would be produced by the structured query language (SQL) statement: SELECT Name, NumberGuest, Rate$ FROM Site1 WHERE NumberGuest >= 10;
0478_s23_qp_23
THEORY
2023
Paper 2, Variant 3
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";
0478_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 ;
0478_w23_qp_23
THEORY
2023
Paper 2, Variant 3
View
A shop that sells cheese has set up a new database table called CheeseStock to store details of the cheeses available for sale. Part of this table is given. ChNo Name InStock SupplierCode PricePerKg WeightKg CH01 American Yes XYZ 4.50 20.0 CH02 Brie Yes XYZ 7.50 21.0 CH03 Burrata No IMP 13.75 0.0 CH04 Camembert No ABC 16.85 0.0 CH05 Cheddar Yes ABC 5.00 50.0 CH06 Comté No SPC 7.35 0.0 CH07 Cottage Yes XYZ 4.50 3.0 CH08 Cream Yes XYZ 5.50 6.5 CH12 Emmental Yes IMP 2.75 1.5 CH15 Feta Yes IMP 12.75 12.0 CH16 Fontina Yes SPC 15.99 1.2 CH17 Gorgonzola Yes SPC 15.25 0.3 CH19 Gouda Yes SPC 7.99 2.5 CH21 Gruyère No SPC 16.75 0.0 CH22 Halloumi Yes IMP 4.75 15.0 CH23 Havarti No SPC 6.75 0.0 CH27 Manchego No IMP 13.99 0.0 CH30 Manouri No IMP 18.50 0.0 CH31 Mascarpone No SPC 12.99 0.0 State the number of records in this part of the database table. Give the name of the field that would be used for the primary key. State the reason for choosing this field for the primary key. Write the output from this structured query language (SQL) statement. SELECT ChNo, WeightKg FROM CheeseStock WHERE SupplierCode = 'ABC'; Complete this SQL statement to display only the name of all the cheeses that are out of stock. SELECT FROM WHERE ; Explain how one of the lines in your statement in part could be changed to display the same information.
0478_w24_qp_21
THEORY
2024
Paper 2, Variant 1
View
Questions Discovered
13