SQL Functions List:

In this article i will give you the basic idea about the SQL functions and will explain about the SQL Functions List.There are lot of interview questions on SQL Functions list.In This Article  will only explain 2 types of functions which are most widely used in day to day activities.SQL has many built in functions to perform the calculation on the data.I am explaining the Scalar functions as well as aggregate functions in this article.

“SQL Functions are nothing but the System programs which is written in to backend to perform the calculation of the data or to process the strings,numbers e.t.c.”

Click Here to Get 20 Most Important Complex SQL Queries 

What are SQL Functions List with Examples :

SQL Functions : SQL Aggregate Functions:

SQL aggregate functions are the functions where user can give multiple values as input but function always returns single value as output.

Aggregate Functions are the Functions where the value of input is grouped together and fetches the output as a single value

Following is single line Explanation of the Aggregate functions:

• AVG() – Returns the average value
• COUNT() – Returns the number of rows
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• SUM() – Returns the sum

Click Here to get 20 Most important interview questions for IBM    

SQL Functions  : SQL Scalar functions:

SQL scalar functions are the functions whose input range is one dimensional and which returns the single output for each row.SQL Scalar functions returns value of every row which we are used in query to process.

SQL Scalar Functions are also known as Single Row Functions..

Following is Single line Explanation of some of the useful Scalar Functions:

• UCASE() – Converts a field to upper case
• LCASE() – Converts a field to lower case
• SUBSTR() – Extract characters from a text field
• LEN()/LENGTH() – Returns the length of a text field
• ROUND() – Rounds a numeric field to the number of decimals specified

Following is Consolidated SQL Functions List with examples:

1) The AVG () Function:

The AVG () function returns the average value of a numeric column.

Syntax:

SELECT AVG (Column_Name)

FROM <Table_Name>;

Scenario: How to Find Average salary from Employee Table departmentwise?

Example:

SELECT AVG (Salary)

FROM Employee;

SELECT DeptNo, AVG (Sal) FROM EMP

GROUP BY DeptNo;

SELECT DeptNo, AVG (Sal) AS AvgSal FROM EMP

GROUP BY DeptNo

ORDER BY DeptNo;

SELECT EMPNo, Ename, Job, Sal, DeptNo FROM EMP

WHERE Sal > (SELECT AVG (Sal) FROM EMP);

2) The COUNT () Function:

The COUNT () function returns the number of rows that matches a specified criteria.We Can count the Number of rows using following 3 types:

1) COUNT (Column_Name)

2) COUNT (*)

3) COUNT (DISTINCT Column_Name)

1) Syntax:

SELECT COUNT (Column_Name)

FROM <Table_Name>;

Scenario : How to find the count of names of Employees who are doing job as CLERK?

Example:

SELECT COUNT (Ename) FROM Employee;

SELECT Job, COUNT (Job) FROM Employee

WHERE Job=’CLERK’

GROUP BY Job;

“The COUNT (Column_Name) function returns the number of values (NULL values will not be counted) of the specified column.”

2) Syntax:

SELECT COUNT (*)

FROM <Table_Name>;

Example:

SELECT COUNT (*) FROM Employee;

“The COUNT (*) function returns the total number of records in a table, counts NULL values also”

3) Syntax:

SELECT COUNT (DISTINCT Column_Name)

FROM <Table_Name>;

Example:

SELECT COUNT (DISTINCT Job) FROM EMP;

SELECT COUNT (DISTINCT (Ename)) FROM EMP;

SELECT Ename, COUNT (DISTINCT (Ename)) FROM EMP GROUP BY Ename;

SELECT Job, COUNT (DISTINCT (Job)) FROM EMP GROUP BY Job;

“The COUNT (DISTINCT column_name) function returns the number of distinct values of the specified column.”

Click Here To Get more information on Rank and Dense_Rank Fuctions

3) The MAX () Function:

The MAX () function returns the largest value of the selected column.

Syntax:

SELECT MAX (Column_Name)

FROM <Table_Name>;

 SELECT MAX (Sal) FROM EMP;

 SELECT DeptNo, Max (Sal) FROM EMP

GROUP BY DeptNo

ORDER BY DeptNo;

4) The MIN () Function:

The MIN () function returns the smallest value of the selected column.

Syntax:

SELECT MIN (Column_Name) FROM <Table_Name>;

SELECT MIN (Sal) FROM EMP;

SELECT DeptNo, Min (Sal) FROM EMP

GROUP BY DeptNo

ORDER BY DeptNo;

5) The SUM () Function:

The SUM () function returns the total sum of a numeric column.

Syntax:

SELECT SUM (Column_Name) FROM <Table_Name>;

Example:

SELECT SUM (Sal) FROM EMP;

SELECT DeptNo, Sum (Sal) FROM EMP

GROUP BY DeptNo

ORDER BY DeptNo;

6) The UPPER () Function:

The UPPER () function converts the value of a field to Upper-Case.

Syntax:

SELECT UPPER (Column_Name) FROM <Table_Name>;

Example :

SELECT UPPER (Ename) FROM EMP;

7) The LOWER () Function:

The LOWER () function converts the value of a field to Lower-Case.

Syntax:

SELECT LOWER (Column_Name) FROM <Table_Name>;

Example:

SELECT LOWER (Ename) FROM EMP;

8) The INITCAP () Function:

The INITCAP () function converts the value of a field to Initial-Case.

Syntax:

SELECT INITCAP (Column_Name) FROM <Table_Name>;

SELECT INITCAP (Ename) FROM EMP;

9) The SUBSTR () Function:

The SUBSTR () function is used to extract characters from a text field.

Syntax:

SELECT SUBSTR (Column_Name, Start Position, Length)

FROM <Table_Name>;

Here is the description of parameters of Substr function.Substring function is widely used functions in SQL and PLSQL development.

Parameter	Description
column_name	Required. The field to extract characters from
Start	Required. Specifies the starting position (starts at 1)
Length	Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text

Example:

SELECT Substr (Ename, 1, 3) FROM EMP;

10) The LENGTH () Function:

The LENGTH () function returns the length of the value in a text field.

Syntax:

SELECT LENGTH (Column_Name)

FROM <Table_Name>;

SELECT LENGTH (Ename) FROM EMP;

11) The ROUND () Function:

The ROUND () function is used to round a numeric field to the number of decimals specified.

Syntax:

SELECT ROUND (Column_Name, Decimals)

FROM EMP;

Following are Parameter and its description for Round Function:

Parameter	Description
column_name	Required. The field to round.
Decimals	Required. Specifies the number of decimals to be returned.

Example:

SELECT ROUND (Comm, 2) FROM EMP;

Hope everyone likes the article on SQL Functions List.If you like this article or want any notes of this article kindly comment in comment section.This SQL Functions list will give you the latest and most used sql functions with real examples.

HOME