Querying and SQL Functions

MySQL functions and grouping: math, text and date functions, aggregate functions, GROUP BY with HAVING, ORDER BY, and Cartesian products and equi-joins of two tables.

Key ideas

A single-row function gives one result for each row; an aggregate (multiple-row) function gives one result for a whole group of rows.
ROUND(x, d) keeps d decimals, rounding a 5 or more up (half away from zero); MOD(a, b) gives the remainder and POWER(a, b) gives a to the power b.
Text positions count from 1: MID or SUBSTR(s, p, n) takes n characters from position p; LEFT and RIGHT take them from the ends; INSTR(s, t) gives where t first appears, ignoring case (0 if absent).
LTRIM, RTRIM and TRIM remove spaces from the start, the end or both; LENGTH counts every character, spaces too; UPPER (UCASE) and LOWER (LCASE) change case.
YEAR, MONTH, DAY, MONTHNAME and DAYNAME take parts of a date; NOW() gives the current date and time.
COUNT(*) counts every row; COUNT(column), SUM, AVG, MAX and MIN skip NULL; COUNT(DISTINCT column) counts each different value once.
GROUP BY makes one output row per group; WHERE filters rows before grouping and HAVING filters the groups after it.
A Cartesian product pairs every row of one table with every row of the other; an equi-join keeps only the pairs whose common column matches, written TABLE.column.

Remember

ROUND	ROUND(912.564, 2) → 912.56; ROUND(912.565, 2) → 912.57; ROUND(912.5, 0) → 913
MOD, POWER	MOD(47, 5) → 2; POWER(3, 4) → 81
Text	MID('Keyboard', 3, 4) → yboa; LEFT('Network', 3) → Net; INSTR('Database', 'BAS') → 5
Dates	MONTH('2026-03-14') → 3; MONTHNAME → March; DAYNAME → Saturday
AVG of whole numbers	four decimals: AVG of 78, 81, 79 and 79 → 79.2500
Clause order	SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Equi-join	SELECT Name, ClubName FROM STUDENT, CLUB WHERE STUDENT.ClubId = CLUB.ClubId;
Cartesian product A × B	degree = columns of A + columns of B; cardinality = rows of A × rows of B

Watch out

Counting text positions from 0: in SQL the first character is position 1.
Writing WHERE COUNT(*) > 1: a condition on groups goes in HAVING.
Counting NULLs in COUNT(column), SUM or AVG: only COUNT(*) counts every row.
Multiplying the columns of a Cartesian product: the columns add and the rows multiply.

Worked example

Write the output of:
SELECT LOWER(RIGHT('Software', 2));

Working: RIGHT gives re; LOWER makes it small letters

Answer: re