SQL Functions

INTRODUCTION

In this article, we will learn about SQL functions and the different types of functions.

TYPES OF FUNCTIONS

1. Basic function
2. String function
3. Case function
4. IIf function
5. User – defined function

BASIC FUNCTION

MIN () function

MIN () gives you the smallest value in a column.

SYNTAX

SELECT MIN( col _ name) FROM table _ name;

EXAMPLE

In this example, to extract the minimum age from the table, use the MIN()function to display minimum age.

SELECT MIN(age) FROM cemployee;

MAX () function

Max () gives the largest value in a column.

SYNTAX

SELECT MAX( col _ name) FROM table _ name;

EXAMPLE

In this example, to extract maximum salary from the table, use the MAX() function to display the maximum salary.

SELECT MAX (salary) FROM cemployee;

COUNT () FUNCTION

COUNT () returns the number of rows that match a specific criteria.

SYNTAX

SELECT COUNT(*) FROM table _name
WHERE condition;

EXAMPLE

In this example, to count maximum age from the table, use the count() function to display maximum age.

SUM () FUNCTION

SUM() function gives the total sum of numeric column.

SYNTAX

SELECT SUM (COL_NAME) FROM table _name;

EXAMPLE

In this example, to calculate the sum total amount of salary from the table, use the SUM() function to display total amount of total salary.

SELECT SUM(salary )FROM cemployee;

AVG() FUNCTION

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

SYNTAX

SELECT AVG(col _name) FROM table _name;

EXAMPLE

In this example, to calculate average salary from the table, use the AVG() function to display average salary.

SELECT AVG(salary) FROM cemployee;

STRING FUNCTIONS

LTRIM ()

Removes blanks on the left side of the character expression.

EXAMPLE

SELECT ‘sqllll’

SELECT ltrim(‘sqllll’);

This is string with leading spaces.

When we use the ltrim() function, all of those blank spaces on the left side will be removed.

LOWER ()

Converts all characters to lower case letters.

EXAMPLE

SELECT 'THIS IS SQL'

SELECT LOWER('THIS IS SQL');

After using the lower() function, the string where all the characters are in capital letters were turned to lowercase.

UPPER ()

Converts all characters to upper case letters.

EXAMPLE

SELECT 'sql language'

SELECT upper ('sql language');

After using the UPPER() function, the string where all the characters are in lowercase were turned to capital letters.

REVERSES ()

Reverses all the characters in the string.

EXAMPLE

In this example, to display the given string in reverse order, use the REVERSES() function.

SELECT REVERSE(' I LIKE SQL');

SUBSTRING ()

Gives a substring from the original string.

EXAMPLE

In this example, to display substring from the given string, use the SUBSTRING() function. Use the SUBSTRING() function extract only this part from the entire string.

SELECT ‘SQL language’
SELECT SUBSTRING('SQL language',5,9);

CASE STATEMENT

Case statement helps in multi way decision making.

CASE STATEMENT SYNTAX

CASE
WHEN condition1 THEN  result1
WHEN condition2 THEN  result2
WHEN condition3 THAN result3
END;

EXAMPLE

In this example, use the CASE statement to check whether the value is greater than or lesser than, or equal to.

select
case
when 10>20 then '10 is greater than 20'
when 10<20 then '10 is less than 20'
else '10 is equal to 20'
end

In this example, use the CASE statement to create grade column and grade column dependent on the salary column.

SELECT*,grade=
case
when salary<29000 then 'C'
when salary<40000 then 'B'
else 'A'
end
from demoemp
go

IIF() FUNCTION

IIF() function is an alternative of case statement.

SYNTAX

IIF (Boolean expression , true_value,false_value)

EXAMPLE

In this example, use iif() to check if the value is true or false. If the value is true, 10 is greater than, or if the value is false, 10 is less than.

SELECT
IIF(10>20,'10 is greater than 20','10 is less than 20');

In this example, use IIF() to create an employee generation column and dependent on the employee age.

SELECT id,name,age,IIF(age>29,’old employee’, ’young employee’) as employee_generation FROM demoemp;

USER-DEFINED FUNCTION

There are two types of user-defined functions:

• Scalar valued
• Table valued

SCALAR VALUED

Scalar valued function always returns a scalar values.

SYNTAX

CREATE  FUNCTION function_name(@param data_type,@param data_type…)
RETURNS return_data_type
AS
BEGIN
Function body
RETURN VALUE
END

EXAMPLE

In this example, use the scalar valued function to create add_five function, and after, create the function. Call the function using select dbo, and give the function a name and pass the number. The passed number increased by five.

CREATE FUNCTION add_five(@num as int)
  RETURNS int
  AS
  BEGIN
  RETURN(
   @num+5
  )
End

Add_five function was created.

SELECT dbo.add_five(30);

Select the function, and after, pass the number. The value has been added.

TABLE VALUED FUNCTION

A table valued function returns a table instead of a scalar.

SYNTAX

CREATE FUNCTION function_name(@param data_type,@param data_type…)
RETURNS table
AS
RETURN(SELECT column_list FROM table_name WHERE [condition])

EXAMPLE

In this example, use the table valued function to create function select_gender. This returns a table after passing the parameter. To return, select the table and give the condition for use the where clause to the passed parameter.

CREATE FUNCTION select_gender(@gender as varchar(20))
RETURNS table
AS
RETURN
(
    SELECT*FROM demoemp WHERE gender=@gender
)

Select_gender function has been created.

SELECT *FROM dbo.select_gender(‘male’);

Extracted male employee from the table.

SELECT*FROM dbo.select_gender(‘female’);

Extracted female employee from the table.

I hope this article helpful for you.