SQL
Unit 10 Summary - part 2
Why do we need database?
Click to Play an SQL game
Click the 3 lines on top left hand,
choose: Sprache wechsein (change language),
choose: English,
then, let's play together.
Oxford AQA IGCSE 2019
15.
A relational database contains the two tables Student and Class. The contents of these tables are shown in Figure 8.
​
Figure 8
​
​
Student
Class
15.1
How many records are there in the table Student? [1 mark]
​
__________________________________________________________________________________
​
Answer:
6
15.2
State the primary key for the table Class and explain why this is the primary key. [2 marks]
​
__________________________________________________________________________________
​
Answer:
ClassID; (Only) Unique (field);
​
​
​
​
15.3
Describe how a relationship has been created between the tables Student and Class. [2 marks]
​
__________________________________________________________________________________
​
Alternative answers:
​
-
The primary key of the Class table's ClassID is also in/is the foreign key in the Student table;
​​
-
There is a field that appears in both tables; called ClassID;
​
​
​
15.4
List the results of executing the following SQL query on the relational database in Figure 8. [3 marks]
​
​
SELECT Firstname, Lastname
FROM Student, Class
WHERE Level = "GCSE" AND
Student.ClassID = Class.ClassID
​
__________________________________________________________________________________
​
Answer:
​
Louise Jones, Paul Smith, Adam Harper
Oxford AQA IGCSE 2020
10.
A relational database stores information about the products that a shop sells and the sales that have been made.
The database contains two tables: Product and Sale. Some of the contents of these tables are shown in Figure 2.
Note that not all of the records in the tables are shown.
​
Figure 2
​
​
Product
Sale
10.1
Explain what a foreign key is and state the name of the field that has been used as a foreign key in this database. [2 marks]
​
__________________________________________________________________________________
​
Answer:
1 mark: Foreign key is the primary key of one table that is used in another table A. a field in a table that links it to another table by appearing in both
1 mark: ProductID
​
​
​
10.2
Write an SQL query to search the database and list all the sales of drinks.
For each sale the SaleID, ProductID, Description and QuantitySold should be listed. [4 marks]
​
__________________________________________________________________________________
​
Answer:
​
Solution 1:
SELECT SaleID, ProductID, Description, QuantitySold
FROM Product, Sale
WHERE Product.ProductID = Sale.ProductID
AND Category = "Drinks"
Solution 2:
SELECT SaleID, ProductID, Description, QuantitySold
FROM Product INNER JOIN Sale ON
Product.ProductID = Sale.ProductID
WHERE Category = "Drinks"
1 mark:
Correct four fields in the SELECT clause and no others, separated by commas.
​
1 mark:
Correct two tables in the FROM clause.
1 mark:
Condition Category = "Drinks" in WHERE clause.
1 mark:
Linking condition included in syntactically correct way in FROM or WHERE clause.
A. table names before fieldnames.
A. use of Alias/AS command eg FROM Product AS P then use of P as table name.
A. INNER JOIN written as one word i.e. INNERJOIN.
A. insertion of spaces into fieldnames.
A. use of " or ' as delimiters around Drinks.
I. unnecessary brackets.
DPT for unnecessary punctuation – allow one semicolon at the very end of the statement, but not at the end of each clause.
DPT missing commas in lists DPT for fieldname before table name.
10.3
When the shop receives new stock the values in the NumberInStock field of the Product table must be updated. Tick one box to show which SQL query is the correct one to change the NumberInStock of ProductID 203 from 10 to 15 [1 mark]
​
__________________________________________________________________________________
Answer:
1 mark: for correct row ticked
No mark: more than one row ticked
1 mark: use of alternative symbol than tick
Oxford AQA IGCSE Specimen Paper
11.
The following tables form a relational database used by a veterinarian’s surgery about appointments and pets.
11.1.
How many records are there in the Pet table? [1 mark]
​
__________________________________________________________________________________
​
​
​
11.2.
Which field is the primary key for the Appointment table? [1 mark]
​
__________________________________________________________________________________
11.3.
Which field is the foreign key in the Appointment table? [1 mark]
​
__________________________________________________________________________________
​
​
​
11.4.
List the results of executing the following SQL query on this relational database. [4 marks]
SELECT Date, OwnerName, TypeOfAnimal
FROM Appointment, Pet
WHERE Pet.PetID = Appointment.PetID AND VetName = 'Dr Marks'
ORDER BY Date DESC
__________________________________________________________________________________
​
​
​
11.5.
Complete the following SQL query so that it will find the names of all the cats and birds in this relational database. [3 marks]
​
SELECT Name
FROM Pet
WHERE TypeOfAnimal _________________
Oxford AQA IGCSE Mock Paper
​
07.
Figure 5 shows two tables which form a relational database. The database stores details of the products that can be purchased in a shop and when these items have been sold.
​
Figure 5
​
Product
Sale
07.1.
Which field is the primary key for the Sale table? [1 mark]
​
__________________________________________________________________________________
07.2.
Explain how the products in the Product table are linked to the sales in the Sale table. [1 mark]
​
__________________________________________________________________________________
​
​
07.3.
Explain how organising the relational database as two tables instead of one has eliminated data redundancy. [2 marks]
​
__________________________________________________________________________________
​
​
07.4
List the results of executing the following query on the relational database in Figure 5.
SELECT Name, Price, Quantity
FROM Product, Sale
WHERE Product.ProductID = Sale.ProductID
AND Category = "Pen" AND Date = "21/10/2017"
ORDER BY Quantity DESC [4 marks]
​
__________________________________________________________________________________
​
​
07.5
Shade one circle to indicate which SQL command could be used to delete a record that is in a table in the database. [1 mark]
