Topic 30 of 52 · Beginner
Functions Module: Roadmap & How Functions Work
What a function is, the categories of SQL commands and functions, and the order to learn them.
Concept
SQL has two kinds of building blocks. Commands (statements) such as CREATE, INSERT, GRANT and COMMIT tell the database what to do. Functions such as UPPER(), ROUND() or NOW() take values and return a value you can use inside a statement.
Commands are grouped into DDL (define structure), DML (change data), DQL (read data), DCL (permissions) and TCL (transactions). Functions are grouped into single-row (scalar) functions that return one value per row, aggregate functions that return one value per group, and window functions that return one value per row while looking at other rows.
Learn in this order: commands (DDL → DML → DCL/TCL), then string, numeric and date functions, then NULL/conditional and conversion functions, then aggregates, then window and JSON functions, and finally system/info and security helpers.
Every lesson in this module has a table of functions with an example and result, a runnable example, points to remember and interview questions.
Cheat sheet
| Category | Examples | Returns | Lesson |
|---|---|---|---|
| DDL commands | CREATE, ALTER, DROP, TRUNCATE, RENAME | Changes structure | DDL Commands Reference |
| DML / DQL commands | INSERT, UPDATE, DELETE, SELECT, REPLACE | Changes / reads rows | DML & DQL Commands Reference |
| DCL / TCL commands | GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT | Permissions / transactions | DCL & TCL Commands Reference |
| String functions | CONCAT, SUBSTRING, TRIM, REPLACE | One value per row | String & Numeric |
| Numeric functions | ROUND, CEIL, MOD, POWER | One value per row | String & Numeric |
| Date & time functions | NOW, DATE_ADD, DATEDIFF, DATE_FORMAT | One value per row | Date, NULL & Aggregate + Date & Time Deep Dive |
| NULL & conditional | COALESCE, IFNULL, NULLIF, IF, CASE | One value per row | Conditional, Comparison & Conversion |
| Conversion | CAST, CONVERT, STR_TO_DATE | One value per row | Conditional, Comparison & Conversion |
| Aggregate | COUNT, SUM, AVG, GROUP_CONCAT | One value per group | Aggregate Functions Deep Dive |
| Window | ROW_NUMBER, RANK, LAG, LEAD | One value per row | Advanced, Window & JSON |
| JSON | JSON_EXTRACT, JSON_TABLE | One value per row / rows | Advanced, Window & JSON |
| System & security | USER(), DATABASE(), UUID(), SHA2() | One value | System, Info & Security |
Code (SQL · MySQL 8)
-- Commands vs functions in one script
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), joined DATE, marks DECIMAL(5,2)); -- DDL
INSERT INTO students VALUES (1, ' ravi kumar ', '2025-07-01', 78.456); -- DML
SELECT
UPPER(TRIM(name)) AS clean_name, -- scalar (string)
ROUND(marks, 1) AS marks_1dp, -- scalar (numeric)
DATEDIFF(CURDATE(), joined) AS days_enrolled -- scalar (date)
FROM students; -- DQL
SELECT COUNT(*) AS total, AVG(marks) AS avg_marks FROM students; -- aggregateRemember
- Commands act on the database; functions compute a value.
- Scalar = one result per row, aggregate = one per group, window = one per row using a group of rows.
- Functions can be nested: UPPER(TRIM(name)) runs TRIM first.
- Most functions return NULL when an input is NULL (CONCAT, arithmetic) — COALESCE and CONCAT_WS are exceptions.
Interview questions
Difference between a scalar and an aggregate function?
A scalar function returns one value for each row (UPPER, ROUND); an aggregate collapses many rows into one value per group (SUM, COUNT).
Name the five SQL command groups.
DDL (CREATE/ALTER/DROP), DML (INSERT/UPDATE/DELETE), DQL (SELECT), DCL (GRANT/REVOKE) and TCL (COMMIT/ROLLBACK/SAVEPOINT).
Why avoid functions on an indexed column in WHERE?
WHERE YEAR(created_at) = 2026 hides the column from the index, forcing a full scan; rewrite as a range: created_at >= '2026-01-01' AND created_at < '2027-01-01'.
Lesson test
Take an 8-question test on “Functions Module: Roadmap & How Functions Work” — concepts, output prediction, bug spotting and interview questions.