SQL function:




A function is known as a named operation that is used to perform tasks that are predefined. Functions are a type of routines that take parameters as input and return calculated values that are based on these parameters. The parameters of a function are enclosed in parentheses. When a function is executed it returns one data value only. Therefore, the function returns a single value for each row that is processed by the query. You must have seen some functions like AVG for average and SUM for sum of the inputs. These functions are called set functions. The set functions are also known as aggregate function.




The SQL functions can be divided into two sub categories:

  1. Aggregate function
  2. Scalar function

Aggregate functions

The aggregate functions are used to process data values and return the appropriate values. The aggregate function sometimes is also called set function that includes the average of numbers or sum of numbers etc. The requirement to use the aggregate functions is that the data should be grouped in some way. The following are some of the aggregate functions:

 

AVG () function

The average function is used to calculate the average of values in a specified column. The syntax of the average function is given below:

SELECT AVG (column-name) from table-name;

This query will calculate the average of the values in the column that the user will specify from a specific table.

 

EXAMPLE using AVG () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to find the average of the marks obtained by students in different subjects then you will use the following query:

SELECT AVG (Marks) from student;

This query will calculate the average of the marks of students and will generate the following result:

AVG (Marks)
76.125

Now let suppose that you want to find the average of the monthly fee of all the students then you will use the following query:

SELECT AVG (MONFEE) from student;

This query will calculate the average of the monthly fee of students and will generate the following result:

AVG (MONFEE)
1075

 

Count ()function

The count function is used to count the number of rows that exist in a table or the number of values that are stored in a column. If you want to count the number of null values in a column then you must use the column name. but if you want to count the number of rows in a table then you should use an asterisk with the count function. You can also count the number of rows in a table by using a condition. The syntax of using the average function is as follows:

SELECT count (column-name) from table-name;

 

EXAMPLE using the count () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Now use the following query to count the number of students that have marks greater than 70.

SELECT count (NAME) from STUDENT where Marks > 70;

In this query we used the count function with a condition that if the marks of the student is greater than 70 than display the names of those students. This query will generate the following result:

COUNT (NAME)
5

 

EXAMPLE of count (distinct)

The count (distinct) function is used to count the distinct or different or unique values from a column.

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Use the following query to count the unique values from the column DEPT:

SELECT count (distinct DEPT) from STUDENT;

This query will generate the following result:

COUNT (distinct DEPT)
4

It can be seen that there are 4 different or unique values in the column DEPT of the table student.

 

First () function

The first () function is used to return the very first value of a specific or selected column. The syntax of the first function is as follows:

SELECT first (column-name) from table-name;

The column-name will be the column which is to be selected to count its first value from the specific table.

 

EXAMPLE of first () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to extract the first value of the column NAME, then you will use the following query:

SELECT first (NAME) from student;

This query will select the column name of the table named as student and will return the first entry or value of the selected column. The query will produce the following result:

FIRST (DEPT)
Marketing

 

Last () function

The last () function is used to return the last value of a specific or selected column. The syntax of the last function is as follows:

SELECT last (column-name) from table-name;

The column-name will be the column which is to be selected to count its lasst value from the specific table.

 

EXAMPLE of last () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to extract the last value of the column NAME, then you will use the following query:

SELECT last (NAME) from student;

This query will select the column name of the table named as student and will return the last entry or value of the selected column. The query will produce the following result:

FIRST (DEPT)
Marketing

 

MAX () function

The max () function is used to return the maximum or the highest value from a certain specified or selected column of the table. The syntax of the MAX () function is given below:

SELECT max (column-name) from table-name;

This will select the column “column-name” from the table “table-name” and will return the highest value from that column.

 

EXAMPLE of MAX () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to find the highest value in the column Marks or you want to find the maximum marks obtained by a student, then you will use the following query:

SELECT MAX (Marks) from student;

This query will select the column marks of the table named as student and will return the highest value of the selected column. The query will produce the following result:

MAX (Marks)
94

 

MIN () function

The min () function is used to return the minimum or the lowest value from a certain specified or selected column of the table. The syntax of the MIN () function is given below:

SELECT min (column-name) from table-name;

This will select the column “column-name” from the table “table-name” and will return the lowest value from that column.

EXAMPLE of Min () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to find the lowest value in the column Marks or you want to find the minimum marks obtained by a student, then you will use the following query:

SELECT MIN (Marks) from student;

This query will select the column marks of the table named as student and will return the highest value of the selected column. The query will produce the following result:

MIN (Marks)
56




SUM () function

The sum function is used to return the sum of the total values that are stored in particular column. The values should be numeric values to get the sum. The syntax of the SUM () function is given below:

SELECT sum (column-name) from table-name;

EXAMPLE of SUM () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to find the sum of the monthly fee of the students then you will use the following query:

SELECT SUM (MONFEE) from student;

This query will select the column MONFEE of the table named as student and will return the sum of the values of that column. The query will produce the following result:

SUM (MONFEE)
8600

 

SCALAR function

The scalar functions are used to return a single value that is based on the values of the input. Several frequently used scalar functions are explained below:

 

UCASE () function

As the name tells that the UCASE function converts a field or a string column to uppercase character. The syntax of the UCASE function is given below:

SELECT UCASE (column-name) from table-name;

This will get the column from a table and will convert it to uppercase characters.

EXAMPLE of UCASE () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to convert all the entries in the column NAME to uppercase letters then you will use the following query:

SELECT UCASE (NAME) from student;

This query will convert the whole column NAME to uppercase characters. The result generated by this query will be as follows:

UCASE (NAME)
TIM
FADDY
JONES
JOHN
JAMES
ABRAHAM
LOUIS
STUART

 

LCASE () function

As the name tells that the LCASE function converts a field or a string column to lowercase characters. The syntax of the LCASE function is given below:

SELECT LCASE (column-name) from table-name;

This will get the column from a table and will convert it to lowercase characters.

 

EXAMPLE of UCASE () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to convert all the entries in the column NAME to lowercase letters then you will use the following query:

SELECT LCASE (NAME) from student;

This query will convert the whole column NAME to lowercase characters. The result generated by this query will be as follows:

LCASE (NAME)
tim
faddy
jones
john
james
abraham
louis
stuart

 

MID () function

The mid () function is used to extract a substring from an identified character string in a column of string values of a table. The extracted string is a defined number of characters. The length and the location from where the substring will be extracted are defined by the user in the query. The syntax of MIN () function is as follows:

SELECT MID (column-name, start, length) from table-name;

This will extract the substring from “column-name” starting at the point “start” and having a specified length from the table “table-name”.

 

EXAMPLE of MID () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to extract the substring from the column DEPT of the student table. Suppose the length of the substring is 3 and it is starting from the point 2. Then the query for this will be as follows:

SELECT MID (DEPT, 2, 3) from student;

The following result will be produced by the above query:

MID (DEPT, 2, 3)
ark
ark
cco
nfo
nfo
ina
ark
ark

 

ROUND () function

The round () function rounds off a numeric value to a number that will be nearest to an integer. The round () can only be defined on decimal point numbers or the numbers having fractional part. The syntax of the round () function is given below:

SELECT round (column-name, decimals) from table-name

This will round off the decimal value in the column “column-name” of the table “table-name”.

 

EXAMPLE of ROUND () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56.6
100 Faddy Marketing 1000 230 Math 78.5
140 Jones Accounting 1200 144 Computer 89.25
110 John Info. System 1100 230 Math 84.75
110 James Info. System 1100 204 English 68.5
190 Abraham Finance 1200 144 Computer 94.43
150 Louis Marketing 1000 204 English 64.75
150 Stuart Marketing 1000 144 Computer 76.25

Suppose you want to round off the marks of the student to the nearest integer then you will use the following query:

SELECT round (Marks) from student;

This query will generate the following result:

Round (Marks)
57
79
89
85
69
94
65
76

It can be seen that the numbers are rounded to the nearest integer that is 56.6 to 57 and so on.




Len () function

The LEN () function is used to find the length of a string in a specific column of a table. The syntax of the LEN () function is as follows:

SELECT LEN (column-name) from table-name where condition;

EXAMPLE of LEN () function

Suppose you have the following table that is named as STUDENT:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 204 English 68
190 Abraham Finance 1200 144 Computer 94
150 Louis Marketing 1000 204 English 64
150 Stuart Marketing 1000 144 Computer 76

Suppose you want to find the length of a string in the column DEPT of the table student. Then you will use the following query:

Select LEN (DEPT) from student where DEPT = ‘Accounting’;

This query will generate the following result:

LEN (DEPT)

10