Two students called Amy Khan: how does a database tell them apart?

Tables, records and fields

• A database is an organised, persistent store of data.
• A table holds data about one kind of thing, such as students.
• A record is one row: all the data about one student.
• A field is one column: one item of data stored for every record, such as Surname.
• Your databases have a single table: one table holds one set of data.

The primary key

• A primary key is a field that holds a different value for every record, so it uniquely identifies each record.
• Names, forms and even birthdays can repeat, so they cannot be keys.
• Schools, shops and banks add an ID field, such as StudentID, to act as the key.
• The database refuses a new record whose key value is already used.

Choosing data types

• Each field has a data type that fits the data it stores.
• Integer for whole numbers, real for decimals, Boolean for TRUE/FALSE, character for one character, text/alphanumeric for text, date/time for dates and times.
• Phone numbers and IDs are stored as text/alphanumeric: they are not used in calculations, can start with 0 and can contain letters or spaces.
• When you define a table, add validation to fields, e.g. a range check on a Mark field or a presence check on Surname.

How can one line of SQL find every overdue book in a library?

SELECT and FROM

• SQL (Structured Query Language) is used to search for data in a database.
• SELECT lists the fields you want to see, separated by commas.
• FROM names the table to search.
• This query shows the Title and DaysLate of every record in Loan:

SELECT Title, DaysLate
FROM Loan

WHERE picks the records

• WHERE gives a condition. Only records that meet it are returned.
• Compare with = < > <= >= and <> (not equal).
• Put text values in quotes: Genre = 'Fantasy'. Numbers need no quotes.
• Join conditions with AND (both must be true) or OR (either can be true): WHERE Genre = 'Fantasy' AND DaysLate > 0 returns Skellig and Matilda.

SELECT Title, DaysLate
FROM Loan
WHERE Genre = 'Fantasy'

Result: Coraline 0, Skellig 7, Matilda 5.

Writing a query

• Read the question for the fields to show (SELECT), the table (FROM) and the rule (WHERE).
• Fields in WHERE do not have to appear in SELECT.
• ORDER BY sorts the results by a field: ASC (ascending, A to Z, smallest first) or DESC (descending, largest first).
• SUM(field) adds up a numeric field; COUNT(field) counts the records returned.

SELECT Title, DaysLate
FROM Loan
WHERE Genre = 'Fantasy'
ORDER BY DaysLate DESC

SELECT SUM(DaysLate)
FROM Loan
WHERE Genre = 'Fantasy'

The first returns Skellig 7, Matilda 5, Coraline 0. The second returns 12.

Big ideas

• A table stores records (rows); each record has the same fields (columns), each with a suitable data type.
• A primary key uniquely identifies each record.
• SELECT fields FROM table WHERE condition: the fields to show, the table to search and the records to keep.
• ORDER BY … ASC or DESC sorts the results.
• SUM adds up and COUNT counts; AND and OR combine conditions.