Worksheets › Class 12 › Querying and SQL Functions › Cheat sheet
Class 12 Querying and SQL Functions: cheat sheet
CBSE Informatics Practices. 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
Practise Querying and SQL Functions
Revise with this page, then practise: every Querying and SQL Functions sheet in the app is new, with an answer key and working.
Coming soon for Android and iPhone.