← SQL roadmap

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

CategoryExamplesReturnsLesson
DDL commandsCREATE, ALTER, DROP, TRUNCATE, RENAMEChanges structureDDL Commands Reference
DML / DQL commandsINSERT, UPDATE, DELETE, SELECT, REPLACEChanges / reads rowsDML & DQL Commands Reference
DCL / TCL commandsGRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINTPermissions / transactionsDCL & TCL Commands Reference
String functionsCONCAT, SUBSTRING, TRIM, REPLACEOne value per rowString & Numeric
Numeric functionsROUND, CEIL, MOD, POWEROne value per rowString & Numeric
Date & time functionsNOW, DATE_ADD, DATEDIFF, DATE_FORMATOne value per rowDate, NULL & Aggregate + Date & Time Deep Dive
NULL & conditionalCOALESCE, IFNULL, NULLIF, IF, CASEOne value per rowConditional, Comparison & Conversion
ConversionCAST, CONVERT, STR_TO_DATEOne value per rowConditional, Comparison & Conversion
AggregateCOUNT, SUM, AVG, GROUP_CONCATOne value per groupAggregate Functions Deep Dive
WindowROW_NUMBER, RANK, LAG, LEADOne value per rowAdvanced, Window & JSON
JSONJSON_EXTRACT, JSON_TABLEOne value per row / rowsAdvanced, Window & JSON
System & securityUSER(), DATABASE(), UUID(), SHA2()One valueSystem, 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;  -- aggregate

Remember

  • 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'.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Module: Roadmap & How Functions Work” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.