SQL functions are predefined operations in SQL that help perform calculations, manipulate data, or extract information from tables. They make queries powerful, efficient, and concise.
Why Use SQL Functions?
- To perform calculations on data
- To manipulate text or dates easily
- To summarize data using aggregates
- To simplify complex queries
Types of SQL Functions
1. Aggregate Functions – Operate on multiple rows, return single value
o Examples: COUNT(), SUM(), AVG(), MIN(), MAX()
2. Scalar Functions – Operate on single values, return one value per row
o Examples: UPPER(), LOWER(), ROUND(), LENGTH()
3. String Functions – Manipulate text data
o Examples: CONCAT(), SUBSTRING(), REPLACE()
4. Date & Time Functions – Work with dates and timestamps
o Examples: NOW(), CURDATE(), DATEDIFF(), DATE_ADD()
Example
SELECT UPPER(name) AS uppercase_name, COUNT(*) AS total_employees
FROM employees;
- UPPER(name) → converts names to uppercase
- COUNT(*) → counts total rows in employees
Key Points
- Functions can be used in SELECT, WHERE, ORDER BY clauses
- Aggregate functions are often combined with GROUP BY
- Scalar functions help in data formatting and manipulation
