Class 12 Advanced SQL Queries: Complete Concepts and Practice

Big picture

Basic SQL selects rows and columns. Functions let a query calculate, reformat and summarise data: round a price, extract part of a name, find the month of a date, or total the sales for each region. MySQL has single-row functions, which return one result per row, and aggregate (multiple-row) functions, which return one result for a group of rows.

The board paper has many "write the output" questions on these functions, so learn each function's exact behaviour, especially ROUND with negative places, MID/SUBSTRING positions (counting from 1), and INSTR.

Learning goals

Use the math functions POWER, ROUND and MOD.
Use the text functions UPPER/UCASE, LOWER/LCASE, MID/SUBSTRING/SUBSTR, LENGTH, LEFT, RIGHT, INSTR, LTRIM, RTRIM and TRIM.
Use the date functions NOW, DATE, MONTH, MONTHNAME, YEAR, DAY and DAYNAME.
Use the aggregate functions MAX, MIN, AVG, SUM and COUNT.
Use GROUP BY, HAVING and ORDER BY together.
Join two tables using a common column.

Core ideas

1. Math functions

Function	Returns	Example	Result
`POWER(x, y)` or `POW(x, y)`	x raised to y	`POWER(2, 3)`	8
`ROUND(n, d)`	n rounded to d decimal places	`ROUND(15.193, 1)`	15.2
`ROUND(n)`	n rounded to the nearest integer	`ROUND(15.5)`	16
`ROUND(n, -d)`	n rounded to the left of the decimal point	`ROUND(15.193, -1)`	20
`MOD(a, b)`	Remainder of a ÷ b	`MOD(17, 5)`	2

More ROUND examples: ROUND(1456.78, -2) gives 1500; ROUND(1456.78, 0) gives 1457; ROUND(3.14159, 2) gives 3.14.

2. Text functions

Positions in MySQL strings start at 1.

Function	Returns	Example	Result
`UPPER(s)` / `UCASE(s)`	Upper case	`UPPER('pandas')`	PANDAS
`LOWER(s)` / `LCASE(s)`	Lower case	`LOWER('SQL')`	sql
`MID(s, pos, n)` / `SUBSTRING(s, pos, n)` / `SUBSTR(s, pos, n)`	n characters starting at position pos	`MID('Informatics', 3, 4)`	form
`LENGTH(s)`	Number of characters (spaces count)	`LENGTH(' Hi ')`	6
`LEFT(s, n)`	Leftmost n characters	`LEFT('Computer', 3)`	Com
`RIGHT(s, n)`	Rightmost n characters	`RIGHT('Computer', 3)`	ter
`INSTR(s, sub)`	Position of the first occurrence of sub (0 if not found)	`INSTR('Informatics', 'for')`	3
`LTRIM(s)` / `RTRIM(s)`	Removes leading / trailing spaces	`LTRIM(' Hi')`	'Hi'
`TRIM(s)`	Removes leading and trailing spaces	`TRIM(' Hi ')`	'Hi'

If the length is omitted, MID('Informatics', 3) returns everything from position 3: formatics.

3. Date functions

Function	Returns	Example (date '2024-08-15')	Result
`NOW()`	Current date and time		e.g. 2024-08-15 10:30:00
`DATE(dt)`	Date part of a date-time	`DATE('2024-08-15 10:30:00')`	2024-08-15
`MONTH(d)`	Month number	`MONTH('2024-08-15')`	8
`MONTHNAME(d)`	Month name	`MONTHNAME('2024-08-15')`	August
`YEAR(d)`	Year	`YEAR('2024-08-15')`	2024
`DAY(d)`	Day of the month	`DAY('2024-08-15')`	15
`DAYNAME(d)`	Weekday name	`DAYNAME('2024-08-15')`	Thursday

Dates are written in the format 'YYYY-MM-DD'. For example, DAYNAME('2025-01-26') is Sunday.

4. Using functions in queries

Consider a table SALES (Id, Product, Price, Qty, SaleDate, Region).

SELECT Product, ROUND(Price * Qty, 0) AS Amount FROM SALES;
SELECT UPPER(Product), LENGTH(Product) FROM SALES;
SELECT * FROM SALES WHERE MONTH(SaleDate) = 8;
SELECT Product FROM SALES WHERE LEFT(Product, 1) = 'P';
SELECT DISTINCT YEAR(SaleDate) FROM SALES;

5. Aggregate functions, GROUP BY and HAVING

Function	Returns
`MAX(col)`, `MIN(col)`	Largest and smallest values
`SUM(col)`, `AVG(col)`	Total and average of values
`COUNT(col)`	Number of non-NULL values
`COUNT(*)`	Number of rows
`COUNT(DISTINCT col)`	Number of different non-NULL values

All aggregate functions ignore NULL, except COUNT(*).

SELECT Region, SUM(Price * Qty) AS Total
FROM SALES
GROUP BY Region
HAVING SUM(Price * Qty) > 50000
ORDER BY Total DESC;

WHERE filters rows before grouping and can't contain aggregate functions.
GROUP BY forms groups of rows with the same value.
HAVING filters groups using aggregate conditions.
ORDER BY sorts the final result (ASC by default, DESC for descending).

Example with data. EMP has Dept and Salary: IT (62000, 58000), Sales (45000, 51000), HR (38000).

Query	Result
`SELECT Dept, AVG(Salary) FROM EMP GROUP BY Dept;`	HR 38000, IT 60000, Sales 48000
`SELECT Dept, COUNT() FROM EMP GROUP BY Dept HAVING COUNT() > 1;`	IT 2, Sales 2
`SELECT MAX(Salary) - MIN(Salary) FROM EMP;`	24000

6. Joining two tables

When related data is in two tables, a join combines rows using a common column.

SELECT E.Name, D.City
FROM EMP E, DEPT D
WHERE E.DeptNo = D.DeptNo;

Without the WHERE condition, the result is a Cartesian product (every row with every row): rows = rows₁ × rows₂.
With the equality condition, it is an equi-join.
NATURAL JOIN joins on columns with the same name and shows the common column only once.

A reliable routine for function and output questions

For text functions, number the characters from 1 and count carefully (spaces count).
For ROUND, check the sign of the second argument: positive rounds decimals, negative rounds tens, hundreds and so on.
For dates, use 'YYYY-MM-DD' and remember MONTHNAME and DAYNAME return names.
For grouped queries, apply WHERE, then GROUP BY, then HAVING, then ORDER BY.
Remember that aggregates skip NULL values.

Connections that matter

Computer Science: relational databases and SQL covers the basic SQL commands used with these functions.
Data handling with Pandas: groupby(), string methods and date handling in Pandas mirror these SQL functions.
Data communication project often stores and queries data in MySQL.

Common mistakes

Counting text positions from 0. MySQL starts at 1.
Thinking `ROUND(15.193, -1)` is 15.2. A negative place rounds to tens: 20.
Using `WHERE SUM(...) > ...`. Aggregate conditions go in HAVING.
Expecting `LENGTH` to ignore spaces.
Returning 'Aug' for MONTHNAME. It returns the full name, August.

Revision checklist

I can predict the results of POWER, ROUND and MOD.
I can predict the results of every text function.
I can use the date functions with 'YYYY-MM-DD' dates.
I can write queries with aggregate functions, GROUP BY, HAVING and ORDER BY.
I can join two tables on a common column.