Databases and SQL queries
A relational database stores data in tables that can be linked. SQL is the language used to search and change that data, and exam questions ask you to read and write short queries.
Part 1 of 3: Learn it
In short
- A table holds records (rows); each record has fields (columns).
- A primary key uniquely identifies each record; a foreign key links to another table.
- SELECT fields FROM table WHERE condition ORDER BY field.
Where this is in your specification
Spec points: AQA 3.7.1 and 3.7.2, OCR 2.2.3, Edexcel 1.2.2 and 6.3.1
| Board | Topic: Databases and SQL |
|---|---|
| AQA 8525 | 3.7.1 and 3.7.2 |
| Edexcel 1CP2 | 1.2.2 and 6.3.1 (records), 6.4.2 (CSV text files) |
| OCR J277 | 2.2.3 (records, SQL, 2D arrays as tables, file handling) |
| Eduqas C500 | 5 Data organisation |
| Cambridge IGCSE 0478 | 9 Databases |
| National 5 C816 75 | Database design and development |
Tables, records and keys
A record is all the data about one item, such as one film. A field is one piece of data about it, such as its year. The primary key is a field whose value is different for every record, such as FilmID. A foreign key is a field in one table that is the primary key of another, which is how tables are linked without repeating data.
Reading data
- SELECT names the fields to show; * means every field.
- FROM names the table.
- WHERE filters records with a condition, using =, <, >, AND, OR. Text values go in quotes: Genre = 'Comedy'.
- ORDER BY sorts the results: ASC for ascending (the default), DESC for descending.
Changing data
- INSERT INTO Films (FilmID, Title, Year, Rating) VALUES (21, 'Tidewater', 2024, 4)
- UPDATE Films SET Rating = 5 WHERE FilmID = 21
- DELETE FROM Films WHERE Year < 1990
Without a WHERE clause, UPDATE and DELETE affect every record in the table.
What does * mean in SELECT *?
Show the answer
Show every field.
Part 2 of 3: See it worked
Worked examples
Example 1
A table Pupils has fields PupilID, Surname, Form and Age. Write a query to show the surnames of every pupil in form 10B, in alphabetical order.
- Field to show: Surname
- Condition: Form = '10B'
- Sort: ORDER BY Surname ASC
Answer: SELECT Surname FROM Pupils WHERE Form = '10B' ORDER BY Surname ASC
Example 2
Using the same table, write a query to show every field for pupils aged 15 or over.
- Every field: SELECT *
- Condition: Age >= 15
Answer: SELECT * FROM Pupils WHERE Age >= 15
Common mistakes
- Leaving out FROM, or putting WHERE before FROM.
- Forgetting quotes round text values in a condition.
- Using a field that might repeat, such as Surname, as a primary key.
- Running UPDATE or DELETE without a WHERE clause.
What is a primary key?
Show the answer
A field whose value is unique for every record, so it identifies each one.
Part 3 of 3: Test yourself
Check yourself
Answer each one in your head or on paper first, then open it to check.
What does * mean in SELECT *?
Show every field.
What is a primary key?
A field whose value is unique for every record, so it identifies each one.
Which keyword sorts results from highest to lowest?
DESC, after ORDER BY and the field name.
Jobs that use this
- Database administrator (s'ouvre dans un nouvel onglet)
- Data analyst-statistician (s'ouvre dans un nouvel onglet)
- Web developer (s'ouvre dans un nouvel onglet)
Each link opens the job profile on the National Careers Service (England). In the rest of the UK: My World of Work (Scotland), Careers Wales, nidirect careers (Northern Ireland).
Ces fiches sont en anglais car elles suivent les programmes d'examen britanniques.
Full lessons and marked practice for this course are coming soon to Brainlag Learn. See courses