SQL Functions with examples

In this post we will learn about Introduction of SQL and What are the functions available in SQl with example.Earlier,i have written a post about How joins used in SQL with examples
 go through this following link Joins in Sql with example. Here we will discuss about
built-in Functions in SQL.

SQL has provides many built-in functions to operations on data.All these built-in functions are useful while performing mathematical  calculations,String concatenations etc..

These functions are divided into two types. They are

1)Aggregate functions
2)Scalar functions


1) Aggregate Functions:

These functions are perform on set of values and returns single value, Except for COUNT
aggregate functions ignore null values.These functions are frequently used with Group by clause of the SELECT statement.

Most useful Aggregate functions has given below:

avg():

This function returns a average values.

Syntax:

Select avg(column name)from table_name;

Ex:

Consider the following is EMP table:

emp_id      name age                salary
   
  100      mahi          34                     9000
  101       laks           45                       9000
  102      pavn          22                     7000
  104      sree           45                     10000
  105      raju           29                       8000


Ex:

Select avg(salary) from emp;

Result:

8200


Count():

This functions returns no of rows present in the table.

Syntax:

Select count(column_name)from table_name;

Ex:

Consider above emp table and write query for Count function like below

Select count(salary)from emp;

Result:

5 (since in emp table there are 5 rows present)


First()

This functions returns the first value of selected column

Syntax:

Select first(column_name) from table_name;


Ex:

Select first(salary)from emp;

Result:

9000


Last()

This function returns the last value of the select column

Syntax:

Select last(column_name)from table_name;

Ex:

Select last(salary)from emp;

Result:

8000

max()

This functions returns the maximum value of the select column of the table.That means largest value.

Syntax:

Select max(Column_name)from table_name;

Ex:

select max(salary)from emp;

Result:

10000

min()

This functions returns the minimum value of the select column of the table.That means lowest value.

Syntax:

Select min(column_name)from table_name;

Ex:

Select min(salary)from emp;

Result:

7000

Sum()

This function returns total sum of select columns

Syntax:

select sum(column_name)from table_name;

Ex:

select sum(salary)from emp;

Result:

43000


2) Scalar Functions:

These functions returns a single value based on input values. I will provide frequently use scalar functions here.

UCASE():

This is used to covert a value of string column to upper case.

Syntax:

select UCASE(column_name)from table_name;

Ex:

select UCASE(name)from emp;


Result:

MAHI
LAKS
PAVAN
SREE
RAJU

LCASE()

This functions is used to convert filed into lower case character


Syntax:

select LCASE(column_name)from table_name;

Ex:

Select LCASE(name)from emp;

Result:

mahi
laks
pavan
sree
raju

len()

This function return the length of the text filed

Syntax:

Select len(column_name)from table_name;

Ex:

Select len(name) from emp;

MID()

This function is used to extract from substrings from column values of string type in table

Syntax:

Select mid(column_name,start,length)from emp_table;

Ex:

select mid(name,3,3) from emp;

Result:

hi
ks
va
ee
ju


ROUND()

This function is used to round of numeric value of nearest integer.  

Syntax:

select round(coulumn_name,decimals)from table_name;

Ex:

select round(salary)from emp;

Result:

suppose salary 9000.67 then the result will display 9001 , Likewise remain salary also will display.


Real also this post: 
Best SQL QUERIES Interview Questions with answers
TOP 20 SQL Query interview Questions
Views in SQL with examples
SQL Subquery with examples