Hi Readers,

In this blog, we will discuss the inbuilt functions of SQL which are used to perform calculations on data. All inbuilt functions are provided to make the task easier to do and we can easily complete a complex task with the help of these functions. There are two types of functions which we can use in SQL to perform calculations which are as follows:-

1. SQL Aggregate Functions
2. SQL Scalar Functions

 SQL Aggregate Functions:-

Now one by one we will discuss both types of functions first of all SQL aggregate functions which returns a single value from multiple values are as follows:-

    •    AVG()
    •    COUNT()
    •    FIRST()
    •    LAST()
    •    MAX()
    •    MIN()
    •    SUM()

We will start with the first function of aggregate functions that is AVG() :-

AVG() Function:-
The AVG() function returns the average value of the column includes all integer values. As in mathematics also if we need to calculate average speed so we need distance and time in integer value. Syntax of the function will be used in this way as given below:-

SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name

For Example:-

SELECT AVG(Price) AS PriceAverage FROM ProductTable;

This will calculate the average of price from Price column in table ProductTable and return the average.

COUNT() Function:-
This  function is used to returns the number of rows that matches a specified condition.

SQL COUNT() Syntax
SELECT COUNT(column_name) FROM table_name;

According to syntax, this function will return the number of values (NULL values will not be counted) of the specified column in the syntax.

For Example:-
SELECT COUNT(*) FROM table_name;

According to example, this function returns the number of records in a table if used in the above way.

FIRST() Function:-
This function is used to returns the first value of the selected column from the table.

SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name;

Note: The FIRST() function is only supported in MS Access so for SQL SERVER we will use TOP instead of FIRST().

SQL Server Syntax
SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC;
Note:ASC is for ascending order.
 

For Example:-
SELECT TOP 1 Employee_Name FROM Employee ORDER BY Employee_Id ASC;

LAST() Function:-
This function returns the last value of the selected column from the table.

SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name;

Note: The LAST() function is only supported in MS Access.

SQL Server Syntax
SELECT TOP 1 column_name FROM table_name ORDER BY column_name DESC;
Note:DESC is for descending order.

For Example:-
SELECT TOP 1 Employee_Name FROM Employee ORDER BY Employee_Id DESC;

MAX() Function

This function returns the largest value of the selected column from the table.
 

SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name;

For Example:-
SELECT MAX(Price) AS HighestPrice FROM ProductTable;

MIN() Function:-
This function returns the smallest value of the selected column from the table.
 

SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name;

For Example:-
SELECT MIN(Price) AS SmallestOrderPrice FROM ProductTable;

SUM() Function:-

This  function returns the total sum of a numeric column from the table.

SQL SUM() Syntax
SELECT SUM(column_name) FROM table_name;

For Example:-
SELECT SUM(Price) AS TotalSum FROM ProductTable;

SQL Scalar Functions:-

This is the second type of the inbuilt functions of SQL. This includes the following functions in it :-

    •    UCASE()
    •    LCASE()
    •    LEN()
    •    ROUND()
    •    NOW()
    •    FORMAT()

Now one by one we will discuss the above-mentioned functions:-

UCASE() Function:-
This function converts the value of a column to uppercase from the table.

SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name;
Note:-We will use UPPER instead of UCASE in SQL.

Syntax for SQL Server
SELECT UPPER(column_name) FROM table_name;

For Example:-
SELECT UPPER(Employee_Name) AS EmployeeNameUpperCase FROM Employee;

LCASE() Function:-
This function converts the value of a column to lowercase from the table.

SQL LCASE() Syntax
SELECT LCASE(column_name) FROM table_name;

Syntax for SQL Server
SELECT LOWER(column_name) FROM table_name;

For Example:-
SELECT LCASE(Employee_Name) AS EmployeeNameLowerCase FROM Employee;

LEN() Function:-

This function returns the length of the value from the given column.

SQL LEN() Syntax
SELECT LEN(column_name) FROM table_name;

For Example:-
SELECT LEN(Employee_Address) as LengthOfAddress FROM Employee;

ROUND() Function:-
This function is used to round a numeric field to the number of decimals specified in the syntax.

SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM table_name;

For Example:-
SELECT ROUND(Price,0) AS RoundedPrice FROM ProductTable;

The NOW() Function:-
This function returns the current system date and time.

SQL NOW() Syntax
SELECT NOW() FROM table_name;

For Example:-
SELECT  Now() AS PerDate FROM ProductTable;

FORMAT() Function
This function is used to format how a field is to be displayed and format is provided with the help of FORMAT() function.

SQL FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name;

For Example:-
SELECT  FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM ProductTable;

In this way we can easily use the inbuilt functions of SQL Server and complete complex task in very less time.