Exam style questions
Download a pdf version of the test
Use the space below each question or a pen and paper to write your answer. When complete click the button for the answer and mark scheme.
NOTE: Answers typed into the browser will not be retained if you leave the page or refresh
Questions
Flat-file database
Explain the problems with flat file databases with examples from the one above and explain how they can be overcome. (6 marks)
They lead to data redundancy where data is stored more than once taking up more space(1) e.g. Repeating the store name for each employee who works there(1)
They also lead to data inconsistency where typos lead to differing
records(1) e.g. 2 rows say Springfield West is store ID 002 and one says it is 003 or Springfield East is missing a letter in one entry.(1)
The solution is a relational database(1) where the data is lnked by key fields used as
foreign keys in another table.(1)
Relational database
Explain how foreign keys are used to link tables using the example of the relational database above. (3 marks)
Foreign keys are the primary key from one table included in another to create a link.(1) Store ID is a foreign key in the employee table.(1) By knowing the store ID we can look up the users tore name.(1)
Any 3 of the 4 for full marks
Give an example of a primary key from the relational database above: 1 mark
Give an example of a record from the employee table in the relational database above: 1 mark
Give an example of a field from the employee table in the relational database above: 1 mark
Primary key: Either EmployeeID or StoreID(1)
Record: 001, Homer, Checkout Operator, £9.50, 001; or any other single row(1)
Field: EmployeeID, Employee name, Role Wage per hr or StoreID(1)
Write a query that displays the employeeid, name and role for workers at store 002 sorted alphabetically by name and then say what would be returned by it. (5 marks)
SELECT Employee ID, Employee name, Role
FROM tblEmployees
WHERE StoreID = 002
SORT BY Employee name
1 mark per correct line
output:
003, Bart, Warehouse manager
004, Lisa, Store manager
005, Margaret, Marketing
1 mark for output
Write a query that returns the Employee name, role and wage per hr for anyone with manager in their role, sorted by role, then say what would be returned by it. (5 marks)
SELECT Employee name, Role, Wage per hr
FROM tblEmployees
WHERE Employee name = "*manager"
SORT BY Role
1 mark per correct line
output:
Marge, Store manager, 50
Lisa, Store manager, 50
Bart, Warehouse manager,20
1 mark for output
Write a query that returns the Employee id, name, wage per hr and store name for anyone earning more than £25 per hr, sorted by store name ascending then wage per hr descending, then say what would be returned by it. (5 marks)
SELECT Employee ID, Employee name, Store name
FROM tblEmployees, tblStores
WHERE Wage per hr > 25 and Employees.StoreID = Stores.StoreID
SORT BY Store name, Wage per hr descending
1 mark per correct line
output:
002,Marge, Store manager, 50, Springfield East
Lisa, Store manager, 50
Margaret, Marketing, 35
1 mark for output
Relational database repeated so you don't have to keep scrolling up
Write a query to insert a new store with id 003 and name Shelbyville (2 marks)
INSERT INTO tblStores
VALUES (003,'Shelbyville')
1 mark per correct line
Abraham's wage per hr rises to £7.50. Write an update query to correct this in the database. (3 marks)
UPDATE tblEmployees
SET Wage per hr = 7.5
WHERE Employee name = 'Abraham'
1 mark per correct line could also say where Employee ID = 006 as well.
Homer is fired for gross negligence. Write a query to delete him from the employee table. (2 marks)
DELETE FROM tblEmployees
WHERE Employee ID = 001
1 mark per correct line
<< Previous: Update and delete SQL Databases home GCSE home Next: Revision Flashcards >>