Structured Query Language - data definition (CREATE TABLE) and data manipulation (SELECT with WHERE, ORDER BY, joins and aggregate functions, plus INSERT, UPDATE and DELETE).

What this dot point is asking

CCEA expects you to read and write Structured Query Language (SQL): the data definition statement CREATE TABLE (with keys), and the data manipulation statements SELECT (with WHERE, ORDER BY, joins and aggregate functions) plus INSERT, UPDATE and DELETE. Writing a SELECT with conditions and sorting, a query with a join, and an aggregate with GROUP BY are high-mark questions, so the syntax must be accurate.

The answer

Defining a database (data definition)

CREATE TABLE Student (
    studentID  INTEGER PRIMARY KEY,
    surname    VARCHAR(30),
    yearGroup  INTEGER,
    mark       INTEGER
);

CREATE TABLE names the table and lists each field with its data type, marking the primary key (a foreign key would be declared with REFERENCES to the related table).

Querying data with SELECT

SELECT surname, mark
FROM Student
WHERE yearGroup = 13 AND mark >= 60
ORDER BY mark DESC;

Joins, aggregates, and changing data

A join combines rows from related tables on a matching column, usually a foreign key to a primary key, so a query can return data spread across tables:

SELECT Order.orderID, Customer.name
FROM Order
INNER JOIN Customer ON Order.customerID = Customer.customerID;

Aggregate functions summarise many rows into one value: COUNT, SUM, AVG, MAX, MIN, often with GROUP BY to summarise per group:

SELECT yearGroup, AVG(mark) AS averageMark
FROM Student
GROUP BY yearGroup;

The DML statements that change data:

INSERT INTO Student (studentID, surname, yearGroup, mark)
VALUES (101, 'Murphy', 13, 72);

UPDATE Student SET mark = 75 WHERE studentID = 101;

DELETE FROM Student WHERE studentID = 101;

Worked example: building up a query with an aggregate

Examples in context

Example 1. A shop's sales report. Management want total sales per product. A query joins OrderLine to Product, groups by product name, and uses SUM(quantity * price) with ORDER BY to rank products by revenue. One SQL statement turns thousands of rows across two tables into a tidy ranked summary, which is why aggregates and joins are core skills.

Example 2. Maintaining the data. When a customer changes address, a single UPDATE Customer SET address = '...' WHERE customerID = 42; corrects it once, and referential integrity keeps their orders linked. A mistaken row is removed with a carefully targeted DELETE using a WHERE clause; omitting the WHERE would delete every row, a classic and serious error.

Try this

Q1. Write SQL to list all fields of every Product whose price is below 10. [2 marks]

• Cue. SELECT * FROM Product WHERE price < 10;

Q2. State the difference between the WHERE and HAVING clauses. [2 marks]

• Cue. WHERE filters individual rows before grouping; HAVING filters the groups after a GROUP BY and can use aggregate functions.

Q3. Name three SQL aggregate functions. [3 marks]

• Cue. Any three of COUNT, SUM, AVG, MAX, MIN.