Skip to the worksheet
Revision Library
Choose a subject

Databases and SQL - Worksheets, Questions and Revision

12 original exam-style questions - 8 pages of questions with a full mark scheme - free printable PDF.

This topic is chapter 9 of A Level Computer Science Topic Workbook 1.

Revision Library
revisionlibrary.co.uk
A-Level · AQA

1.9 Databases and SQL

AQA 7517 · Calculators not allowed · about 105 minutes
Total Marks
Name: _______________________________    Date: ____ / ____ / ______
Answer ALL questions. Show all your working.

Elm Grove Music Academy: a database case study

Original scenario written for Revision Library.

Elm Grove Music Academy offers one-to-one instrumental lessons and is building a relational database to replace its old spreadsheet system. Questions 1, 3, 4, 10 and 11 take the academy's original, unnormalised booking data and normalise it step by step to Third Normal Form (3NF), and model it with an entity relationship (ER) diagram. Questions 2, 5, 6, 7, 8 and 12 use the resulting normalised database (with sample data added) to write and interpret SQL statements. Questions 12 to 14 look at how the database is used and maintained once it is in service: transaction processing, concurrent access, and the trade-off between normalisation and denormalisation.

1
Before building its database, Elm Grove Music Academy's systems analyst reviews some key relational database terms.
(a)Define the term 'primary key'.(2)
(b)Define the term 'foreign key'.(2)
(c)Define the term 'candidate key'.(2)
(d)Using an example from Elm Grove Music Academy's booking data, explain what is meant by a 'composite key'.(2)
(Total for Question 1 is 8 marks)
2
Figure 2: Elm Grove Music Academy's database (following normalisation in Questions 3, 4 and 10), with sample data added. Questions 2, 5, 6, 7, 8 and 12 all refer to this database.

Student table:
StudentIDStudentNameStudentPhone
S01Priya Chatterjee07911 123456
S02Callum Reid07922 654321
S03Aisha Bello07933 789012
S04Jack Whitfield07944 345678
S05Freya Thomson07955 901234
S06Zainab Hussain07966 567890

Instrument table:
InstrumentIDInstrumentName
I01Piano
I02Guitar
I03Violin
I04Drums

Instructor table:
InstructorIDInstructorName
T01Daniel Osei
T02Fiona Clarke
T03Marcus Lindqvist

Booking table (primary key: StudentID + InstrumentID):
StudentIDInstrumentIDInstructorIDDayTimeDurationCost
S01I01T01Monday16:003018.00
S01I02T02Wednesday17:004524.00
S02I01T01Tuesday15:303018.00
S03I03T03Monday18:004522.50
S03I02T02Thursday10:003021.00
S04I01T01Monday09:006032.00
S04I04T02Friday16:303020.00
S05I02T02Wednesday09:303021.00

(Duration is in minutes; Cost is in GBP (£).)
(a)Write an SQL query to display the StudentID, InstrumentID and Time of every booking that takes place on a Monday, ordered by Time, earliest first.(4)
(b)State the StudentID, InstrumentID and Time values that this query would return, in the order they would appear.(2)
(Total for Question 2 is 6 marks)
3
Elm Grove Music Academy's original spreadsheet stores each student's lesson bookings as a repeating group, as shown below (Figure 1). Assume the business rule: a student may book lessons for several different instruments, but can only have one active booking per instrument (so a student cannot have two separate bookings for, say, Piano at the same time).

Figure 1 (unnormalised, UNF):
StudentIDStudentNameStudentPhoneLessons (InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)
S01Priya Chatterjee07911 123456(I01, Piano, T01, Daniel Osei, Monday, 16:00, 30, 18.00), (I02, Guitar, T02, Fiona Clarke, Wednesday, 17:00, 45, 24.00)
S02Callum Reid07922 654321(I01, Piano, T01, Daniel Osei, Tuesday, 15:30, 30, 18.00)

(Duration is in minutes; Cost is in GBP (£).)
(a)State one problem that would occur if this unnormalised (UNF) data was stored and processed in this form.(2)
(b)Convert the data in Figure 1 into First Normal Form (1NF) by removing the repeating group. Write out the resulting flat table, including every attribute, and state a suitable primary key.(4)
(Total for Question 3 is 6 marks)
4
Question 3(b)'s 1NF relation is:

Booking1NF(StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)

with primary key (StudentID, InstrumentID).
(a)Explain why (StudentID, InstrumentID) is a suitable primary key for this relation, rather than StudentID alone or InstrumentID alone.(2)
(b)Identify TWO partial dependencies in this 1NF relation. For each, state the determinant and the dependent attribute(s).(4)
(c)Hence convert the relation into Second Normal Form (2NF). Write out each resulting relation, underlining the primary key of each.(4)
(Total for Question 4 is 10 marks)
5
Questions 2, 5, 6, 7, 8 and 12 refer to Figure 2 (Question 2).
(a)Explain why an INNER JOIN between the Booking and Student tables is needed to display a student's name alongside their booking details, following the normalisation carried out in Questions 4 and 10.(2)
(b)Write an SQL query, using an INNER JOIN, to display the StudentName and Cost for every booking taught by instructor T01 (Daniel Osei).(4)
(c)State the result this query would return.(2)
(Total for Question 5 is 8 marks)
6
Elm Grove Music Academy needs to make the following changes to its database. Assume each part below is applied independently to the original data in Figure 2 (Question 2).
(a)A new student, Harriet Osborne, joins the academy. Her StudentID is S07 and her phone number is 07977 112233. Write an SQL statement to add her record to the Student table.(2)
(b)Callum Reid (S02) changes his phone number to 07900 445566. Write an SQL statement to update his record.(2)
(c)Freya Thomson (S05) cancels her Wednesday Guitar lesson with instructor T02. Write an SQL statement to delete only this booking record (do not delete her Student record).(2)
(Total for Question 6 is 6 marks)
7
Using the original Student and Booking data in Figure 2 (Question 2) - that is, not including any of the changes made in Question 6.
(a)Write an SQL query, using a subquery, to display the StudentID and StudentName of every student who has not booked any lessons.(4)
(b)State the result this query would return.(1)
(Total for Question 7 is 5 marks)
8
Figure 2 (Question 2) is used again for this question.
(a)A database administrator attempts to run the statement: DELETE FROM Student WHERE StudentID = 'S01'; Explain why the DBMS might reject this statement.(3)
(b)Describe how the database could instead be configured to automatically remove Priya Chatterjee's booking records at the same time as her student record, and give one risk of using this approach.(3)
(Total for Question 8 is 6 marks)
9
Elm Grove Music Academy's manager suggests that, to make monthly reports run faster, the database should be deliberately denormalised, for example by storing StudentName directly in the Booking table again (as it was before Question 4), instead of looking it up via a join each time. Discuss the advantages and disadvantages of denormalising the database in this way, compared with keeping it fully normalised to 3NF.
(Total for Question 9 is 6 marks)
10
The 2NF Booking relation from Question 4(c) is:

Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost), primary key (StudentID, InstrumentID).
(a)State what is meant by a transitive dependency between non-key attributes.(2)
(b)Identify the transitive dependency present in the 2NF Booking relation above.(2)
(c)Convert the relations into Third Normal Form (3NF). Write out the complete, final set of relations for Elm Grove Music Academy's database, underlining each primary key and labelling each foreign key (FK).(6)
(Total for Question 10 is 10 marks)
11
Using the final 3NF relations from Question 10(c), an entity relationship (ER) diagram is to be produced for Elm Grove Music Academy's database.
(a)Identify the four entities required, and state which one is needed only to resolve a many-to-many relationship (a 'link' or associative entity).(2)
(b)Draw an entity relationship diagram for this database. Show each entity, its primary key (underlined) and any foreign keys, and label the cardinality of every relationship.(6)
(c)Explain why a link entity (BOOKING) is needed to model the relationship between STUDENT and INSTRUMENT, rather than connecting them with a single direct relationship.(2)
(Total for Question 11 is 10 marks)
12
Figure 2 (Question 2) is used again for this question.
(a)Write an SQL query to display, for each instructor, their InstructorName and the total number of bookings they teach. Only include instructors who teach more than one booking, and order the output with the highest number of bookings first.(6)
(b)State the final output of this query.(2)
(Total for Question 12 is 8 marks)
Mark scheme · 1.9 Databases and SQL

Question 1

  • (a) an attribute, or minimal set of attributes, that uniquely identifies each record/row in a table (oe)B1
  • (a) it is chosen from the table's candidate keys and must not contain null values (oe)B1
  • (a) Answer: A primary key is a field (or minimal combination of fields) that uniquely identifies each record in a table. It is chosen from the table's candidate keys and cannot contain null values.
  • (b) an attribute in one table that refers to (matches the values of) the primary key of another, or the same, table (oe)B1
  • (b) it creates a link between the two tables and is used to enforce referential integrity between them (oe)B1
  • (b) Answer: A foreign key is a field in one table that refers to the primary key of another (or the same) table. It creates a link between the two tables and is used to enforce referential integrity between them.
  • (c) an attribute, or minimal combination of attributes, whose values are unique for every record in a table (i.e. could be used to identify records) (oe)B1
  • (c) a table may have more than one candidate key; one is chosen to become the primary key and the rest become alternate keys (oe)B1
  • (c) Answer: A candidate key is a field, or minimal combination of fields, whose values are unique for every record in a table, so it could be used to identify records. A table can have several candidate keys; one is chosen as the primary key, and the rest become alternate keys.
  • (d) a primary/candidate key made up of two or more attributes, where no single one of those attributes alone is sufficiently unique to identify a record (oe)B1
  • (d) valid example, e.g. (StudentID, InstrumentID) together uniquely identify a booking, because StudentID alone repeats for a student with several instruments, and InstrumentID alone repeats across different students (oe any valid example)B1
  • (d) Answer: A composite key is a primary key made up of two or more attributes, where no single attribute on its own is unique enough to identify a record. For example, in a booking record, StudentID alone is not unique (a student can book several instruments) and InstrumentID alone is not unique (an instrument can be booked by several students), but together (StudentID, InstrumentID) uniquely identify each booking.

Question 2

  • (a) SELECT StudentID, InstrumentID, TimeB1
  • (a) FROM BookingB1
  • (a) WHERE Day = 'Monday'B1
  • (a) ORDER BY Time ASC (ASC may be omitted, as it is the default) caoB1
  • (a) Answer:
    SELECT StudentID, InstrumentID, Time
    FROM Booking
    WHERE Day = 'Monday'
    ORDER BY Time ASC;
  • (b) (S04, I01, 09:00) firstB1
  • (b) then (S01, I01, 16:00), then (S03, I03, 18:00) caoB1
  • (b) Answer:S04, I01, 09:00
    S01, I01, 16:00
    S03, I03, 18:00

Question 3

  • (a) it contains a repeating group / a variable number of lesson entries per student record, which is difficult for a DBMS to store and query efficiently (oe)B1
  • (a) instrument and instructor details are duplicated across lesson entries (e.g. 'Piano'/'Daniel Osei' repeated for S01 and S02), wasting storage and risking update anomalies/inconsistency if, for example, an instructor's name changes (oe)B1
  • (a) Answer: The Lessons column is a repeating group, so each student record can hold a different number of lesson entries, which is difficult for a DBMS to store and search efficiently. It also duplicates data, such as instrument and instructor names, across multiple rows, wasting storage and risking inconsistency if, for example, an instructor's name changes and not every copy is updated.
  • (b) one row per (student, instrument) booking, so no nested/repeating group remainsB1
  • (b) all attributes present and atomic: StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, CostB1
  • (b) three rows shown, correctly matching the sample data (two for S01, one for S02)B1
  • (b) primary key correctly stated as the composite key (StudentID, InstrumentID)B1
  • (b) Answer:Booking1NF(StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)
    StudentIDStudentNameStudentPhoneInstrumentIDInstrumentNameInstructorIDInstructorNameDayTimeDurationCost
    S01Priya Chatterjee07911 123456I01PianoT01Daniel OseiMonday16:003018.00
    S01Priya Chatterjee07911 123456I02GuitarT02Fiona ClarkeWednesday17:004524.00
    S02Callum Reid07922 654321I01PianoT01Daniel OseiTuesday15:303018.00

    Primary key: (StudentID, InstrumentID).

Question 4

  • (a) StudentID alone is not unique, because a student can appear in more than one row (once for each instrument they book) (oe)B1
  • (a) InstrumentID alone is not unique, because an instrument can be booked by more than one student; only the combination of the two uniquely identifies each row (oe)B1
  • (a) Answer: StudentID alone cannot be the key, because a student who books more than one instrument appears in more than one row (e.g. S01 appears twice). InstrumentID alone cannot be the key either, because the same instrument can be booked by more than one student (e.g. I01 appears twice, for S01 and S02). Only the combination (StudentID, InstrumentID) is unique for every row.
  • (b) determinant: StudentIDB1
  • (b) dependent attribute(s): StudentName and StudentPhone (both depend on StudentID alone, part of the composite key)B1
  • (b) determinant: InstrumentIDB1
  • (b) dependent attribute: InstrumentName (depends on InstrumentID alone, part of the composite key)B1
  • (b) Answer: Partial dependency 1: StudentID -> StudentName, StudentPhone (both depend only on part of the composite key, StudentID). Partial dependency 2: InstrumentID -> InstrumentName (depends only on part of the composite key, InstrumentID).
  • (c) Student(StudentID, StudentName, StudentPhone) - primary key StudentIDB1
  • (c) Instrument(InstrumentID, InstrumentName) - primary key InstrumentIDB1
  • (c) Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID)B1
  • (c) StudentID and InstrumentID within Booking correctly identified as foreign keys, referencing Student and Instrument respectivelyB1
  • (c) Answer:Student(StudentID (PK), StudentName, StudentPhone)
    Instrument(InstrumentID (PK), InstrumentName)
    Booking(StudentID (PK, FK to Student), InstrumentID (PK, FK to Instrument), InstructorID, InstructorName, Day, Time, Duration, Cost)

Question 5

  • (a) after normalisation, StudentName is stored only in the Student table, not in Booking, having been removed to eliminate the partial dependency from Question 4 (oe)B1
  • (a) an (INNER) JOIN matches each row in Booking to the corresponding row in Student using the shared StudentID field, combining data from both tables in one result (oe)B1
  • (a) Answer: StudentName no longer appears in Booking, because it was moved into its own Student table when the partial dependency was removed in Question 4. An INNER JOIN is therefore needed to match each Booking row to the matching Student row (using the shared StudentID field) so that both a booking's details and the student's name can appear in the same result.
  • (b) SELECT Student.StudentName, Booking.CostB1
  • (b) FROM BookingB1
  • (b) INNER JOIN Student ON Booking.StudentID = Student.StudentIDB1
  • (b) WHERE Booking.InstructorID = 'T01' (accept an equivalent correct alternative, e.g. using table aliases)B1
  • (b) Answer:
    SELECT Student.StudentName, Booking.Cost
    FROM Booking
    INNER JOIN Student ON Booking.StudentID = Student.StudentID
    WHERE Booking.InstructorID = 'T01';
  • (c) Priya Chatterjee, 18.00 and Callum Reid, 18.00B1
  • (c) Jack Whitfield, 32.00 (all three rows correct, any order) caoB1
  • (c) Answer:Priya Chatterjee, 18.00
    Callum Reid, 18.00
    Jack Whitfield, 32.00

Question 6

  • (a) INSERT INTO Student (StudentID, StudentName, StudentPhone)B1
  • (a) VALUES ('S07', 'Harriet Osborne', '07977 112233'); - correct syntax, with values in matching order caoB1
  • (a) Answer:
    INSERT INTO Student (StudentID, StudentName, StudentPhone)
    VALUES ('S07', 'Harriet Osborne', '07977 112233');
  • (b) UPDATE Student SET StudentPhone = '07900 445566'B1
  • (b) WHERE StudentID = 'S02'; - WHERE clause present, so only S02's record is affected caoB1
  • (b) Answer:
    UPDATE Student
    SET StudentPhone = '07900 445566'
    WHERE StudentID = 'S02';
  • (c) DELETE FROM BookingB1
  • (c) WHERE StudentID = 'S05' AND InstrumentID = 'I02'; - correct WHERE clause uniquely identifying the one booking, using the composite primary key caoB1
  • (c) Answer:
    DELETE FROM Booking
    WHERE StudentID = 'S05' AND InstrumentID = 'I02';

Question 7

  • (a) SELECT StudentID, StudentNameB1
  • (a) FROM StudentB1
  • (a) WHERE StudentID NOT IN (SELECT StudentID FROM Booking)B1
  • (a) whole query correct and would execute without error (accept an equivalent NOT EXISTS or LEFT JOIN ... WHERE Booking.StudentID IS NULL solution) caoB1
  • (a) Answer:
    SELECT StudentID, StudentName
    FROM Student
    WHERE StudentID NOT IN (SELECT StudentID FROM Booking);
  • (b) S06, Zainab Hussain (the only student with no booking record) caoB1
  • (b) Answer: S06, Zainab Hussain

Question 8

  • (a) S01 (Priya Chatterjee) is referenced as a foreign key value in the Booking table, since she has existing booking records (oe)B1
  • (a) deleting the Student row would leave those Booking rows referencing a StudentID that no longer exists - an orphaned/dangling foreign key (oe)B1
  • (a) the DBMS enforces referential integrity, so by default it restricts (rejects) any delete that would break this rule (oe)B1
  • (a) Answer: S01 (Priya Chatterjee) appears as a foreign key value in the Booking table, since she has two existing bookings. If the Student row for S01 were deleted, those Booking rows would reference a StudentID that no longer exists, breaking referential integrity. To prevent this, the DBMS by default rejects (restricts) the delete.
  • (b) the foreign key constraint (StudentID in Booking referencing Student) could be set to use ON DELETE CASCADE (oe)B1
  • (b) this automatically deletes every matching row in Booking (i.e. every booking for that student) when the referenced Student row is deleted (oe)B1
  • (b) valid risk, e.g. booking/historical data could be lost unintentionally, or a cascading delete can remove more data than the user realises, especially if it chains through further related tables (oe)B1
  • (b) Answer: The foreign key from Booking to Student could be defined with ON DELETE CASCADE. This means that when a Student row is deleted, every Booking row referencing that StudentID is automatically deleted too, so S01's bookings would be removed at the same time as her student record. One risk is that this booking data (which might be needed for records or invoicing) could be lost unintentionally, and a cascading delete can remove more data than an administrator realises, especially if other tables also reference Booking.

Question 9

  • Level 3 A balanced discussion gives accurate advantages and disadvantages of denormalising, applied to the academy's database, and reaches a justified overall view.5-6
  • Level 2 Some accurate advantages and/or disadvantages are given, with limited balance between the two, and/or limited application to the academy's database.3-4
  • Level 1 A one-sided or generic comment on normalisation/denormalisation is made, with little reference to the academy's database.1-2
  • Level 0 No creditable content.0
  • Indicative content:
    • Advantage of denormalising: fewer joins are needed to produce a report (e.g. StudentName is read directly from Booking), which can make read-heavy queries faster, especially over large tables.
    • Advantage of denormalising: simpler queries may be easier for less experienced staff or report-writers to construct correctly.
    • Disadvantage of denormalising: StudentName is duplicated across every booking row for that student, wasting storage and reintroducing the update anomaly identified in Question 3 (if a student's name changes, every duplicated copy must be updated, or the data becomes inconsistent).
    • Disadvantage of denormalising: increases the risk of insertion/deletion anomalies and makes it harder to guarantee data integrity, which matters more for a booking system that is updated frequently, as opposed to a rarely-updated reporting/data-warehouse system.
    • Valid overall view: for a small, frequently-updated operational database like this one, the integrity benefits of 3NF are likely to outweigh the reporting speed gained from denormalising, unless the reports are run very often on very large volumes of data (any justified conclusion credited).

Question 10

  • (a) occurs when a non-key attribute depends on another non-key attribute, rather than depending directly on the (whole) primary key (oe)B1
  • (a) i.e. Key -> A -> B, where A and B are both non-key attributes, so B depends on the key only indirectly, via A (oe)B1
  • (a) Answer: A transitive dependency occurs when a non-key attribute depends on another non-key attribute, rather than depending directly on the primary key. That is, Key -> A -> B, where A and B are both non-key attributes, so B is only indirectly dependent on the key, via A.
  • (b) InstructorID -> InstructorName, i.e. (StudentID, InstrumentID) -> InstructorID -> InstructorNameB1
  • (b) InstructorName depends on InstructorID, which is itself a non-key attribute of Booking, not directly on the primary key (oe)B1
  • (b) Answer: InstructorName depends on InstructorID (InstructorID -> InstructorName), and InstructorID is itself a non-key attribute of Booking, not part of its primary key. So InstructorName depends on the primary key (StudentID, InstrumentID) only transitively, via InstructorID.
  • (c) Student(StudentID, StudentName, StudentPhone) - primary key StudentID (unchanged from 2NF)B1
  • (c) Instrument(InstrumentID, InstrumentName) - primary key InstrumentID (unchanged from 2NF)B1
  • (c) new relation Instructor(InstructorID, InstructorName) created, primary key InstructorIDB1
  • (c) Booking(StudentID, InstrumentID, InstructorID, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID), with InstructorName removedB1
  • (c) StudentID and InstrumentID within Booking correctly labelled FK (to Student and Instrument respectively)B1
  • (c) InstructorID within Booking correctly labelled FK (to Instructor)B1
  • (c) Answer:Student(StudentID (PK), StudentName, StudentPhone)
    Instrument(InstrumentID (PK), InstrumentName)
    Instructor(InstructorID (PK), InstructorName)
    Booking(StudentID (PK, FK to Student), InstrumentID (PK, FK to Instrument), InstructorID (FK to Instructor), Day, Time, Duration, Cost)

Question 11

  • (a) STUDENT, INSTRUMENT, INSTRUCTOR and BOOKING correctly identified as the four entitiesB1
  • (a) BOOKING correctly identified as the link/associative entityB1
  • (a) Answer: The four entities are STUDENT, INSTRUMENT, INSTRUCTOR and BOOKING. BOOKING is the link (associative) entity, needed to resolve the many-to-many relationship between STUDENT and INSTRUMENT.
  • (b) STUDENT box shown correctly, with primary key StudentID underlinedB1
  • (b) INSTRUMENT box shown correctly, with primary key InstrumentID underlinedB1
  • (b) INSTRUCTOR box shown correctly, with primary key InstructorID underlinedB1
  • (b) BOOKING box shown correctly, with composite primary key (StudentID, InstrumentID) underlined and InstructorID labelled as a foreign keyB1
  • (b) STUDENT-BOOKING and INSTRUMENT-BOOKING relationships both correctly labelled 1:M (one-to-many), with the 'one' end at STUDENT/INSTRUMENT and the 'many' end at BOOKINGB1
  • (b) INSTRUCTOR-BOOKING relationship correctly labelled 1:M (one-to-many), with the 'one' end at INSTRUCTOR and the 'many' end at BOOKINGB1
  • (b) Answer: Diagram description (in place of a drawing): four entity boxes. STUDENT(StudentID underlined, StudentName, StudentPhone). INSTRUMENT(InstrumentID underlined, InstrumentName). INSTRUCTOR(InstructorID underlined, InstructorName). BOOKING(StudentID and InstrumentID underlined together as the composite key, InstructorID marked FK, plus Day, Time, Duration, Cost). Lines: STUDENT 1 --- M BOOKING; INSTRUMENT 1 --- M BOOKING; INSTRUCTOR 1 --- M BOOKING.
  • (c) the underlying relationship between STUDENT and INSTRUMENT is many-to-many: a student can book several instruments, and an instrument can be booked by several students (oe)B1
  • (c) a relational/ER model cannot represent a many-to-many relationship directly, so it must be decomposed into two one-to-many relationships via an intermediate link entity holding foreign keys to both (oe)B1
  • (c) Answer: STUDENT and INSTRUMENT have an underlying many-to-many relationship: a student can book several different instruments, and an instrument can be booked by several different students. A relational database cannot store a many-to-many relationship directly, so BOOKING is used as a link entity, holding a foreign key to each of STUDENT and INSTRUMENT, which splits the many-to-many relationship into two one-to-many relationships instead.

Question 12

  • (a) SELECT Instructor.InstructorName, COUNT(*) (or an equivalent alias, e.g. AS NumBookings)B1
  • (a) FROM BookingB1
  • (a) INNER JOIN Instructor ON Booking.InstructorID = Instructor.InstructorIDB1
  • (a) GROUP BY Instructor.InstructorName (or InstructorID)B1
  • (a) HAVING COUNT(*) > 1B1
  • (a) ORDER BY COUNT(*) DESC (or the chosen alias) DESC, and the whole query is syntactically correct overall caoB1
  • (a) Answer:
    SELECT Instructor.InstructorName, COUNT(*) AS NumBookings
    FROM Booking
    INNER JOIN Instructor ON Booking.InstructorID = Instructor.InstructorID
    GROUP BY Instructor.InstructorName
    HAVING COUNT(*) > 1
    ORDER BY NumBookings DESC;
  • (b) Fiona Clarke, 4B1
  • (b) Daniel Osei, 3 (in this order; Marcus Lindqvist correctly excluded) caoB1
  • (b) Answer:Fiona Clarke, 4
    Daniel Osei, 3

Mark your answers

Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.

Question 1

8 marks
Did your answer earn the marks?

Question 2

6 marks
Did your answer earn the marks?

Question 3

6 marks
Did your answer earn the marks?

Question 4

10 marks
Did your answer earn the marks?

Question 5

8 marks
Did your answer earn the marks?

Question 6

6 marks
Did your answer earn the marks?

Question 7

5 marks
Did your answer earn the marks?

Question 8

6 marks
Did your answer earn the marks?

Question 9

6 marks
Did your answer earn the marks?

Question 10

10 marks
Did your answer earn the marks?

Question 11

10 marks
Did your answer earn the marks?

Question 12

8 marks
Did your answer earn the marks?
Mark my answers

Worksheets are written for Revision Library and come with a full mark scheme; a few English packs quote an out-of-copyright text, and name it on the page. Exam board names and specification codes say which qualification a pack suits: we are not affiliated with, endorsed by or approved by any exam board, and no past-paper question is reproduced here. Rights queries: hello@revisionlibrary.co.uk