1)Aggregate Functions:
(a) sum( ):
This function is used to get the sum of
numeric column.
Syntax: select sum(column_name) from table_name;
EX:select sum(salary) from employee;
O/p:
(b) avg( ):
This function is used to get the average of
numeric column.
Syntax:
select avg(column_name) from table_name;
EX:
select avg(salary) from employee;
O/p: avg(salary)
(c)count( ):
This function is used to get the no.of rows in table.
Syntax:
select count(*) from table_name;
EX:select count(*) from employee;
O/p: count(*)
➔ This fuction is also allows the where condition;
Ex:
select count(*) from employee where name= “a”;
O/P: 1
(d)min( ):
This function is used to get the minimum
value from a column.
Syntax:
select min(column_name) from table_name;
EX: select min(salary) from employee;
O/p: 30000
(e) max( ):
This function is used to get the maximum value
from a column.
Syntax:
select max(column_name) from table_name;
EX:select max(salary) from employee;
O/p: 35000
(f)first( ):
This function is used to get the first value of
selected column.
Syntax:
select column_name from table_name limit 1;
EX:select name from employee limit 1;
O/p: a
(g)last( ):
This function is used to get the last value of
selected column.
Syntax:
select column_name from table_name order by
column_name desc limit 1;
EX:
select name from employee order by name desc
limit 1;
O/p: e
2)Conversion Function:
Upper( ):
This function convert a string to Uppercase.
Syntax: select upper(string);
Eg: select upper(“dbms”) ;
O/p: DBMS
lower( ):
This function convert a string to lowercase.
Syntax: select lower(string);
Eg: select upper(“Dbms”)
O/p: dbms
3)String Functions:-
These are accept character as input and return number
or character value.
1)concat():-
This function is used to combine two strings.
Syntax: select concat(string1,string2);
Eg: select concat(“cse”,”world”);
O/p: cse world
2)strcmp( ):-This function is used to compare two strings.
Synax:
select concat(string1,string2);
Eg:select strcmp(“man” , “mom”);
O/p: 1
3)length( ):
This function is used to count the length of the
string.
Ex: select length(“cse”);
O/P: length(“cse”)
3
4)substr( ):
This function is used to return a portion of string
from given start point to end point.
Ex: select substr(“world”,2);
O/p: substr(“world”,2)
orld
5)instr( ):
This function is used to return a numeric position of a
character (or) string.
Ex: select instr(“world”, “l”)
O/P: instr(“world”, “l”);
4
6)lpad( ):
This function is used to inert the symbol with the
actual length of the string from left side.
Ex: select lpad(“world”,10, “*”);
O/P: lpad(“world”,10, “*”)
*****world
7)rpad( ):This function is used to insert the symbol
with the actual length of the string from rpad side.
Ex: select rpad(“world”,10, “*”);
Op:
rpad(“world”,10,“*”)
world*****
8)ltrim( ):
This function is used to remove leading spaces in a
given string from leftside.
Ex: select ltrim(“ world”);
O/P: world
9)rtrim( ):
This function is used to remove leading spaces in
a given string from rightside.
Ex: select ltrim(“world ”);
O/P: world
4)NUMERIC FUNCTIONS:
1. truncate( ):
Ex: select trunc(28.7)#removes decimal part
O/P:28
2)round( ):
Ex: select round(27.6)
O/P: 28
3)mod( ):
Ex: select mod(27,6)
O/P: 3 #remainder
4)least( ):
Ex: select least(-27.5,-28.5)
O/P: -28.5
5)greatest( ):
Ex: select greatest(-27.5,-28.5)
O/P: -27.5
6)sqrt( ):
Ex: select qtr(25)
O/P: 5
7)ceil( ):
Ex: select ceil(27.2)
O/P: 28
8)floor( ):
Ex: select floor(27.2)
O/P: 27
9)power( ):
Ex: select power(8,2)
O/P: 64
5)Date and time functions:
A)CURRENT_DA TE :
Returns the current date.
SYNTAX:
SELECT CURRENT_DATE;
O/P: 2025-08-06
B)CURRENT_TIME:
Returns the current time
SYNTAX:
SELECT CURRENT_TIME;
O/P: 14:32:21
C)NOW():
Returns current date and time.
SYNTAX:
SELECT NOW();
O/P:
2025-08-06 14:32:21
D)DAY(), MONTH(), YEAR():
Extracts parts of a date.
SYNTAX:
SELECT DAY('2025-08-06'), MONTH('2025-08-06'),
YEAR('2025-08-06');
O/P: 6, 8, 2025
