Write SQL queries using SELECT with WHERE, ORDER BY, JOIN, GROUP BY and aggregate functions, and modify data with INSERT, UPDATE and DELETE

What this dot point is asking

SEAB wants you to write SQL that retrieves data with SELECT, filters with WHERE, sorts with ORDER BY, combines tables with JOIN, summarises with GROUP BY and aggregate functions, and changes data with INSERT, UPDATE and DELETE. The core idea is that SQL is declarative: you describe the result you want, and the database works out how to produce it.

The answer

SELECT, WHERE and ORDER BY

The SELECT statement reads data. Choose columns, the source table, an optional filter and an optional sort:

SELECT Name, ClassID
FROM Student
WHERE ClassID = 3
ORDER BY Name ASC;

WHERE keeps only rows matching a condition (=, <, >, LIKE, AND, OR). ORDER BY sorts the result, ascending by default or DESC for descending.

JOIN across tables

A JOIN combines rows from two tables where a foreign key matches a primary key:

SELECT Student.Name, Class.ClassName
FROM Student
JOIN Class ON Student.ClassID = Class.ClassID;

The ON clause states the matching condition. Always qualify column names with the table when they could be ambiguous.

GROUP BY and aggregate functions

Aggregate functions summarise many rows into one value: COUNT, SUM, AVG, MIN, MAX. GROUP BY applies them per group:

SELECT ClassID, COUNT(*) AS NumStudents, AVG(Mark) AS MeanMark
FROM Student
GROUP BY ClassID;

Use HAVING to filter groups after aggregation (whereas WHERE filters rows before).

INSERT, UPDATE and DELETE

These change the data:

INSERT INTO Student (StudentID, Name, ClassID)
VALUES (5012, 'Aisyah Rahman', 3);

UPDATE Student SET ClassID = 4 WHERE ClassID = 3;

DELETE FROM Student WHERE StudentID = 5012;

The WHERE clause on UPDATE and DELETE decides which rows are affected. Omit it and the statement changes or removes every row.

Examples in context

Example 1. A reporting dashboard. A sales dashboard runs a single grouped query - join orders to products, group by category, sum the revenue - to turn thousands of raw order rows into a short summary table. The same data underlies many reports just by changing the grouping column.

Example 2. Safe bulk edits. When a school renames a class, an administrator runs one UPDATE ... WHERE ClassID = ... instead of editing rows by hand. The WHERE clause is what makes the change precise; testing the matching SELECT first confirms exactly which rows will be touched.

Try this

Q1. Write SQL to list the names of students with a mark above 80, sorted highest first, from Student(Name, Mark). [2 marks]

• Cue. SELECT Name FROM Student WHERE Mark > 80 ORDER BY Mark DESC;

Q2. What does an UPDATE statement do if you forget the WHERE clause? [1 mark]

• Cue. It updates every row in the table, not just the intended ones.

Q3. Which clause filters groups produced by GROUP BY? [1 mark]

• Cue. HAVING filters aggregated groups, after grouping; WHERE filters rows before.