Database Management: Joins, Aggregates & Updates

Grade 12 SQL: pulling data from several tables at once, summarising it, and changing it.

A join combines records from two tables using the foreign-key relationship. INNER JOIN returns only records that match on both sides — the one you want almost every time.

Example

SELECT tblLearners.FirstName, tblSubjects.SubjectName, tblMarks.Mark FROM tblMarks INNER JOIN tblLearners ON tblMarks.LearnerID = tblLearners.LearnerID INNER JOIN tblSubjects ON tblMarks.SubjectID = tblSubjects.SubjectID WHERE tblMarks.Mark >= 75;

Function	Returns	Example
COUNT(*)	Number of records	COUNT(*) AS NumLearners
SUM(field)	Total	SUM(Amount) AS TotalPaid
AVG(field)	Mean	ROUND(AVG(Mark), 1) AS AvgMark
MAX / MIN	Largest / smallest	MAX(Mark) AS TopMark

GROUP BY decides what each aggregate covers; HAVING then filters those groups. The distinction between WHERE and HAVING is examined almost every year.

Example

SELECT Grade, COUNT(*) AS NumLearners, AVG(Mark) AS AvgMark FROM tblMarks GROUP BY Grade HAVING COUNT(*) > 5;

WHERE vs HAVING: WHERE filters individual records BEFORE grouping; HAVING filters the groups AFTER. You cannot use an aggregate in WHERE.
INSERT / UPDATE / DELETE: The data-modification statements. UPDATE and DELETE without a WHERE clause affect every record — a classic trap.
Subquery: A query in brackets whose result the outer query uses, e.g. WHERE Mark = (SELECT MAX(Mark) FROM tblMarks).

Example

UPDATE tblStock SET Price = Price * 1.1 WHERE CategoryID = 3; DELETE FROM tblOrders WHERE OrderDate < '2020-01-01';

💡 Exam Tip

Every non-aggregated field in the SELECT list must also appear in GROUP BY. Forgetting one is the most common reason a grouping query is rejected.