Fields, records and a primary key
A field is a single item of data — one column (e.g. Title). A record is all the data about one entity — one row, made of several fields. A table holds the records. A primary key is a field whose value is unique for every record, used to uniquely identify it — no two records share it. A single-table (flat-file) database suits data about one type of entity only (e.g. a song list).
The six field data types
0478 limits field data types to six: text/alphanumeric (words or a letters-and-digits code, e.g. SongNumber), character (a single character), Boolean (TRUE/FALSE), integer (a whole number/count/year), real (a value with a decimal part, e.g. a price or Minutes), and date/time. Choose the type from the kind of value stored — never default everything to text or reuse one type where a different one fits.
SQL clauses and their fixed order
SQL queries one table in a fixed clause order: SELECT ... FROM ... WHERE ... ORDER BY .... SELECT picks the columns (* = all); FROM names the table; WHERE filters by a condition with = > < >= <= <> and AND/OR; ORDER BY sorts the output, ASC (default) or DESC. SUM(field) totals a numeric field; COUNT(*) counts matching records. Text values go in single quotes (WHERE Genre = 'Rock'); numbers do not (WHERE Minutes > 3).
Drawn from real examiner reports.
Field vs record vs table
field, record and table are frequently swapped. Wrong: "a record is one piece of data" or "a field is a row." Right: a field is one column (e.g. Title); a record is one row (all the data about one entity); a table is the whole collection of records. Keep field = column, record = row fixed; order: field, record, table.
Primary key = "first/important field"
A primary key is not "the first field" or "an important field" — it is the field whose value is unique for every record, used to identify each record. When asked to choose and justify one, the reason must be uniqueness. A field like Title or Genre is a poor key because two records could share the value. The mark is in the word unique.
June 2023 Paper 2 examiner report (0478_s23_er): Q10(a) "Most candidates correctly identified SongNumber as the most appropriate primary key field" and "gave a valid reason" — the valid reason is uniqueness.
WHERE filters; ORDER BY sorts
WHERE decides which records appear — it filters by a condition (e.g. WHERE Minutes > 3). ORDER BY decides the order the chosen records display in — it sorts (e.g. ORDER BY Title ASC), not which appear. Putting the sort field after WHERE, or the condition after ORDER BY, breaks the query. WHERE = which; ORDER BY = order.
June 2023 Paper 2 examiner report (0478_s23_er): Q10(c) a few candidates "did not refer back to the previous question when selecting the response to the WHERE part" — the WHERE condition must match the earlier part.
Repeating or defaulting data types
Examiners flag candidates who do not use the data types provided and repeat one type for several fields. Match each field to its value: a decimal (price, minutes) is real; a whole count/year is integer; a letters-and-digits code is text/alphanumeric; a yes/no flag is Boolean; a date is date/time. Never default everything to text.
June 2023 Paper 2 examiner report (0478_s23_er): Q10(b) "A few candidates did not use the data types provided and a few repeated the data types for the fields."
SUM adds values; COUNT counts rows
SUM and COUNT are different aggregate functions. SUM(field) adds up the values in a numeric field and returns a total (e.g. total minutes). COUNT(*) (or COUNT(field)) returns the number of records/rows that match. Using COUNT to add up values, or SUM to count rows, is a common slip: SUM adds values; COUNT counts rows.
Misspelt field names / wrong quoting
Two accuracy slips fail an otherwise-correct query. (1) Copy field names letter-for-letter — a misspelled field name (e.g. Titel for Title) no longer matches. (2) Quote text, not numbers: text goes in single quotes (WHERE Genre = 'Rock'); numbers do not (WHERE Minutes > 3). A wrong quote or field name costs the mark.
June 2023 Paper 2 examiner report (0478_s23_er): Q10(c) "A few candidates did not copy the field names correctly" — an SQL query fails if a field name is misspelled.
Fill the SELECT-FROM-WHERE-ORDER BY scaffold
For "write the SQL", fill the fixed scaffold — one mark per clause: SELECT (which fields, or *) FROM (table) WHERE (condition) ORDER BY (field, ASC/DESC). Copy field names exactly; quote text, not numbers. Omit WHERE/ORDER BY if not needed.
Read a query clause by clause
For the output of a given statement, work clause by clause: apply WHERE first to find matching records, then take the SELECTed fields, then ORDER BY to sort. Check whether SUM/COUNT wants a single total/count, not a list of rows.
Answer the command word in context
Match the command word: "state/identify" wants a short answer (e.g. a field name or data type); "explain/justify" needs a reason — for a primary key, that reason is uniqueness. Answer in the question's context and field names, not with generic responses.
Database — an organised, structured collection of data that can be searched, sorted and queried.
Single-table (flat-file) database — a database that stores all its data in one table. It is used when the data is about one type of entity only, so the data fits sensibly in a single table without repeating groups of data (e.g. a list of songs, a stock list of books).
Table — a structure that stores data about one type of thing as a set of records and fields.
Full notes, flashcards, Q&A and the topic quiz for every premium subject.
Premium plans are US$8.99/month or US$49.99/year — first month free.
Studying with a parent's blessing? Show them this.
