Stretch tasks Relational Databases and SQL

Round 1: Quiz Blitz

1. One row, describing one instance A record is a row. A field is a column.
2. A field whose value is unique for every record It identifies each record. It need not be the first field.
3. Holds another table's primary key, linking the tables The foreign key is how a loan points back to exactly one borrower.
4. Redundancy: the same data stored many times; Update anomalies: copies that disagree Splitting the data into linked tables stores each fact once.
5. Year 11 students' first names and surnames, A to Z by surname SELECT the fields, FROM the table, WHERE the records match, ORDER BY to sort.
6. desc ASC is ascending, DESC is descending.
7. False It is the field whose value is unique for every record, wherever it sits.
8. WHERE surname = 'Khan' Text values need quotation marks. Numbers do not.
9. Any sequence of characters The wildcard lets LIKE match part of a value.
10. UPDATE UPDATE ... SET ... WHERE changes existing records.
11. Without it, every borrower is paired with every loan Matching the primary key to the foreign key performs the join.
12. field Fields are columns. Records are rows.

Round 2: Quick Fire

1. • SELECT title, price
   • FROM Books WHERE price < 10
   • ORDER BY price ASC
   Model answer

   SQLCopy

   SELECT title, price
   FROM Books
   WHERE price < 10
   ORDER BY price ASC

2. • Each address is stored only once, removing redundancy
   • Changing an address is one edit, so copies cannot disagree
   Model answer

   Each borrower's address is then stored only once, which removes redundancy and saves space. When a borrower moves, it is one edit, so the database can never hold two contradictory addresses.

3. • A primary key uniquely identifies each record in its table
   • A foreign key holds the primary key of another table, linking them
   Model answer

   A primary key is a field whose value is unique for every record, so it identifies each record in its table. A foreign key is a field holding the primary key of a record in another table, which links the two tables.

4. • It changes every student, because there is no WHERE clause
   • Add WHERE yearGroup = 10
   Model answer

   It sets every student in the table to Year 11, because there is no WHERE clause. Adding WHERE yearGroup = 10 limits the change to the Year 10 students.

Round 3: Big Think

1. • Redundancy: each film's details are stored many times, wasting space
   • Update anomalies: a change means editing each copy
   • Copies can disagree, leaving inconsistent data
   • A film with no screenings cannot be recorded
   • Split the data into separate tables
   • The films table has a primary key such as filmID
   • The screenings table holds filmID as a foreign key
   • Each film's details are then stored once, so a change is one edit
   Model answer

   Storing it all in one table causes redundancy: each film's details are stored many times, wasting space. It also causes update anomalies: if a running time changes, each copy must be edited, and if one is missed the data becomes inconsistent, with two contradictory values. A film with no screenings yet cannot be recorded at all, which is an insertion problem. Instead, the data should be split into two tables. A Films table holds each film's details once, with filmID as its primary key, and a Screenings table holds filmID as a foreign key, linking each screening to exactly one film. A change is then one edit in one place.

2. • SELECT surname
   • FROM Students, Clubs
   • WHERE Students.studentID = Clubs.studentID
   • AND clubName = 'Chess'
   • ORDER BY surname
   • INSERT INTO Students
   • VALUES (5120, 'Ola', 'Bello', 9)
   • DELETE FROM Clubs WHERE clubID = 77
   Model answer

   SQLCopy

   SELECT Students.surname
   FROM Students, Clubs
   WHERE Students.studentID = Clubs.studentID
   AND Clubs.clubName = 'Chess'
   ORDER BY Students.surname ASC
   
   INSERT INTO Students(studentID, firstName, surname, yearGroup)
   VALUES (5120, 'Ola', 'Bello', 9)
   
   DELETE FROM Clubs
   WHERE clubID = 77

This or That

1. A row A record is one row. A field is one column.
2. No: each value is unique A primary key identifies one record, so no two records may share a value.
3. Quotation marks: 'Khan' Text values need quotation marks. Numbers, such as 11, do not.
4. WHERE SELECT picks the fields. WHERE picks the records.
5. A foreign key Each loan holds the borrower's ID, which is the primary key of the Borrowers table.
6. INSERT INTO UPDATE changes values already stored. INSERT INTO adds a new record.
7. Deletes every record Without WHERE, the statement applies to every record in the table.
8. title LIKE '%cat%' = needs an exact match. LIKE with the % wildcard matches part of a value.

Memory Match

• A 4 Record: One row of a table
• B 1 Field: One column, holding one attribute
• C 5 Primary key: Uniquely identifies each record
• D 6 Foreign key: Holds another table's primary key
• E 2 Redundancy: The same data stored more than once
• F 3 Wildcard: % in LIKE: any characters

Sort It

1. ORDER BY Sorts the results ORDER BY sorts.
2. ORDER BY surname DESC DESC sorts Z to A, after ORDER BY.
3. WHERE Borrowers.borrowerID = Loans.borrowerID Matching the keys is a condition in WHERE, and it performs the join.
4. ORDER BY ASC for A to Z ASC means ascending order.
5. SELECT * for every field SELECT * returns every field.
6. FROM Borrowers, Loans when joining two tables Both tables are named after FROM.
7. WHERE Filters which records are returned WHERE picks the records.
8. FROM Names the table or tables FROM says where the data is.
9. SELECT Lists the fields to show SELECT names the fields you want.
10. WHERE mark >= 70 AND yearGroup = 11 Conditions joined with AND belong in WHERE.
11. SELECT firstName, surname A list of fields follows SELECT.
12. WHERE title LIKE '%computing%' A LIKE condition filters records, so it is WHERE.