Using a database through Structured Query Language (SELECT, INSERT, UPDATE, DELETE) and query by example, including selecting, sorting and joining data.

What this dot point is asking

CCEA wants you to use a database through Structured Query Language (SELECT, INSERT, UPDATE, DELETE) and through query by example, including selecting, filtering, sorting and joining data. This is the practical side of databases: getting data in, out and changed.

Selecting data with SQL

A basic query names the columns, the table, an optional filter and an optional sort:

SELECT Surname, Town
FROM Students
WHERE Year = 13
ORDER BY Surname;

This returns the surname and town of Year 13 students, sorted alphabetically. Using SELECT * returns all columns, which is convenient but wasteful when only some are needed.

Inserting, updating and deleting

INSERT INTO Students (StudentID, Surname, Year, Town)
VALUES (1024, 'Kelly', 13, 'Bangor');

UPDATE Students
SET Town = 'Newtownards'
WHERE StudentID = 1024;

DELETE FROM Students
WHERE StudentID = 1024;

Joining tables

Because data is split across normalised tables, queries often need a JOIN to combine them on a matching key:

SELECT Students.Surname, Bookings.BookingDate
FROM Students
JOIN Bookings ON Students.StudentID = Bookings.StudentID
WHERE Bookings.BookingDate = '2026-06-16';

This links each booking to the student who made it by matching the foreign key in Bookings to the primary key in Students. A JOIN is needed precisely because normalisation splits related data across tables; without it, a query could only see one table at a time.

Summarising data

SQL can also summarise rather than just list. Aggregate functions such as COUNT, SUM, AVG, MAX and MIN calculate a single value over many rows, and GROUP BY produces one result per group:

SELECT Town, COUNT(*) AS NumberOfStudents
FROM Students
GROUP BY Town;

This counts how many students live in each town, returning one row per town. Aggregate queries turn raw records into the kind of summary information a manager actually wants, which is why they are examined alongside the basic SELECT.

Query by example

Query by example (QBE) is a visual alternative to SQL. The user is shown a grid of the fields and enters criteria under the columns they want (for example "13" under Year and a sort order under Surname); the database then builds and runs the underlying query. QBE is easier for non-technical users because no syntax must be memorised, while SQL is more precise, more powerful for complex queries, and can be saved and embedded in programs.

Try this

Q1. Write an SQL statement to return all columns of the Books table where Genre is 'Science'. [2 marks]

• Cue. SELECT * FROM Books WHERE Genre = 'Science';

Q2. State which SQL keyword sorts the results of a query. [1 mark]

• Cue. ORDER BY.

Q3. Explain why a DELETE statement should normally include a WHERE clause. [2 marks]

• Cue. The WHERE clause selects which records to remove; without it, DELETE removes every record in the table, causing unintended data loss.