SQL functions are built-in operations used to perform calculations, modify values, and analyze data in a query. They simplify complex tasks like counting records, formatting text, and working with dates.
A. Aggregate Functions
Aggregate functions in SQL are used to perform calculations on multiple rows of data and return a single summarized result. They help in analyzing large datasets by providing totals, averages, counts, and minimum or maximum values. These functions are widely used in reporting and analytics to extract meaningful insights.
1. COUNT() : Returns the number of rows that match a condition.
SELECT COUNT(*) FROM table_name;Example:
SELECT COUNT(*) FROM students;Returns the total number of students in the table.
2. SUM() : Adds up the values in a numeric column.
SELECT SUM(numeric_column) FROM table_name;Example:
SELECT SUM(marks) FROM students;Returns the total sum of all students’ marks.
3. AVG() : Returns the average (mean) value of a numeric column.
SELECT AVG(numeric_column) FROM table_name;Example:
SELECT AVG(age) FROM students;Calculates the average age of all students.
4. MIN() : Returns the smallest value in a column.
SELECT MIN(column_name) FROM table_name;Example:
SELECT MIN(age) FROM students;Finds the youngest student’s age.
5. MAX() : Returns the largest value in a column.
SELECT MAX(column_name) FROM table_name;Example:
SELECT MAX(age) FROM students;Finds the oldest student’s age.
B. String Functions
String functions in SQL are used to manipulate and format text-based data stored in tables. They allow operations like combining strings, measuring length, changing case, or extracting specific parts of text. These functions are very useful when working with names, addresses, and other textual fields. By applying string functions, queries can produce cleaner and more readable outputs.
6. CONCAT() : Combines two or more strings into one.
SELECT CONCAT(column1, ' ', column2) AS alias_name
FROM table_name;Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;Displays full names by combining first and last name.
7. LENGTH() : Returns the number of characters in a string.
SELECT LENGTH(column_name) FROM table_name;Example:
SELECT LENGTH(name) FROM students;Shows how many characters are in each student’s name.
8. UPPER() / LOWER() : Converts text to uppercase or lowercase.
SELECT UPPER(column_name) FROM table_name;
-- OR
SELECT LOWER(column_name) FROM table_name;Example:
SELECT UPPER(name) FROM students;Returns all student names in uppercase.
9. SUBSTRING() : Extracts part of a string from a specified position.
SELECT SUBSTRING(column_name, start_position, length)
FROM table_name;Example:
SELECT SUBSTRING(name, 1, 3) FROM students;Returns the first 3 letters of each student’s name.
C. Date/Time Functions
Date/Time functions in SQL are designed to work with date and time values stored in tables. They help retrieve the current date, calculate differences between two dates, or format timestamps for reporting. These functions are essential for tasks like tracking orders, scheduling events, or generating time-based reports.
10. NOW() : Returns the current date and time.
SELECT NOW();Example:
SELECT NOW();Shows the current system date and time.
11. CURDATE() : Returns the current date only.
SELECT CURDATE();Example:
SELECT CURDATE();Shows today’s date in YYYY-MM-DD format.
12. DATEDIFF() : Returns the number of days between two dates.
SELECT DATEDIFF(date1, date2);Example:
SELECT DATEDIFF('2025-07-10', '2025-07-01');Returns 9 (the difference between two dates in days).