SQL | OCR H446

Learning Objectives

By the end of this topic you will be able to:

Write SQL SELECT queries using WHERE, ORDER BY, GROUP BY, HAVING, LIKE and wildcards

Write JOIN queries (INNER JOIN) to query multiple tables

Write INSERT, UPDATE and DELETE statements

Write CREATE TABLE statements with constraints (PRIMARY KEY, NOT NULL, FOREIGN KEY)

SELECT

SELECT Queries

SELECT Name, Age
FROM Student
WHERE Age > 16
ORDER BY Name ASC;

Filtering

WHERE Name LIKE 'A%'
-- names starting with A

WHERE Grade BETWEEN 70 AND 100

WHERE Dept = 'CS'
AND Salary > 30000

Aggregates

SELECT Dept,
COUNT(*) AS Total,
AVG(Salary)
FROM Staff
GROUP BY Dept
HAVING COUNT(*) > 5;

JOIN

INNER JOIN — Querying Multiple Tables

An INNER JOIN returns rows where there is a matching value in both tables, linking them via a shared key. Essential when data is spread across multiple related tables.

SELECT Student.Name, Course.Title, Enrolment.Date
FROM Enrolment
INNER JOIN Student ON Enrolment.StudentID = Student.StudentID
INNER JOIN Course ON Enrolment.CourseID = Course.CourseID
WHERE Course.Title = 'Computer Science';

Only records with a match in both tables are included. Students not enrolled on any course would not appear.

DML

INSERT, UPDATE and DELETE

INSERT

INSERT INTO Student
(StudentID, Name, Age)
VALUES
(1004, 'Dan', 17);

UPDATE

UPDATE Student
SET Age = 18
WHERE StudentID = 1004;

DELETE

DELETE FROM Student
WHERE StudentID = 1004;

Warning

Always include a WHERE clause in UPDATE and DELETE. Without it, all rows in the table will be updated/deleted. This is an irreversible data loss.

DDL

CREATE TABLE

CREATE TABLE Student (
StudentID INTEGER PRIMARY KEY,
Name       VARCHAR(50) NOT NULL,
DOB        DATE,
CourseID   INTEGER
REFERENCES Course(CourseID)
);

Common Data Types

INTEGER, REAL/FLOAT, VARCHAR(n), CHAR(n), DATE, BOOLEAN

Common Constraints

PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, REFERENCES (foreign key)

Exam Practice

OCR H446 Style · 4 marks

A database has tables: Order(OrderID, CustomerID, Date, Total) and Customer(CustomerID, Name, City). Write an SQL query to display the Name and Total for all orders over £100, sorted by Total descending.

[4 marks]

SELECT Customer.Name, Order.Total
FROM Order
INNER JOIN Customer ON Order.CustomerID = Customer.CustomerID
WHERE Order.Total > 100
ORDER BY Order.Total DESC;

Correct SELECT fields (Name, Total)

Correct INNER JOIN with ON clause linking CustomerID

WHERE Total > 100

ORDER BY Total DESC

Common Mistakes

Don’t Lose Marks

Using WHERE instead of HAVING for aggregate conditions — WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY. WHERE COUNT(*) > 5 is wrong; it must be HAVING COUNT(*) > 5.

Forgetting table.column notation in JOINs — when two tables share a column name (e.g. CustomerID), you must qualify it as Order.CustomerID or an ambiguity error occurs.

Omitting WHERE in UPDATE/DELETE — DELETE FROM Student; with no WHERE clause deletes every row. Always add WHERE unless a full table wipe is intentional.