Theory Notes/🗄️ Topic 4: Data & Information Management, Solution Development/11.4.11

11.4.11Grade 11

Complex SQL on a Single Table

Grouping, calculated fields, aggregate functions and more powerful SQL — building directly on 10.4.11.

Grouping and filtering groups:

Clause	Purpose	Example
GROUP BY	Groups rows sharing the same value in a field, usually combined with an aggregate function	SELECT Grade, COUNT(*) FROM Learners GROUP BY Grade
HAVING	Filters groups AFTER grouping (unlike WHERE, which filters rows before grouping)	SELECT Grade, COUNT() FROM Learners GROUP BY Grade HAVING COUNT() > 20

Creating calculated fields:

Concatenating fields — joining field values together, e.g. combining Name and Surname into a full name.
Renaming fields with AS — giving a calculated or existing field a friendlier output name.
Generating random numbers within SQL.
Formatting numbers — ROUND (round to a set number of decimals), INT/FLOOR (round down), CEILING (round up).
Casting a field — converting a field's value from one type to another within the query.

Example

SELECT Name, ROUND(Average, 1) AS RoundedAverage FROM Learners;

Mathematical operators including MOD (remainder) and DIV/integer division, usable directly within SQL calculations.

Aggregate functions summarise data across multiple rows:

Function	Purpose
SUM	Total of a numeric field
AVERAGE / AVG	Mean of a numeric field
MIN / MAX	Smallest / largest value
COUNT	Number of matching rows

Common date functions: NOW (current date/time), YEAR, MONTH, TIME, DATE, HOUR, MINUTE, DAY — used to extract or work with parts of a date/time value, including accurately calculating a person's age from a date of birth (accounting for whether their birthday has occurred yet this year).

String functions:

Function	Purpose
LENGTH	Number of characters in a string
MID / SUBSTR	Extract characters from the middle of a string
LEFT	Extract characters from the start of a string
RIGHT	Extract characters from the end of a string
Position-finding function (e.g. INSTR/LOCATE)	Find the position of a specific character within a string

These functions are frequently combined in a single query, and Boolean conditions from 11.4.4 apply fully within WHERE/HAVING clauses here.

Altering data — INSERT with only some fields specified (letting an autonumber primary key be generated automatically), and applying the Boolean conditions from 11.4.4 within UPDATE/DELETE WHERE clauses.

Example

INSERT INTO Learners (Name, Grade) VALUES ('Zola', 11); -- LearnerID autonumbers automatically

💡 Exam Tip

Remember the key WHERE vs HAVING distinction: WHERE filters individual rows before any grouping happens; HAVING filters entire groups after GROUP BY has been applied — you cannot use an aggregate function like COUNT() inside a WHERE clause.