SQL to search for data

The use of SQL to search for data

2.2.3 Additional Programming Techniques

Additional Programming Techniques — Note 5 of 12

SQL commands:SELECT, FROM, WHERE

What you need to know

SQL (Structured Query Language) is used to search for, manage, and retrieve data from a database. The three key SQL commands you need to know are SELECT (which fields to return), FROM (which table to search), and WHERE (which conditions to apply). You can select specific fields, multiple fields, or all fields using the wildcard *.

What to know

SELECT specifies which field(s) to retrieve. List only the fields asked for — don't use * if specific fields are needed.
FROM specifies which table to search in. SPELLING MUST BE EXACT (e.g. TblResult not TblResults).
WHERE applies a condition to filter the results, e.g. WHERE YearGroup = 11.
SELECT * returns ALL fields from the table.
Multiple conditions can be combined with AND and OR, e.g. WHERE DroneType = "Octocopter" AND Mileage > 50000.
Use = for comparison in SQL (not ==, although == is accepted in some exam mark schemes).
SQL is not case-sensitive for keywords, but the convention is to write them in UPPERCASE.

Key terms

Database: An organised collection of related data stored in tables that allows data to be easily accessed, managed and queried.

Exam technique

Tip:No spaces in field names. Spelling of table and field names must match exactly what is given (TblResult not TblResults).
Tip:The order must be correct: SELECT ... FROM ... WHERE ...
Mistake:SELECT ALL is NOT valid SQL. Use SELECT * or list all field names separated by commas.
Mistake:IF is NOT valid SQL — use WHERE for filtering conditions.
Tip:String values in WHERE must use quotation marks: WHERE SensorType = "Door". Numeric values don't need quotes: WHERE Mileage > 50000.
Tip:When asked for multiple conditions, combine with AND: WHERE SensorType = "Door" AND Length > 20.

Code examples

SQL query examples

sql

-- Get all unsold cars
SELECT Model
FROM Cars
WHERE Sold = FALSE

-- Get multiple fields with conditions
SELECT Model, Manufacturer, Price
FROM Cars
WHERE Sold = TRUE AND Price > 4000

-- Get all fields for Ford cars
SELECT *
FROM Cars
WHERE Manufacturer = 'Ford'

Ready to test yourself?

Practise 2.2.3 questions

← Previous noteRecords to store data2.2.3 Additional Programming Techniques Next note →one-dimensional (1D)2.2.3 Additional Programming Techniques

Back to Additional Programming Techniques

Revise another OCR GCSE topic: Programming Fundamentals

-- Get all unsold cars SELECT Model FROM Cars WHERE Sold = FALSE -- Get multiple fields with conditions SELECT Model, Manufacturer, Price FROM Cars WHERE Sold = TRUE AND Price > 4000 -- Get all fields for Ford cars SELECT * FROM Cars WHERE Manufacturer = 'Ford'