SQL | OCR A-Level Computer Science

SQL is the standard language for interacting with relational databases. It allows you to create, query, update, and delete data.

Definition

SQL (Structured Query Language) is a declarative programming language used to manage and manipulate relational databases.

SELECT Queries

Worked Example

Basic SELECT:

sql

SELECT FirstName, LastName, Email
FROM Student
WHERE Year = 12
ORDER BY LastName ASC;

This query:
• Selects three columns
• From the Student table
• Only where Year equals 12
• Sorted alphabetically by LastName

Worked Example

Using Wildcards:

sql

-- Select ALL columns from Student
SELECT * FROM Student;

-- Select products where name starts with 'A'
SELECT * FROM Product WHERE Name LIKE 'A%';

Wildcards:
• % matches any number of characters
• _ matches exactly one character
• LIKE 'A%' finds anything starting with A
• LIKE '_ones' finds 5-letter words ending in 'ones'

Combining Conditions with AND and OR

A WHERE clause can test more than one condition. With AND a record is returned only if both conditions are true. With OR it is returned if at least one of them is true.

Worked Example

AND and OR:

sql

-- Year 12 students whose last name begins with S
SELECT FirstName, LastName
FROM Student
WHERE Year = 12 AND LastName LIKE 'S%';

-- Students who are in Year 12 or Year 13
SELECT FirstName, LastName
FROM Student
WHERE Year = 12 OR Year = 13;

JOIN Queries

Definition

A JOIN combines rows from two or more tables based on a related column (usually a foreign key relationship).

Worked Example

Worked Example: JOIN Query

Tables:
• Student: StudentID, Name, TutorID
• Tutor: TutorID, TutorName, Department

sql

SELECT Student.Name, Tutor.TutorName, Tutor.Department
FROM Student
JOIN Tutor ON Student.TutorID = Tutor.TutorID
WHERE Tutor.Department = 'Computer Science';

Result: Names of students and their tutors, but only for the Computer Science department

Nested SELECT

Definition

A nested SELECT (also called a subquery) is a SELECT statement written inside another query. The inner query runs first and its result is used by the outer query.

Worked Example

Worked Example: Nested SELECT

sql

SELECT Name
FROM Student
WHERE TutorID = (
    SELECT TutorID
    FROM Tutor
    WHERE TutorName = 'Mr Brown'
);

How it runs:
• The inner query finds Mr Brown's TutorID in the Tutor table
• The outer query then returns the name of every student who has that TutorID
• The same result could be produced with a JOIN: a nested SELECT is another way to use data from a second table

Data Modification Commands

Worked Example

INSERT - Add new records:

sql

INSERT INTO Student (Name, Year)
VALUES ('Alice', 12);

UPDATE - Modify existing records:

sql

UPDATE Student
SET Year = 13
WHERE StudentID = 'S001';

DELETE - Remove records:

sql

DELETE FROM Student
WHERE Year = 11;

CREATE TABLE - Make a new table:

sql

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Year INT,
    Email VARCHAR(100)
);

ALTER TABLE - Change table structure:

sql

ALTER TABLE Student
ADD Email VARCHAR(100);

DROP

DELETE removes records but leaves the table in place. DROP removes the table itself: its structure and every record in it.

Worked Example

DROP TABLE - Remove a whole table:

sql

DROP TABLE Student;

Compare with DELETE:

sql

DELETE FROM Student;

After DELETE with no WHERE clause the Student table still exists but is empty. After DROP there is no Student table at all.

Key Point

SQL Command Summary:
• SELECT - retrieve data from tables
• INSERT INTO - add new records
• UPDATE - modify existing records
• DELETE FROM - remove records
• CREATE TABLE - create a new table
• ALTER TABLE - modify table structure
• DROP TABLE - delete a table entirely
• JOIN - combine data from multiple tables
• WHERE - filter results by condition
• ORDER BY - sort results (ASC/DESC)
• LIKE - pattern matching with wildcards