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.
- Aggregate Functions:-
These functions perform calculations on multiple rows and return a single value.
- COUNT():- Returns the number of rows that match a condition.
Syntax:-
SELECT COUNT(*) FROM table_name;
Example:
SELECT COUNT(*) FROM students;
→ Returns the total number of students in the table.
- SUM():- Adds up the values in a numeric column.
Syntax:-
SELECT SUM(numeric_column) FROM table_name;
Example:
SELECT SUM(marks) FROM students;
→ Returns the total sum of all students’ marks.
- AVG():- Returns the average (mean) value of a numeric column.
Syntax:-
SELECT AVG(numeric_column) FROM table_name;
Example:
SELECT AVG(age) FROM students;
→ Calculates the average age of all students.
- MIN():- Returns the smallest value in a column.
Syntax:-
SELECT MIN(column_name) FROM table_name;
Example:
SELECT MIN(age) FROM students;
→ Finds the youngest student’s age.
- MAX():- Returns the largest value in a column.
Syntax:-
SELECT MIN(column_name) FROM table_name;
Example:
SELECT MAX(age) FROM students;
→ Finds the oldest student’s age.
- String Functions:-
These functions help in manipulating or formatting text values.
- CONCAT():- Combines two or more strings into one.
Syntax:-
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
- LENGTH():- Returns the number of characters in a string.
Syntax:-
SELECT LENGTH(column_name) FROM table_name;
Example:
SELECT LENGTH(name) FROM students;
→ Shows how many characters are in each student’s name.
- UPPER() / LOWER():- Converts text to uppercase or lowercase.
Syntax:-
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.
- SUBSTRING():- Extracts part of a string from a specified position.
Syntax:-
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.
- Date/Time Functions
These functions are used to work with dates and times.
- NOW():- Returns the current date and time.
Syntax:-
SELECT NOW();
Example:
SELECT NOW();
→ Shows the current system date and time.
- CURDATE():- Returns the current date only.
Syntax:-
SELECT CURDATE();
Example:
SELECT CURDATE();
→ Shows today’s date in YYYY-MM-DD format.
- DATEDIFF():- Returns the number of days between two dates.
Syntax:-
SELECT DATEDIFF(date1, date2);
Example:
SELECT DATEDIFF(‘2025-07-10’, ‘2025-07-01’);
→ Returns 9 (the difference between two dates in days)