SQL provides many built-in functions to perform calculations on data. Operations such as mathematical calculations, string manipulation like concatenation, substring are performed using these SQL Functions.
SQL Functions broadly can be divided into the following two categories.
- Aggregate Functions
- Scalar Functions
SQL Aggregate Functions
SQL Aggregate Functions return a single value after performing calculations on the multiple values. Following are the some of the most commonly used SQL Aggregate functions.
- AVG() – It returns the average value.
- COUNT() – It returns the number of rows.
- FIRST() – It returns the first value.
- LAST() – It returns the last value.
- MAX() – It returns the largest value.
- MIN() – It returns the smallest value.
- SUM() – It returns the sum.
SQL Scalar Functions
SQL Scalar Functions returns a single value based on the input provided. Here, the input also a single value. Following are the some of the most commonly used SQL Scalar Functions.
- UCASE() – It converts a field to upper case.
- LCASE() – It converts a field to lower case.
- MID() – It extract characters from a text field.
- LEN() – It returns the length of a text field.
- ROUND() – It rounds a numeric field to the number of decimals specified.
- NOW() – It returns the current system date and time.
- FORMAT() – It formats how a field is to be displayed.