DAY - 17
JOIN AND FUNCTIONS
SQL Functions
SQL
provides many built-in functions to perform operations on data. These functions
are useful while performing mathematical calculations, string concatenations,
sub-strings etc. SQL functions are divided into two catagories,
- Aggregrate Functions
- Scalar Functions
1.
Aggregrate Functions
These
functions return a single value after calculating from a group of
values.Following are some frequently used Aggregrate functions.
A.
AVG()
Average
returns average value after calculating from values in a numeric column.
Its
general Syntax is,
SELECT AVG(column_name)
from table_name
Example using AVG()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query to find average of salary will be,
SELECT avg(salary)
from Emp;
Result
of the above query will be,
|
avg(salary)
|
|
8200
|
B.
COUNT()
Count
returns the number of rows present in the table either based on some condition
or without condition.
Its
general Syntax is,
SELECT COUNT(column_name)
from table-name
Example using COUNT()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query to count employees, satisfying specified condition is,
SELECT COUNT(name)
from Emp where salary = 8000;
Result
of the above query will be,
|
count(name)
|
|
2
|
Example of COUNT(distinct)
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query is,
SELECT COUNT(distinct
salary) from emp;
Result
of the above query will be,
|
count(distinct salary)
|
|
4
|
C.
FIRST()
First
function returns first value of a selected column
Syntax
for FIRST function is,
SELECT FIRST(column_name)
from table-name
Example of FIRST()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query
SELECT
FIRST(salary) from Emp;
Result
will be,
|
first(salary)
|
|
9000
|
D.
LAST()
LAST
return the return last value from selected column
Syntax
of LAST function is,
SELECT LAST(column_name)
from table-name
Example of LAST()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query will be,
SELECT
LAST(salary) from emp;
Result
of the above query will be,
|
last(salary)
|
|
8000
|
E.
MAX()
MAX
function returns maximum value from selected column of the table.
Syntax
of MAX function is,
SELECT MAX(column_name)
from table-name
Example of MAX()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query to find Maximum salary is,
SELECT
MAX(salary) from emp;
Result
of the above query will be,
|
MAX(salary)
|
|
10000
|
F.
MIN()
MIN
function returns minimum value from a selected column of the table.
Syntax
for MIN function is,
SELECT MIN(column_name)
from table-name
Example of MIN()
Consider
following Emp table,
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query to find minimum salary is,
SELECT
MIN(salary) from emp;
Result
will be,
|
MIN(salary)
|
|
6000
|
G.
SUM()
SUM
function returns total sum of a selected columns numeric values.
Syntax
for SUM is,
SELECT
SUM(column_name) from table-name
Example of SUM()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
Shane
|
29
|
8000
|
|
403
|
Rohan
|
34
|
6000
|
|
404
|
Scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query to find sum of salaries will be,
SELECT
SUM(salary) from emp;
Result
of above query is,
|
SUM(salary)
|
|
41000
|
2.
Scalar Functions
Scalar
functions return a single value from an input value. Following are soe
frequently used Scalar Functions.
A. UCASE()
UCASE
function is used to convert value of string column to Uppercase character.
Syntax
of UCASE,
SELECT UCASE(column_name)
from table-name
Example of UCASE()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
anu
|
22
|
9000
|
|
402
|
shane
|
29
|
8000
|
|
403
|
rohan
|
34
|
6000
|
|
404
|
scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query for using UCASE is,
SELECT
UCASE(name) from emp;
Result
is,
|
UCASE(name)
|
|
ANU
|
|
SHANE
|
|
ROHAN
|
|
SCOTT
|
|
TIGER
|
B. LCASE()
LCASE
function is used to convert value of string column to Lowecase character.
Syntax
for LCASE is,
SELECT LCASE(column_name)
from table-name
Example of LCASE()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
shane
|
29
|
8000
|
|
403
|
rohan
|
34
|
6000
|
|
404
|
scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query for converting string value to Lower case is,
SELECT
LCASE(name) from emp;
Result
will be,
|
LCASE(name)
|
|
Anu
|
|
Shane
|
|
Rohan
|
|
Scott
|
|
Tiger
|
C. MID()
MID
function is used to extract substrings from column values of string type in a
table.
Syntax
for MID function is,
SELECT MID(column_name,
start, length) from table-name
Example of MID()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000
|
|
402
|
shane
|
29
|
8000
|
|
403
|
rohan
|
34
|
6000
|
|
404
|
scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000
|
SQL
query will be,
select
MID(name,2,2) from emp;
Result
will come out to be,
|
MID(name,2,2)
|
|
Nu
|
|
Ha
|
|
Oh
|
|
Co
|
|
Ig
|
D. ROUND()
ROUND
function is used to round a numeric field to number of nearest integer. It is
used on Decimal point values. Syntax of Round function is,
SELECT ROUND(column_name,
decimals) from table-name
Example of ROUND()
Consider
following Emp table
|
eid
|
name
|
age
|
salary
|
|
401
|
Anu
|
22
|
9000.67
|
|
402
|
shane
|
29
|
8000.98
|
|
403
|
rohan
|
34
|
6000.45
|
|
404
|
scott
|
44
|
10000
|
|
405
|
Tiger
|
35
|
8000.01
|
SQL
query is,
SELECT
ROUND(salary) from emp;
Result
will be,
|
ROUND(salary)
|
|
9001
|
|
8001
|
|
6000
|
|
10000
|
|
8000
|
