MySQL ships with a large library of built-in functions that operate on values while a query is being executed. These functions allow a single SELECT statement to compute, transform, and format data without any additional programming. For the Class 12 examination the syllabus groups them into four families: mathematical (numeric) functions, string functions, date and time functions, and aggregate functions. Understanding what each function does, and being able to predict the output of expressions that combine them, is a reliable source of marks in both theory and practicals.
Functions are used inside a SELECT clause, a WHERE clause, or even inside other functions. Most functions accept one or more arguments and return a single value per call. Single-row functions transform each value independently, so they can be applied column-wise to produce one result per row. Aggregate functions, by contrast, collapse many rows into one summary value. Mixing the two families carelessly, such as combining a single-row column with an aggregate without grouping, is a classic source of errors.
This chapter covers each function family in turn. For every function we give its exact syntax, a working example, and the expected output, because output-prediction questions are the most common way these functions are tested. The aggregate functions were introduced in the queries chapter; here they are summarised and extended so that the full toolkit is available in one place.
Numeric functions accept numeric values and return numeric results.
SELECT ROUND(15.678, 2);
SELECT TRUNCATE(15.678, 2);
SELECT POWER(2, 3);
SELECT SQRT(81);
SELECT MOD(17, 5);
SELECT ABS(-45);
SELECT CEIL(4.2), FLOOR(4.8);
SELECT GREATEST(10, 25, 18), LEAST(10, 25, 18);
ROUND(x, d): rounds x to d decimal places. ROUND(15.678, 2) returns 15.68.TRUNCATE(x, d): removes digits beyond d decimal places without rounding. TRUNCATE(15.678, 2) returns 15.67.POWER(a, b): raises a to the power b. POWER(2, 3) returns 8.SQRT(x): gives the square root. SQRT(81) returns 9.MOD(a, b): returns the remainder when a is divided by b. MOD(17, 5) returns 2.ABS(x): gives the absolute value. ABS(-45) returns 45.CEIL(x): rounds up to the nearest whole number. CEIL(4.2) returns 5.FLOOR(x): rounds down to the nearest whole number. FLOOR(4.8) returns 4.GREATEST(v1, v2, ...): returns the largest of the listed values.LEAST(v1, v2, ...): returns the smallest of the listed values.String functions manipulate character data.
SELECT UCASE('hello'), UPPER('hello');
SELECT LCASE('HELLO'), LOWER('HELLO');
UCASE (also UPPER) converts a string to uppercase, and LCASE (also LOWER) converts it to lowercase. UCASE('hello') returns HELLO.
SELECT LENGTH('hello');
SELECT MID('Informatic', 3, 4);
SELECT SUBSTR('Informatic', 3, 4);
SELECT LEFT('Informatic', 3);
SELECT RIGHT('Informatic', 3);
LENGTH(str): returns the number of characters. LENGTH('hello') returns 5.MID(str, pos, len) or SUBSTR(str, pos, len): extracts len characters starting at position pos (1-based). MID('Informatic', 3, 4) returns form.LEFT(str, n): returns the first n characters. LEFT('Informatic', 3) returns Inf.RIGHT(str, n): returns the last n characters. RIGHT('Informatic', 3) returns tic.SELECT CONCAT('Info', 'matics');
SELECT INSTR('Informatic', 'mat');
CONCAT(s1, s2, ...): joins the strings together. CONCAT('Info', 'matics') returns Informatics.INSTR(str, substr): returns the position where substr first appears in str. INSTR('Informatic', 'mat') returns 7.SELECT TRIM(' hello ');
SELECT LTRIM(' hello');
SELECT RTRIM('hello ');
SELECT LPAD('123', 5, '0');
SELECT RPAD('123', 5, '*');
TRIM(str): removes spaces from both ends.LTRIM(str): removes leading spaces.RTRIM(str): removes trailing spaces.LPAD(str, n, pad): pads the left side with pad characters to reach total length n. LPAD('123', 5, '0') returns 00123.RPAD(str, n, pad): pads the right side similarly.Date functions work on DATE and DATETIME values.
SELECT NOW();
SELECT CURDATE();
SELECT DATE('2024-08-15 10:30:00');
SELECT YEAR('2024-08-15');
SELECT MONTH('2024-08-15');
SELECT DAY('2024-08-15');
SELECT DAYNAME('2024-08-15');
SELECT MONTHNAME('2024-08-15');
SELECT DATEDIFF('2024-08-20', '2024-08-15');
NOW(): returns the current date and time.CURDATE(): returns the current date only.DATE(expr): extracts the date part from a datetime value.YEAR(date): extracts the year. YEAR('2024-08-15') returns 2024.MONTH(date): extracts the month number, so MONTH('2024-08-15') returns 8.DAY(date): extracts the day of the month, so DAY('2024-08-15') returns 15.DAYNAME(date): returns the name of the weekday, so DAYNAME('2024-08-15') returns Thursday.MONTHNAME(date): returns the month name, so MONTHNAME('2024-08-15') returns August.DATEDIFF(d1, d2): returns the number of days between two dates, calculated as d1 minus d2. DATEDIFF('2024-08-20', '2024-08-15') returns 5.Aggregate functions summarise an entire column into one value.
SELECT COUNT(*) FROM student;
SELECT SUM(marks), AVG(marks) FROM student;
SELECT MAX(marks), MIN(marks) FROM student;
COUNT(*): number of rows; COUNT(col): number of non-NULL values in col.SUM(col): total of the column values.AVG(col): arithmetic mean of the non-NULL values.MAX(col) and MIN(col): largest and smallest values in the column.COUNT(*) ignore NULL values.Aggregates combine naturally with GROUP BY:
SELECT city, AVG(marks) FROM student GROUP BY city;
SELECT city, COUNT(*) FROM student GROUP BY city HAVING COUNT(*) > 2;
Functions can be nested inside one another, and the inner function is evaluated first:
SELECT LENGTH(UCASE('hello'));
SELECT ROUND(SQRT(50), 1);
SELECT DAY(LAST_DAY('2024-02-10'));
LENGTH(UCASE('hello')) first converts 'hello' to 'HELLO' and then counts 5 characters, returning 5. Nested expressions of this type are a favourite in output-prediction questions.
| Function | Example | Result |
|---|---|---|
ROUND(15.678, 2) |
Round to 2 places | 15.68 |
TRUNCATE(15.678, 2) |
Cut to 2 places | 15.67 |
MOD(17, 5) |
Remainder | 2 |
POWER(2, 3) |
2 raised to 3 | 8 |
UCASE('abc') |
Uppercase | ABC |
LCASE('ABC') |
Lowercase | abc |
MID('Informatic', 3, 4) |
Substring | form |
CONCAT('Info', 'matics') |
Join strings | Informatics |
INSTR('Informatic', 'mat') |
First position | 7 |
| Function | Example | Result |
|---|---|---|
YEAR('2024-08-15') |
Year number | 2024 |
MONTH('2024-08-15') |
Month number | 8 |
DAY('2024-08-15') |
Day of month | 15 |
DAYNAME('2024-08-15') |
Weekday name | Thursday |
DATEDIFF(d1, d2) |
Days between | d1 minus d2 |
COUNT(*) |
Row count | Total rows |
SUM(col) |
Total | Sum of values |
AVG(col) |
Mean | Average value |
MAX(col) / MIN(col) |
Extremes | Largest / smallest |
TRUNCATE when rounding is intended: TRUNCATE(15.678, 2) gives 15.67 while ROUND(15.678, 2) gives 15.68.MID with LEFT; MID starts at a given position, while LEFT always starts at the first character.MID('abc', 1, 2) returns 'ab' not 'bc'.LENGTH on numeric data and forgetting that it first converts the number to a string.MONTH where the month name is wanted; MONTH(date) gives a number and MONTHNAME(date) gives the name.DATEDIFF(d1, d2) to be symmetric; it returns d1 minus d2 and can be negative if d1 is earlier.CONCAT('Info', 'matics') with a + instead of a comma, since MySQL does not use + to join strings.MOD(a, b) and a % b are equivalent in MySQL.YEAR, MONTH, and DAY take a date as argument and return a number.MySQL functions give a single SELECT statement the ability to compute, transform, format, and summarise data. Numeric functions such as ROUND, TRUNCATE, MOD, POWER, and SQRT handle mathematical work; string functions like UCASE, LCASE, MID, CONCAT, INSTR, and the padding family manipulate text; and the date functions YEAR, MONTH, DAY, DAYNAME, MONTHNAME, and DATEDIFF interpret and compare dates. Aggregate functions, including COUNT, SUM, AVG, MAX, and MIN, collapse whole columns into summary values and work with GROUP BY to produce per-group statistics. Because functions can be nested and combined freely, they make MySQL a complete data-processing language, and they are examined heavily through output-prediction and query-writing questions throughout the Class 12 curriculum.