Skip to content
MathsGenie logo
Quick links
Open app

Course home

  1. A Level
  3. Computer Science OCR
  5. Revision guides

Structured Query Language (SQL)

What you'll learn

  • How SQL queries use SELECT, FROM, WHERE, LIKE, AND and OR.
  • How wildcards * and % help you select or match data.
  • How to use JOIN and nested SELECT queries.
  • What INSERT, DELETE and DROP do to a database.

The database ideas SQL depends on

SQL is used with a relational database, where data is stored in tables.

Definition

Table, record and field

A table stores data about one type of thing, such as students or books. A record is one row in a table. A field is one column in a table, such as StudentID, Name or DateOfBirth.

For example:

StudentIDNameYearGroupTutorClassID
1Asha12Khan10
2Ben12Patel20
3Cara13Khan30

A primary key is a field that uniquely identifies a record, such as StudentID. A foreign key is a field that links to a primary key in another table, such as ClassID.

What SQL is

Definition

Structured Query Language

Structured Query Language (SQL) is a language used to create, search, insert and remove data in a database. OCR expects you to recognise and use only a small assessed subset of SQL unless the question explains anything extra.

SQL keywords are often written in capitals, for example SELECT, but database systems are usually not case-sensitive for the keywords. Field names, table names and string data may be treated more strictly depending on the system, so copy spellings from the question.

Key Idea

The basic query shape

Most retrieval queries follow this pattern: SELECT the fields you want, FROM the table where the data is stored, then optionally use WHERE to filter the records.

SELECT and FROM

SELECT chooses which fields appear in the result. FROM names the table being queried.

  • SELECT Name FROM Student returns only the Name field from every record in Student.
  • SELECT * FROM Student returns every field from every record in Student.

Here, * is a wildcard meaning “all fields”.

Example

Choosing fields from a table

Using the Student table above, write a query to show only each student’s name and tutor.

  1. Decide which fields should appear in the output: Name and Tutor.
  2. Decide which table contains those fields: Student.
  3. Combine the clauses: SELECT Name, Tutor FROM Student.
Common Mistake

Selecting too much data

Do not use SELECT * if the question asks for specific fields. SELECT * is useful, but it returns all fields, which may not match the required output.

WHERE: filtering records

WHERE adds a condition that a record must satisfy to be included.

For example, SELECT Name FROM Student WHERE YearGroup = 12 returns only students in year 12.

Definition

Condition

A condition is a test that is either true or false for each record, such as YearGroup = 12 or Tutor = 'Khan'.

SQL uses = for equality in a WHERE condition. This is different from OCR exam pseudocode, where comparison is written as ==.

Example

Filtering records with WHERE

Write a query to show the names of students whose tutor is Khan.

  1. Choose the output field: only Name is needed.
  2. Choose the table: the data is in Student.
  3. Add the filter condition: Tutor = 'Khan'.
  4. Write the query: SELECT Name FROM Student WHERE Tutor = 'Khan'.

LIKE and the % wildcard

LIKE is used in a WHERE clause to match a pattern rather than an exact value.

The % wildcard means “any sequence of characters”, including no characters.

PatternMeaning
'A%'starts with A
'%son'ends with son
'%ann%'contains ann somewhere

For example, SELECT Name FROM Student WHERE Name LIKE 'A%' returns names starting with A.

Example

Matching part of a string

Write a query to find students whose names contain the letters ar.

  1. The field being searched is Name.
  2. Because ar can appear anywhere in the name, put % before and after it: '%ar%'.
  3. Use LIKE because this is pattern matching, not exact equality.
  4. Write the query: SELECT Name FROM Student WHERE Name LIKE '%ar%'.
Tip

Wildcard sanity check

Read % as “anything”. So '%ar%' reads as “anything, then ar, then anything”.

AND and OR

AND combines conditions where both must be true.

Example: SELECT Name FROM Student WHERE YearGroup = 12 AND Tutor = 'Khan'

This returns students who are in year 12 and have Khan as tutor.

OR combines conditions where at least one must be true.

Example: SELECT Name FROM Student WHERE Tutor = 'Khan' OR Tutor = 'Patel'

This returns students whose tutor is Khan or Patel.

Example

Combining conditions

Write a query to show names of year 12 students whose tutor is Patel.

  1. The output field is Name.
  2. The table is Student.
  3. Two conditions must both be true: YearGroup = 12 and Tutor = 'Patel'.
  4. Because both conditions are required, join them with AND: SELECT Name FROM Student WHERE YearGroup = 12 AND Tutor = 'Patel'.
Common Mistake

Using OR when both conditions are needed

OR usually makes the result larger because a record only needs to pass one condition. If the question says “students in year 12 who have Patel as tutor”, use AND, not OR.

JOIN: combining related tables

A JOIN combines records from two related tables. In OCR SQL, JOIN means INNER JOIN: only records with matching values in both tables are included.

The diagram below shows how an inner join keeps only matching ClassID values from both tables.

Diagram showing Student and Class tables joined using an INNER JOIN on ClassID, producing only matching rows

A typical join query is:

SELECT Student.Name, Class.ClassName FROM Student JOIN Class ON Student.ClassID = Class.ClassID

The ON part gives the matching rule for the join.

Example

Joining two tables

Write a query to show each student’s name with their class name.

  1. Identify the fields required in the output: Student.Name and Class.ClassName.
  2. Identify the relationship: Student.ClassID links to Class.ClassID.
  3. Use JOIN to combine matching records from the two tables.
  4. Write the query: SELECT Student.Name, Class.ClassName FROM Student JOIN Class ON Student.ClassID = Class.ClassID.
Common Mistake

JOIN is not every possible combination

An inner join does not pair every student with every class. It only keeps rows where the join condition matches.

Nested SELECTs

A nested SELECT is a SELECT query placed inside another query. The inner query runs first, and its result is used by the outer query.

Example:

SELECT Name FROM Student WHERE ClassID = (SELECT ClassID FROM Class WHERE ClassName = 'Computing')

This means:

  1. Find the ClassID for the class called Computing.
  2. Use that ClassID to find matching students.
  3. Return their names.
Example

Using an inner query first

Suppose the Class table says Computing has ClassID 10. Trace the nested query SELECT Name FROM Student WHERE ClassID = (SELECT ClassID FROM Class WHERE ClassName = 'Computing').

  1. Evaluate the inner query: SELECT ClassID FROM Class WHERE ClassName = 'Computing' gives 10.
  2. Substitute that result into the outer condition: ClassID = 10.
  3. Apply the outer query to Student, returning names of students whose ClassID is 10.

INSERT: adding records

INSERT adds a new record to a table.

A common form is:

INSERT INTO Student (StudentID, Name, YearGroup, Tutor, ClassID) VALUES (4, 'Dina', 12, 'Khan', 10)

You should provide values in the same order as the field list.

Common Mistake

Mismatched INSERT values

If you list five fields, you need five values, and they must match the correct fields. For example, putting 'Dina' where StudentID belongs would be wrong.

DELETE: removing records

DELETE removes records from a table.

Example: DELETE FROM Student WHERE StudentID = 2

This removes the record for the student with StudentID 2.

Common Mistake

DELETE without WHERE

DELETE FROM Student would remove every record from the Student table. The table structure would still exist, but the data would be gone.

DROP: removing a table

DROP removes a whole database object, such as a table.

Example: DROP TABLE Student

This removes the Student table itself, including its structure and its data.

Key Idea

DELETE vs DROP

DELETE removes records from inside a table. DROP removes the table itself.

Putting it together

When you read or write SQL, build the query in a logical order:

  1. What data should appear? Use SELECT.
  2. Which table or tables contain it? Use FROM and possibly JOIN.
  3. Which records should be included? Use WHERE, LIKE, AND and OR.
  4. Is the command changing the database? Use INSERT, DELETE or DROP carefully.
Example

Writing a query from a requirement

Requirement: show the names of year 12 students whose names start with A.

  1. The output needed is only the student name, so use SELECT Name.
  2. The data comes from the Student table, so add FROM Student.
  3. The year condition is YearGroup = 12.
  4. The name pattern is “starts with A”, so use Name LIKE 'A%'.
  5. Both conditions must be true, so join them with AND: SELECT Name FROM Student WHERE YearGroup = 12 AND Name LIKE 'A%'.
Exam technique

In the exam

  1. Build retrieval queries in the order SELECT, FROM, then WHERE, even though the database logically filters records before displaying fields.
  2. Use LIKE with % for partial text matching, and use = for exact matches.
  3. For JOIN, look for the matching key fields, often a primary key and a foreign key with the same name.
  4. Treat DELETE and DROP as destructive: say exactly whether records or the whole table are removed.
Self review

Check yourself

  • What is the difference between SELECT * FROM Student and SELECT Name FROM Student?
  • When should you use LIKE '%ing%' rather than = 'ing'?
  • Why does an OCR JOIN exclude records that do not have a matching value in both tables?
PreviousNext

How was this guide?

Teach Genie

Review Structured Query Language (SQL) by teaching Genie

Teach it back in your own words, spot gaps, and remember it better.

Start teaching
Genie and Baby Genie

Lesson

Recap your knowledge with an interactive lesson

8 minute activity

Start lesson

SQL is used with relational databases, where data is organized into tables. In this structure, a row represents a single record, while a column represents a specific field of information.

A primary key uniquely identifies a record, such as a StudentID. Conversely, a foreign key stores a matching key from another table, such as ClassID, so that related tables can be linked together.

SQL can retrieve data with queries such as SELECT and change data using commands like INSERT, DELETE, and DROP. In exam questions, it is crucial to copy field names and table names exactly as they are given.

Flashcards

Remember key concepts with flashcards

20 flashcards

Practice flashcards

In a database table, one row is a [     ] and one column is a [     ].

Structured Query Language (SQL) Revision Guide

  1. A Level
  2. /Computer Science
  3. /Structured Query Language (SQL)

Revision guides