Detailed · Deep study
1Single-table databases
A single item of data about something, shown as a column in a table, e.g. Author.
All the data about one item, shown as a row in a table, e.g. all the details of one book.
To define a table from given data storage requirements, decide the table name, the fields needed, a suitable data type for each, the primary key, and any validation on the fields (e.g. a range check on Price, a presence check on Title, a format check on BookID).
See Topic 7 · Algorithm design and problem-solvingValidation checks are covered in 7.1.2Data types
| Data type | Stores | Example field |
|---|---|---|
| text/alphanumeric | letters, digits and symbols | Title, BookID, phone number |
| character | a single character | Grade (A–E) |
| Boolean | one of two values: TRUE/FALSE (Yes/No) | InStock |
| integer | a whole number | Pages |
| real | a number with a decimal part | Price |
| date/time | a date and/or time | DateBorrowed |
3Primary keys
A field that contains a unique value for every record, so that each record can be identified.
Author is not a suitable primary key, because two books can have the same author; BookID is suitable because each book is given a different ID.
4Structured query language (SQL)
| SQL | Meaning |
|---|---|
| SELECT | the fields to show (* for all fields) |
| FROM | the table to search |
| WHERE | the condition records must meet |
| ORDER BY … ASC / DESC | sort the results in ascending or descending order |
| SUM( ) | adds up the values of a numeric field |
| COUNT( ) | counts the number of records |
| AND / OR | combine conditions: both must be true / at least one must be true |
| BookID | Title | Author | Genre | Pages | Price | InStock |
|---|---|---|---|---|---|---|
| B001 | The Silent Sea | Ade Okafor | Thriller | 320 | 8.99 | TRUE |
| B002 | Stars Above | Mia Chen | Sci-fi | 410 | 12.50 | FALSE |
| B003 | Lost Valley | Ade Okafor | Adventure | 275 | 7.25 | TRUE |
| B004 | Code Breakers | Raj Patel | Thriller | 360 | 9.99 | TRUE |
| B005 | Red Planet | Mia Chen | Sci-fi | 290 | 6.50 | TRUE |
| B006 | River Song | Lena Ruiz | Adventure | 198 | 5.99 | FALSE |
SELECT Title, Author
FROM Books
WHERE Genre = 'Thriller';SELECT Title, Price
FROM Books
WHERE Price < 8.00
ORDER BY Price ASC;SELECT BookID, Title
FROM Books
WHERE Genre = 'Adventure' AND InStock = TRUE;SELECT Title
FROM Books
WHERE Genre = 'Sci-fi' OR Pages > 350
ORDER BY Title DESC;SELECT COUNT(*)
FROM Books
WHERE InStock = TRUE;SELECT SUM(Pages)
FROM Books
WHERE Author = 'Mia Chen';5Exam focus
Key words: database; table; field; record; data type; primary key; SQL; SELECT; FROM; WHERE; ORDER BY; ASC; DESC; SUM; COUNT; AND; OR.
Quick self-check
Answer in your head first, then reveal.
7 (1).
Titles may not be unique / two books could have the same title (1).
SELECT Title, Pages (1) FROM Books (1) WHERE Author = 'Ade Okafor' (1) ORDER BY Pages ASC (1).
2 (1).
Real (1).
