SQL(function)

It is an set of SQL statements that expect only input parameters, that perform
action and return the result.

A function can return an only single value or a table. Function are used as a part
of SQL commands.

Types of functions:

1. Scalar function
2. Aggregate function

Scarlar function:

A scalar function is a function that operates on the scalar values. It takes one or
more inputs values as arguments directly and return a value.

There are different types of scalar function like:

 Math function.
 Text function.
 Date function.
 Etc.
These function can be applied over the columns of a table to perform relevant
operation on the value of each record.

Aggregate function:

An aggregate function is that operate an aggregate data. Aggregate function

takes a complete set of data as input and return the value that is computed from
all the values in the set.

 Min()
 Max()
 AVG()
 Sum()
 Count()

Math function:

Math function or the mathematical function performs the operation over the
different numeric values.

 Power()
 Round()
 Mod()

Power():

Power function returns the value of number raised to the power of another
number. The power function is also called POW().

Syntax: POW(m,n) here m is a number which is the base of the experimental n is a

number which is the exponent of the exponential.

Eg:1:- mysql> select pow(5, 3);

>> 125

Eg:2:- mysql> select pow(2, 8);

>> 256
Round ():

Round function writes a number rounded to a number of decimal places.

Syntax: round(coloumn_name, position of decimal)

Eg:1:- mysql> select round(454.352,2);

>> 454.35

Eg:2:- mysql> select round(454.352);

>> 454

Eg:3:- mysql> select round(454.325);

>> 500

Eg:4:- mysql> select round(454.325);

>> 454.31

Mod():

The mod returns the reminder of two numeric values.

Syntax: mod(dividend, divisor)

Here dividend is a literal number or a numeric expression to divide and divisor is a

literal number or a numeric expression by which to divide the dividend.

Eg:1:- mysql> select mod(19, 3);

>> 1

Eg:2:- mysql> select mod(22.5, 7);

>> 1.5

Text function:
the text function is performed over the test values.
Syntax: UPPER(str)

Eg:1:- mysql> select upper(‘information_practices’);

>> INFORMATION_PRACTICES

# upper case is also represent as ucase().

Syntax: lower(str)

It returns the string(str) with all characters changed to lover case.

Eg:2:- mysql> select lower(‘MOHIT’);

>> mohit

#Substring()

Substring performs the operations over the string values. The forms without
len(length) argument return a substring from a string(str).

Starting at the position pos(). The forms with a len argument returns a substring
len character long from string(str).

Syntax: substring(str, pos)_substring(str, from pos)

Syntax: substring(str, pos, len)_substring(str, from pos for len)

Eg:1:- mysql> select substring(‘eligibility’,3);

>> gibility

Length(str):

In text length function performs operations over the string values. Length
function returns the length of str.

Syntax: length(‘str’)

Eg:1:- mysql> select length(‘text’);

>> 4
Eg:2:- mysql> select length(‘information_practices’);

>> 21

Eg:3:- mysql> select length(‘birth’);

>> 5

Left function: Left function returns the left most len character from the string
value Or null if any argument is null.

Eg:1:- mysql> select left(‘eligibility’, 5);

>> eligi

Eg:2:- mysql> select left(‘information_practices’, 11);

>> informatics

Right function:

The right function returns the right most character from the string(str) or null if
any argument is null.

Eg:1:- mysql> select right(‘eligibility’, 6);

>> bility

INSTR function(str, substr):

Instring function returns the position of the first occurance of the substring in
string(str).

Eg:1:- mysql> select instr(‘eligibility’, bility);

>> 6

Eg:2:- mysql> select instr(‘toolbox’, box);

>> 5

Ltrim(str):
Ltrim() returns the string with leading space. Ltrim removes the space characters.

Eg:1:- mysql> select ltrim(‘toolbox’);

>> toolbox

Rtrim(str):

Rtrim function return the string(str) with trailing space characters remove.

Eg:1 mysql> select rtrim(‘toolbox’);

>> toolbox

Trim(str):

Trim function returns the string with all space character prefix removed.

Eg:1:- mysql> select trim(‘toolbar’);

>> toolbar

Eg:2:- mysql> select trim(LEADING’x’ FROM ‘xxxtoolboxxxx’);

>> toolbox

Date function:

In sql date function performs operations over the date values. Following are the
different types of date().

1. NOW()
2. DATE()
3. MONTH()
4. MONTHNAME()
5. YEAR()
6. DAY()
7. DAYNAME()

NOW()
Now() returns the current date and time at a value in ‘yyyy-mm-dd hh:mm:ss’
format depends on their location. The function is used in string or numeric
content.

Eg:1:- mysql> select NOW();

>> “2023-11-17 09:25:30”

DATE(): Date function extract the date part from the date expression (expr).

Eg:1:- select date(‘2023-11-17’);

>> 2023-11-17

MONTH()

Month function returns the month for date in the range of 1 to 12.

Eg: mysql> select month(‘2023-11-17’);

>> 11

MONTHNAME()

Monthname returns the full name of month.

Eg: select Monthname(‘2020-11-17’);

>> November

Year()

Year function returns the year of the date in the range of 1000 to 9999.

Eg: mysql> select year(‘2023-11-17’);

>> 2023

DAY()

Day function return the day of the month in the range of 1 to 31.
Eg: mysql> select day(‘2023-11-17’);

>> 17

DAYNAME()

Dayname function returns the week day name from the date.

Eg: mysql> select dayname(‘2023-11-17’);

>> Friday

Aggregate()

Sql performs operation over the set of values or apply on the coloumn aggregate
function are also called group function.

Types of aggregate function:

 MAX()
 MIN()
 AVG()
 SUM()
 COUNT()

To understand the sql aggregate() consider a employee database table which

have the following record.

Employee(Emp)

Emp id Name Salary(Sal) Department(depr)

001 Mansi 5000 Marketing
002 Sandeep 4000 HR
003 Mohit NULL HR
004 Shubh 3500 Supervisor
005 Ashi 4500 Manager
006 Umang 4000 HR
007 Krishna NULL Coordinator
Eg:1 mysql>MAX(Sal) FROM Emp;

Eg:2 mysql>MIN(Sal) FROM Emp;

Eg:3 mysql>AVG(Sal) FROM Emp;

Eg:4 mysql>SUM(Sal) FROM Emp;

Eg:5 mysql>COUNT(Sal) FROM Emp;

Eg:6 mysql>COUNT(*) FROM Emp;

Output:

>> 5000

>> 3500

>> 4200

>> 21000

>> 5

>> 7

Aggregate function details

MAX():- max function used to get the maximum value from a column to get the
maximum salary(as per the given table) draw by employees the query would be:-

Mysql> select MAX(Sal) FROM Emp;

MIN():- min function is used to get the minimum value from a column. To get the
minimum salary drawn by an employee, the query would be:-

Mysql> select MIN(Sal) FROM Emp;

AVG():- avg function is used to get the average values of a numeric column. To get
the average salary drawn by an employee the query would be:-

Mysql> Select AVG(Sal) FROM Emp;

SUM():- sum function is used to get the sum of a numeric value in the column. to
get the total salary drawn by an employee the query would be:-

Mysql> select SUM(Sal) FROM Emp;

COUNT():- count function read the numbers of row in the given table that satisfy
the condition in WHERE clause if the WHERE clause condition is not satisfied then
the query returns the total numbers of rows in the given table.

Eg: if we want number of employees in a particular department from given table

then the query would be:

Mysql> select count() FROM Emp;

The output will be 7 (as per the given Emp table). Another condition of count
function.

Eg: mysql> select count () FROM Emp WHERE depr = HR;

>> 3

MySQL order by clause

Mysql order by clause to sort the table data in accending and decending order by
default data is not insert into the table in any order than the data will be arranged
in accending order.

If we want to return the value in any particular order then we have to sort it by
using my sql order by clause or statement.

Syntax:-

Mysql> select column_name FROM table_name orederby(column_name

ASC/DESC).

Eg: mysql> select * FROM Emp orderby sal;

We are having a database of student table with the following database.

Mystudent
 Roll no. Student name Class Marks
001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
004 Subh 11 95
005 Mansi 9 63
006 Krishna 12 84
007 Umang 10 46
Mysql> select* FROM my student

GROUP BY clause

The GROUP BY clause groups a set of rows or records into a set of summay
row/record by by value of column. It return one row for each group.

The GROUP BY clause is an optional clause of the select statement.

Syntax:- select c1,c2,c3………cn, aggregate (ci) from table_name WHERE_condition

GROUP BY c1,c2………cn;

Eg: mysql> select class from mystudent groupby class;

>>

Class
9
10
11
12
Eg: mysql> select * class FROM mystudent;

>>

Roll no. Student name Class Marks

001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
004 Subh 11 95
005 Mansi 9 63
 006 Krishna 12 84
007 Umang 10 46

Eg: mysql> select * class COUNT(*) FROM mystudent GROUP BY class;

>>

Class Count
9 2
10 1
11 2
12 2

Eg: mysql> select * FROM mystudent database table;

>>

Roll no. Student name Class Marks

001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
004 Subh 11 95
005 Mansi 9 63
006 Krishna 12 84
007 Umang 10 46

Eg: mysql> select class, AVG(marks) FROM mystudent GROUP BY class;

>>

Class Avg marks

9 78
10 46
11 90
12 77
Eg: mysql> select * FROM mystudent;

Roll no. Student name Class Marks

001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
004 Subh 11 95
005 Mansi 9 63
006 Krishna 12 84
007 Umang 10 46

Eg: mysql> select class, AVG(marks) FROM mystudent WHERE class > 10GROUP
BY class order by marks(desc);

>>

Class Avg marks

12 90
11 77
Mysql GROUP BY HAVING clause
The having clause is used in the select statement to specify the condition for
group or rows or aggrigates.

Or

The having clause is used with the group of clause to filter group base on a
specified condition. WHERE is applied before GROUP BY , HAVING is applied after
filter or aggregates.

Eg: mysql> select *FROM mystudent;

Roll no. Student name Class Marks

001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
 004 Subh 11 95
005 Mansi 9 63
006 Krishna 12 84
007 Umang 10 46

Eg: mysql> select class, avg FROM mystudent GROUP BY class having avg marks <
90;

>>

Class Avg marks

9 78
10 46
12 77
Eg: mysql> select * FROM mystudent;

Roll no. Student name Class Marks

001 Ayush 9 95
002 Mohit 12 70
003 Tanya 11 85
004 Subh 11 95
005 Mansi 9 63
006 Krishna 12 84
007 Umang 10 46

Eg: mysql> select class,AVG(marks) FROM mystudent GROUP BY class HAVING

COUNT(*)<3;

>>

Class Avg marks

9 78
10 48
11 90
12 77