8. Databases

Browse all infographics →

Dotted terms are defined in the glossary.

• SELECT * — every column of a table

  The simplest query. Look at the STUDENT table first, then pick columns.

  O LevelASSELECT & FROM 2210 §9.2 / 9618 §8.3
  Run

• Choose the columns you want

  Name the fields, separated by commas — the result has only those columns, in that order.

  O LevelASSELECT & FROM 2210 §9.2
  Run

• DISTINCT — each value once

  Which towns do students come from? Without DISTINCT you get one row per student.

  ASSELECT & FROM 9618 §8.3
  Run

• Calculated column with an alias

  Arithmetic in the SELECT list and AS to name the new column — the annual salary from a monthly one.

  ASSELECT & FROM 9618 §8.3
  Run

• WHERE with a text value

  Text is written in single quotes. Only rows where the condition is TRUE are returned.

  O LevelASWHERE 2210 §9.2
  Run

• WHERE with a number and a comparison

  Numbers have no quotes. > >= < <= = <> all work.

  O LevelASWHERE 2210 §9.2
  Run

• AND, OR and brackets

  AND binds tighter than OR — use brackets to say what you mean, exactly as in Boolean logic.

  O LevelASWHERE 2210 §9.2
  Run

• LIKE with wildcards

  % matches any characters, _ matches exactly one. Names beginning with A; emails ending in a domain.

  ASWHERE 9618 §8.3
  Run

• IN and BETWEEN

  IN tests a list; BETWEEN is inclusive at both ends. Dates are compared as 'YYYY-MM-DD'.

  ASWHERE 9618 §8.3
  Run

• IS NULL — missing values

  Enrolments without a mark yet. NULL is not 0 and not '' — = NULL never matches; use IS NULL.

  ASWHERE 9618 §8.3
  Run

• ORDER BY ascending

  Alphabetical by last name — ASC is the default.

  O LevelASORDER BY 2210 §9.2
  Run

• ORDER BY descending, two keys

  Highest salary first; ties broken by last name. LIMIT keeps the top few.

  O LevelASORDER BY 2210 §9.2
  Run

• WHERE then ORDER BY

  Filter first, then sort — the clause order is fixed: SELECT … FROM … WHERE … ORDER BY.

  O LevelASORDER BY 2210 §9.2
  Run

• COUNT(*) — how many rows

  How many students are in form 10A? COUNT(*) counts matching rows.

  O LevelASCOUNT, SUM, AVG, MIN, MAX 2210 §9.2
  Run

• SUM — total of a column

  Total monthly salary bill for the Computer Science department.

  O LevelASCOUNT, SUM, AVG, MIN, MAX 2210 §9.2
  Run

• AVG, MIN and MAX together

  Statistics for one class. Aliases make the headings readable; NULL marks are ignored.

  ASCOUNT, SUM, AVG, MIN, MAX 9618 §8.3
  Run

• COUNT(DISTINCT …)

  How many different towns — COUNT(Town) would count every student.

  ASCOUNT, SUM, AVG, MIN, MAX 9618 §8.3
  Run

• INNER JOIN two tables

  Which teacher takes each class? Join CLASS to TEACHER on the foreign key.

  ASINNER JOIN 9618 §8.3
  Run

• Three tables through a link table

  Student → ENROLMENT → CLASS: the many-to-many resolved. Aliases (S, E, C) keep it short.

  ASINNER JOIN 9618 §8.3
  Run

• Four tables — who studies what with whom

  STUDENT, ENROLMENT, CLASS, SUBJECT and TEACHER in one query. Notice every ON pairs a foreign key with a primary key.

  ASA2INNER JOIN 9618 §8.3
  Run

• Join using WHERE (the older style)

  Older mark schemes accept the comma join with the condition in WHERE. Same result as INNER JOIN … ON.

  ASINNER JOIN 9618 §8.3
  Run

• LEFT JOIN — keep rows with no match

  Every teacher, even those with no class (their ClassID shows NULL). INNER JOIN would drop them.

  A2INNER JOIN 9618 §8.3 (extension)
  Run

• GROUP BY with COUNT

  How many students in each form group — one row per group.

  ASGROUP BY & HAVING 9618 §8.3
  Run

• GROUP BY across a join

  Average mark per class, with the subject title. Everything not inside AVG must be in GROUP BY.

  ASGROUP BY & HAVING 9618 §8.3
  Run

• HAVING — filter the groups

  Departments whose total salary bill is over 200 000. WHERE filters rows before grouping; HAVING filters groups after.

  ASGROUP BY & HAVING 9618 §8.3
  Run

• Most borrowed books

  Join LOAN to BOOK, count loans per title, sort by the count.

  ASGROUP BY & HAVING 9618 §8.3
  Run

• INSERT INTO — add a row

  Values in the same order as the columns you list. Then SELECT to prove it is there.

  ASINSERT, UPDATE, DELETE 9618 §8.3
  Run

• UPDATE — change values

  Set a mark and grade for one enrolment. Without WHERE every row would change!

  ASINSERT, UPDATE, DELETE 9618 §8.3
  Run

• DELETE FROM — remove rows

  Delete a returned loan. Try deleting a STUDENT who has enrolments and see referential integrity refuse.

  ASINSERT, UPDATE, DELETE 9618 §8.3
  Run

• Referential integrity in action

  A foreign key must point at an existing primary key. This INSERT is refused — read the error, then fix the StudentID.

  ASINSERT, UPDATE, DELETE 9618 §8.1, §8.3
  Run

• UPDATE with a calculation

  Give every Mathematics teacher a 10% rise — an expression on the right of SET.

  ASINSERT, UPDATE, DELETE 9618 §8.3
  Run

• CREATE TABLE with a primary key

  DDL: each column has a data type; PRIMARY KEY makes the identifier unique and NOT NULL.

  ASCREATE & ALTER TABLE 9618 §8.3
  Run

• CREATE TABLE with a foreign key

  A child table referencing a parent — the FOREIGN KEY line is what the mark scheme looks for.

  ASCREATE & ALTER TABLE 9618 §8.3
  Run

• Composite primary key (link table)

  Two columns together make the key — the standard way to resolve a many-to-many relationship.

  ASCREATE & ALTER TABLE 9618 §8.1, §8.3
  Run

• ALTER TABLE — add and drop a column

  Add a phone number to TEACHER, then remove it again.

  ASCREATE & ALTER TABLE 9618 §8.3
  Run

• CREATE DATABASE and the full DDL

  The whole thing from the top: a database, a parent table, then a child table with keys.

  ASCREATE & ALTER TABLE 9618 §8.3
  Run

• 9618: students taught by ZAK, alphabetically

  Paper 1 §8 style: a query spanning four tables with a WHERE on the teacher's name.

  ASExam-style questions 9618 §8.3
  Run

• 9618: overdue loans with the borrower's email

  Loans not returned and due before today — join and a date comparison.

  ASExam-style questions 9618 §8.3
  Run

• 9618: DDL for a new table with keys

  Write the CREATE TABLE for an ATTENDANCE table linked to STUDENT and CLASS — composite key and two foreign keys.

  ASExam-style questions 9618 §8.3
  Run

• 9618: average mark per subject, best first, only subjects with 5+ marks

  GROUP BY across a join with HAVING and ORDER BY on an aggregate.

  ASExam-style questions 9618 §8.3
  Run

• Customer orders

  The classic: an order form with a repeating group of order lines becomes Customer, Order, OrderLine and Product.

  ASNormalisation to 3NF 9618 §8.1
  Open

• Students and classes

  A timetable table with a transitive dependency (teacher details depend on the teacher, not the class).

  ASNormalisation to 3NF 9618 §8.1
  Open

• Library loans

  Loans with a composite key: the partial dependencies on BookID and MemberID must move out (2NF), then the author details (3NF).

  ASNormalisation to 3NF 9618 §8.1
  Open

• Hotel bookings

  Guests, rooms and bookings — room type details depend on the room type code.

  ASNormalisation to 3NF 9618 §8.1
  Open

• Airline tickets

  A ticket carries passenger, flight and aircraft details — three levels of dependency.

  ASNormalisation to 3NF 9618 §8.1
  Open

• Invoice — stop at 2NF and see why it fails 3NF

  A worked example whose model answer is only in 2NF; the checker names the transitive dependency.

  ASNormalisation to 3NF 9618 §8.1
  Open

• School: students, classes, teachers

  A student is in one class; a class has one teacher; a teacher can teach several classes.

  ASEntity–relationship diagrams 9618 §8.2
  Open

• Students and courses (many-to-many)

  A student takes many courses and a course has many students — the M:M must be resolved with a link entity.

  ASEntity–relationship diagrams 9618 §8.2
  Open

• Library: books, members, loans

  Three entities and two relationships, one of them with attributes of its own (the loan).

  ASEntity–relationship diagrams 9618 §8.2
  Open

• Hospital: doctors, patients, wards

  A ward holds many patients; a patient has one consultant doctor; doctors and patients also meet in appointments (M:M).

  ASEntity–relationship diagrams 9618 §8.2
  Open

• One-to-one: employee and company car

  Each employee has at most one car and each car one driver — and why 1:1 is rare.

  ASEntity–relationship diagrams 9618 §8.2
  Open