Home > DeveloperSection > Blogs > SQL Functions

SQL Functions


MYSQL SQL Server  SQL 
Ratings:
0 Comment(s)
 180  View(s)
Rate this:

SQL Functions     


SQL functions are of two types:

Aggregate functions

These functions return a single value, calculated from the values in the column:

·         AVG() : Returns a average value in the column.

·         FIRST(): Returns the first value in the column.

·         SUM(): Returns the sum in the column.

·         MAX(): Returns the largest value in the column.

·         LAST(): Returns the last value in the column.

·         COUNT():Returns the number of rows.

·         MIN(): Returns the minimum value in the column.

Scalar functions

Scalar functions return a single value, based on input data. Following are the various types of scalar functions:

·         UCASE(): Converts the column into upper case.

·         NOW(): Returns the current date and time of the system.

·         LEN(): Returns the length of the field.

·         LCASE(): converts the column into lower case.

·         MID(): Extract data from a text field.

·         ROUND(): Rounds a numeric field to the number of decimal specified.

·         FORMAT(): Formats how a column is to be displayed.

NOTE: First create a database of student name and then create a table. After creating table insert the values in the table. Following is an example which will be used by SQL functions:

Customer Id

Name

Address

Contact   Marks

12ST09

John

USA

456788        54

12ST10

Royce

Argentina

457912        34

12ST11

Mickel

London

56276          45

12ST12

Tom

New york

2898938      43

12ST13

Hugh

Luanda

2974789      34

12ST14

Nolan

Paris

232688        56

12ST15

Jennifer

USA

43762762    67

 

UCASE()

UCASE () function takes a character as a parameter and returns a string in which all characters have been converted into uppercase.

SQL UCASE() syntax:

Select UCASE(Column name) from table name;

 

Please note: If the character type char then it will return char, otherwise it will return varchar. Following is an example of UCASE() function:

JOHN

ROYCE

MICKEL

TOM

HUGH

NOLAN

JENNIFER

 

LCASE()

LCASE () function takes a character as a parameter and returns a string in which all characters have been converted into lowercase.

SQL LCASE() syntax:

Select LCASE(Column name) from table name;

 

Please note: If the character type char then it will return char, otherwise it will return varchar. Following is an example of LCASE() function:

Name

john

royce

mickel

tom

hugh

nolan

jennifer

 

MID()

The MID() function is used to extract characters from text column. Following is a syntax of MID() function:

SELECT MID(column name, start, length) FROM table name;

 

Please note: column name and start is mandatory where as length can be option. If length parameter is omitted, then MID() function will return rest of the text. Following is an example of MID() function:

John

Royc

Mick

Tom

Hugh

Nola

 

Note: If length parameter is omitted, then MID() function will return rest of the text.

LEN()

The LEN() function returns the length of the column field. Following is the syntax of LEN() function:

SELECT LEN(column name) FROM table name;

 It will return the length of the column field.

ROUND()

The ROUND() is used to round the number decimal at which it is specified. Following is the syntax of ROUND()function:

SELECT ROUND(column name, decimals) FROM table name;

 Note: column name and decimals parameter is important to give.

NOW()

The NOW() function returns the current system date and time. Following is the syntax of NOW() function:

SELECT NOW() FROM table name;

 

Following is an output for NOW() function:

2016-06-29 14:18:50

2016-06-29 14:18:50

2016-06-29 14:18:50

2016-06-29 14:18:50

2016-06-29 14:18:50

2016-06-29 14:18:50

2016-06-29 14:18:50

 

FORMAT()

FORMAT() function is used to format how function is to be displayed. Following is the syntax of NOW() function:

SELECT FORMAT(column name, format) FROM table name;

 

For example:

SELECT FORMAT(Now(),'YYYY-MM-DD') FROM student_ info;;

 

2016-06-29

2016-06-29

2016-06-29

2016-06-29

2016-06-29

2016-06-29

2016-06-29

 

AVG()

AVG() function is used to return the average value of numeric function. Following is the syntax of AVG() function:

SELECT AVG(column name) FROM table name;

 

In our example, column name will be the Marks which will return the average of all 7 students:

45.50

 

COUNT()

Count() function is very useful in counting the number of records, which is returned by SELECT statement. Following is the syntax of COUNT() function:

SELECT COUNT(*) FROM table name;

 Above function will count the number of records present in that table. Following is the example of COUNT() function:

SELECT COUNT(*) FROM student_info;

 

Output:

7

 The number of rows present in student_info is 7.

MAX()

MAX() function is used to count the maximum value in the given record. Following is the syntax of MAX() function:

SELECT MAX(column name) FROM table name;

Above function will give the maximum value in the given column name. Following is the example of MAX() function:

SELECT MAX(marks) FROM student_info;

 

Output:

67

 Above function had given the maximum marks of a student which is 67.

MIN()

MIN() function is used to count the minimum value in the given record. Following is the syntax of MIN() function:

SELECT MIN(column name) FROM table name;

Above function will give the minimum value in the given column name. Following is the example of MIN() function:

SELECT MIN(marks) FROM student_info;

 

Output:

34

 Above function had given the minimum marks of a student which is 34.

LAST()

LAST() function is used to last value in the given record. Following is the syntax of LAST() function:

SELECT LAST(column name) FROM table name;

Above function will give the last name in the given column name. Following is the example of LAST() function:

SELECT LAST(name) FROM student_info;

 

Output:

Jennifer

 Above function had given the name of column.

FIRST()

FIRST() function is used to first value in the given record. Following is the syntax of FIRST() function:

SELECT FIRST(column name) FROM table name;

Above function will give the first name in the given column name. Following is the example of FIRST()

function:

SELECT FIRST(name) FROM student_info;

 

Output:

John

 Above function had given the name of column.

SUM()

SUM() function is used to give the sum of the column in the given record. Following is the syntax of SUM() function:

SELECT SUM(column name) FROM table name;

Above function will give the sum of the given column in the given table. Following is the example of SUM() function:

SELECT SUM(marks) FROM student_info;

 

Output:

333

 Above function had given the sum of student marks.

User Defined Functions

To create a function CREATE FUNCTION statement is used. Following is the syntax is used to CREATE FUNCTION:

To create a function CREATE FUNCTION statement is used. Following is the syntax is used to CREATE FUNCTION:

CREATE FUNCTION name_of_function(parameter1,parameter2,…)

    RETURNS datatype

   [NOT] DETERMINISTIC

Code block

 

In above syntax, CREATE FUNCTION is used to create a function followed by the name of the function.

Parameter1, parameter2 are the parameters of the function inside the parentheses. You cannot specify INOUT, IN or OUT modifiers to the parameters. By default, all parameters are IN parameters.

In RETURN statement you must specify the data type of the return value.

If stored function does not return the same result it is termed as non deterministic and if return same result than it is considered as a deterministic.

In the body of the stored function code is written. It can be compound statements or a single statement. Inside the body at least one return statement should be specify.


Don't want to miss updates? Please click the below button!

Follow MindStick