IGCSE Computer Science 0478 — 9 Databases Hangman

The 32 key terms

1database table

A database table stores data in columns called fields and rows called records. A single-table database keeps all its data in this one table.

2field and record

A field is a column and a record is one data row. The heading row is not a record. A table with 4 fields and 6 records has 4 columns and 7 rows including the heading.

3string

Text made of a sequence of characters (single letters, digits or symbols), written in quotes, like 'Sam' or '0412345678'. Phone numbers are strings.

4character field

A character field holds exactly one character, like a grade A-E or an M/F code. A text field holds a string of any length, like a name or a postcode.

5integer

A whole number with no decimal part, positive or negative, used for counts such as the number of students in a class (e.g. 25, 0 or -3).

6float

A floating point number is a number with a decimal part, such as 1.75 or 19.95. It is used for measurements like height and for money, unlike an integer, which has no decimals.

7Boolean

A data type with only two possible values: true or false (yes or no). Used for yes/no fields, TRUE and FALSE in a spreadsheet, and the result of a comparison such as 5>3.

8Date/Time

A data type that stores a calendar date and/or time of day, such as 25/03/2025 or 14:30, so values can be sorted in order and used to calculate the duration between two dates.

9data type

Pick the data type by what you will do with the value: calculate (number), sort by date (date/time), test true or false (Boolean). Postcodes are text, not numbers, as you never do maths on them.

11table design

Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

12validation

An automatic check that data is reasonable and in the right form when entered. It blocks bad data early, but it cannot prove the data is true (a valid age of 15 could still be wrong).

13validation check

A test that an entry is sensible. Types include presence, data type, range, length, format and list. Each one tests a different property of the entry.

14validation rule

A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.

16PRIMARY KEY

A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

18table structure

The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.

19query

A saved question asked of a table. It shows only the fields and records that answer it, as a new view, and leaves the original table unchanged.

20SELECT name, age FROM students;

SELECT lists the fields to show and FROM names the table. This returns the name and age columns for every record in students. SELECT * shows all columns. End with a semicolon.

21SELECT lastName, firstName FROM students;

The output has only the fields named, as columns in the order written after SELECT, with a heading row of field names and one row per record kept (all records if there is no WHERE).

22WHERE

An SQL clause that keeps only the rows meeting a condition, such as WHERE age >= 15 or WHERE town = 'Perth'. Text goes in quotes, and AND or OR can combine conditions.

23WHERE Score > 80

A criterion that limits a query to records where the condition is true, so only Score values above 80 are shown. Compare with =, <>, <, >, <=, >=. Text goes in quotes.

24WHERE Town = 'Leeds'

The value in a WHERE condition must match the field's data type: text and characters go in quotes, numbers do not, and Boolean fields use TRUE or FALSE, e.g. Member = TRUE.

25AND / OR

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

26WHERE (A OR B) AND C

Brackets make SQL work out the OR first, then the AND. A record must meet C and also A or B. Moving the brackets, as in A OR (B AND C), can return different records.

27ORDER BY

Sorts the rows a SELECT query returns by a chosen field. It sorts A to Z or lowest to highest by default (ASC), or the reverse if you add DESC after the field name.

28SELECT FROM WHERE ORDER BY

The order of clauses in a full SQL query: SELECT, FROM, WHERE, then ORDER BY. WHERE filters the rows first, then ORDER BY sorts the rows that are left.

29SUM(field)

Adds up all the values in a numeric field and returns one total, e.g. SELECT SUM(Score) FROM Results; gives one row. It only works on numbers.

30COUNT(*)

SQL function that counts records. SELECT COUNT(*) FROM Students; outputs one row with the number of records in the table. It counts rows, whereas SUM adds up values.

31SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

WHERE filters the records first, then COUNT or SUM works only on the matching ones. This counts members from Leeds. If no record matches, COUNT gives 0.

32WHERE, ORDER BY, SELECT

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

33SELECT field FROM table WHERE condition;

Completing an SQL script: read the task, then fill the gap with the right field, WHERE condition, ASC or DESC, AND or OR, or SUM or COUNT so the query returns exactly what is asked.

34SELECT fields FROM table WHERE condition ORDER BY field DESC;

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

35SQL error

A mistake in an SQL script, such as text without quotes, > instead of >=, AND instead of OR, clauses in the wrong order, a missing table after FROM or a missing semicolon.