SQL Fundamentals

This lesson covers the fundamentals of SQL (Structured Query Language) for the OCR A-Level Computer Science (H446) specification. SQL is the standard language for managing and querying relational databases.

---

What is SQL?

SQL (Structured Query Language) is a declarative language used to:

• Query data (retrieve specific information).
• Manipulate data (insert, update, delete records).
• Define data structures (create tables, set constraints).

SQL is declarative -- you describe WHAT data you want, not HOW to get it. The database engine determines the most efficient way to execute the query.

---

SELECT Queries

The SELECT statement retrieves data from one or more tables.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column ASC|DESC;

Examples

-- Select all columns from Students
SELECT * FROM Students;

-- Select specific columns
SELECT Name, Age FROM Students;

-- Select with a condition
SELECT Name, Grade FROM Students WHERE Grade = 'A';

-- Select with multiple conditions
SELECT Name, Age FROM Students WHERE Age >= 16 AND Grade = 'A';

-- Select with OR
SELECT Name FROM Students WHERE Subject = 'Maths' OR Subject = 'Science';

---

WHERE Clause and Operators

Operator	Description	Example
=	Equal to	WHERE Age = 17
<> or !=	Not equal to	WHERE Grade <> 'U'
>	Greater than	WHERE Score > 80
<	Less than	WHERE Score < 50
>=	Greater than or equal	WHERE Age >= 18
<=	Less than or equal	WHERE Price <= 10.00
BETWEEN	Range (inclusive)	WHERE Age BETWEEN 16 AND 18
LIKE	Pattern matching	WHERE Name LIKE 'A%'
IN	Matches any in a list	WHERE Grade IN ('A', 'B', 'C')
IS NULL	Is a null value	WHERE Phone IS NULL
IS NOT NULL	Is not null	WHERE Email IS NOT NULL
AND	Both conditions true	WHERE Age > 16 AND Grade = 'A'
OR	Either condition true	WHERE Grade = 'A' OR Grade = 'B'
NOT	Negates a condition	WHERE NOT Grade = 'U'

---

Wildcards with LIKE

Wildcard	Meaning	Example
%	Any number of characters (including zero)	'A%' matches Alice, Adam, A
_	Exactly one character	'_ob' matches Bob, Rob

-- Names starting with 'A'
SELECT * FROM Students WHERE Name LIKE 'A%';

-- Names ending with 'e'
SELECT * FROM Students WHERE Name LIKE '%e';

-- Names containing 'mi'
SELECT * FROM Students WHERE Name LIKE '%mi%';

-- Names with exactly 5 characters
SELECT * FROM Students WHERE Name LIKE '_____';

---

ORDER BY

Sorts the results in ascending (ASC, default) or descending (DESC) order.

-- Sort by name alphabetically
SELECT * FROM Students ORDER BY Name ASC;

-- Sort by score highest first
SELECT * FROM Students ORDER BY Score DESC;

-- Sort by multiple columns
SELECT * FROM Students ORDER BY Grade ASC, Name ASC;

---

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single result.