OCR GCSE SQL (J277)

An interactive lesson and quiz on Records, Tables, and SQL.

Optional: 5-Minute Crash Course

Short on time? Watch this video first to recap SELECT, FROM, and WHERE.

Part 1: What is a Record?

Let's look at how data is structured in a database table.

Think of a database table like a spreadsheet. It has columns (called Fields) and rows (called Records).

  • A Field defines what kind of data is stored (e.g., `FirstName`).
  • A Record is a single, complete set of information about one thing (e.g., one person).

Example Table: `Students`

StudentID (Field) FirstName (Field) FormTutor (Field)
1001 Jane Mr. Jones
1002 David Ms. Patel
1003 Amir Mr. Jones

This table has 3 Fields and 3 Records. The highlighted row is a single record for the student 'David'.

Part 2: What is SQL? (SELECT, FROM, WHERE)

SQL (Structured Query Language) is the code we use to "ask questions" (or queries) to a database.

For your GCSE, you must know these three commands, which form a sentence:

SELECT FirstName, LastName

...WHAT you want to see (which columns/fields).

FROM Students

...WHERE to look for it (which table).

WHERE FormTutor = 'Mr. Jones'

...HOW to filter it (which rows/records).

Putting It All Together:

"Get the first name and last name of all students in Mr. Jones' form."


SELECT FirstName, LastName
FROM Students
WHERE FormTutor = 'Mr. Jones';

Remember: Always end your full SQL query with a semicolon (;).

Part 3: The Wildcard (*) and AND

Two more essential tools for your queries.

The Wildcard (*)

Instead of listing every field name, you can use the * (asterisk) wildcard. It's a shortcut that means "all fields".

"Get *all information* about the student with ID 1002."


SELECT *
FROM Students
WHERE StudentID = 1002;

Using AND to Combine Filters

What if you need to filter by two conditions? You use the AND keyword in your WHERE clause.

"Get the `Song` from the `TblMusic` table where the `Genre` is 'Rock' AND the `Year` is 1991."


SELECT Song
FROM TblMusic
WHERE Genre = 'Rock' AND Year = 1991;

Part 4: Quiz (Page 1 of 6)

1. A single row in a database table, representing one item (like one student), is called a:
2. What is the SQL wildcard character used to select all fields from a table?
3. Which SQL command is used to specify the table you want to search?

Part 4: Quiz (Page 2 of 6)

4. Complete the query to select the ItemCode for items with a Stock level less than 10.

(Context: The table is named ITEMS and has fields ItemCode, Stock)

SELECT
FROM
WHERE
5. Complete the query to return all fields for students from the Results table who take the subject "Art".

(Context: The table is named Results and has fields StudentID, Subject, Grade)

SELECT
FROM
WHERE
6. Complete the query to find the PlayerName and Score for players in the 'Red' team.

(Context: The table is named TblPlayers and has fields PlayerName, Score, Team)

SELECT
FROM
WHERE

Part 4: Quiz (Page 3 of 6)

7. This SQL is incorrect. Re-type it correctly to find ActID and Stage from TblMusic where the Day is 'Saturday'. 
SELECT ActID AND Stage
FROM TblActs
IF Day
Saturday
8. This SQL is incorrect. Re-type it correctly to find all fields from TblBookings for stays longer than 1 night.

(Context: The field for nights is named Nights)

SELECT ALL
FROM TblBookings
IF Nights < 1
9. This SQL is incorrect. Re-type it to find Title and Author from TblLibrary where the Genre is 'Fantasy'. 
SELECT Title, Author
WHERE Genre = 'Fantasy'
FROM TblLibrary

Part 4: Quiz (Page 4 of 6)

10. Complete the query to select the StudentName field from the conduct table for all records that have negative Points.

(Context: The table is conduct. Fields are StudentName, Points)

SELECT
FROM
WHERE
11. Complete the query to find DroneID and Mileage from TblDrone for 'Octocopter' types with Mileage > 50000.

(Context: Table: TblDrone. Fields: DroneID, Mileage, DroneType)

SELECT
FROM
WHERE
12. Complete the query to find SensorID from events for 'Door' sensors triggered for more than 20 seconds.

(Context: Table: events. Fields: SensorID, SensorType, Length)

SELECT
FROM
WHERE

Part 4: Quiz (Page 5 of 6)

13. Complete the query to find the Make and Model of all cars from the year 2020.

(Context: Table: TblCars. Fields: Make, Model, Year)

SELECT
FROM
WHERE
14. Complete the query to find all fields for 'Fantasy' books written by 'Tolkien'.

(Context: Table: TblLibrary. Fields: Title, Author, Genre)

SELECT
FROM
WHERE
15. This SQL is incorrect. Re-type it to find the Team for the player 'Harry'.

(Context: Table: TblPlayers. Fields: PlayerName, Team)

SELECT PlayerName
FROM TblPlayers
WHERE Team = 'Harry'
16. Complete the query to find the Price of the car with CarID 105.

(Context: Table: TblCars. Fields: CarID, Price)

SELECT
FROM
WHERE

Part 4: Quiz (Page 6 of 6)

17. This SQL is incorrect. Re-type it to find PlayerName for players with Score > 100.

(Context: Table: TblPlayers. Fields: PlayerName, Score)

SELECT PlayerName
FROM TblPlayers
WHERE 'Score > 100'
18. Complete the query to find Title for 'Comedy' films made in the Year 2015.

(Context: Table: TblFilms. Fields: Title, Genre, Year)

SELECT
FROM
WHERE
19. Drag the correct tokens into the query. Find the StudentID of a student named 'David'.

(Context: Table: Students. Fields: StudentID, FirstName)

SELECT
FROM
WHERE

Drag from here:

Students * FirstName = 'David' TblPlayers StudentID FirstName

Part 5: Final Challenge (Write from Scratch)

Time to write the full queries yourself! Write the complete SQL statement, including SELECT, FROM, WHERE, and a semicolon (;).

20. Write a full SQL query to find the Model and Price from the TblCars table for all cars where the Make is 'Ford'.

(Context: Table: TblCars. Fields: Make, Model, Price)

21. Write a full SQL query to find *all fields* from the TblLibrary table for books where the Genre is 'Sci-Fi' AND the Author is 'Asimov'.

(Context: Table: TblLibrary. Fields: Title, Author, Genre)

22. Write a full SQL query to find the PlayerName from the TblPlayers table for players who have a Score greater than 500.

(Context: Table: TblPlayers. Fields: PlayerName, Score)

23. Write a full SQL query to find the Subject and Grade from the Results table for the student with StudentID 1007.

(Context: Table: Results. Fields: StudentID, Subject, Grade)

Lesson Complete!

Well done, Student!

This is your 1st attempt.

Your final score:
0 / 0

Your Personal Feedback: