*************FUNCTIONS*****************
it is a block of code which is used to perform specific task
inbuilt functions
[Link] functions
[Link] functions
[Link] functions
[Link] functions
user defined
1.[pl/sql]
**[Link] functions
takes n number of input and generates a single output
types...
[Link](): used to obtain maximum value from the column
syntax:max(column_name);
[Link](): used to obtain minimum value from the column
syntax:min(column_name);
[Link](): used to obtain total value from the column
syntax:sum(column_name);
[Link](): used to obtain average value from the column
syntax:avg(column_name);
[Link]():used to obtain the number of rows from the given column value from the
column
syntax:count(*/column_name);
note: only count function takes * as an argument
**characteristics of aggregate function
->aggregate functions execute group by group
->we cannot pass any other columns along with aggregate functions
->we cannot use this as nested functions
->we cannot pass multiple columns along with aggregate functions.
->we cannot pass inside the where clause
->we can pass group by expression along with aggregate function
->it ignores null values.
[Link] the max ana min of sal from emp
->select max(sal),min(sal)
from emp;
52 waqtd the total salary given to the salesman
select sum(sal)
from emp
where job="salesman";
53 waqtd the average sal given to emp whose name starts with a or k
select avg(sal)
from emp
where fname like "a%" or fname like "k%";
[Link] the
55 waqtd the doj of the first emp in the company
select min(doj) from emp;
56 waqtd the number of emps working in the company
select count(*) from emp;
57 waqtd the number of different job roles available in the company
select count(distinct job) from emp;
[Link] the difference b\w max and min sal
select max(sal)-min(sal) from emp;
59 no of emps working in dept 113(can use * or dno)
select count(dno) from
emp where dno=113;
60. waqtd the number of emps working in dept 112 and 111
select count(*)
from emp
where dno=112 or dno=111;
[Link] no of emp working in each dept
select count(*),dno
from emp
group by dno;
******group by clause****
group by clause is used to group the records
syntax :
select aggregate exp/group by expression
from table_name
[where clause]
group by column_name;
(group by exp)
characteristics:
->it executes row by row
->group by clause executes after from clause if there is no where clause
->group by clause converts the row records into group records
->we can pass multiple columns inside group by clause
->after group clause all other clauses after it will executes group by group itself
->we can display only aggregate functions and grp by expression
[Link] the max sal and min sal given in each dept
select max(sal),min(sal),dno
from emp
group by dno;
63 waqtd the number of emps working in each job role
select count(*),job
from emp
group by job;
64 waqtd the max and min of sal given to salesman or dispatcher or accountant in
every dept
select max(sal),min(sal),dno
from emp
where job in("salesman","dispatcher","accountant")
group by dno;
65,waqtd the doj of first emp and last emp in every dept
select min(doj),max(doj),dno
from emp
group by dno;
66 waqtd the number of emp who are getting sal more than 35000 and sal less than
100000 in each dept
select count(*),dno
from emp
where sal>35000 and sal<100000
group by dno;
[Link] the number of emp who are getting salary more than or equal to 30000 and
max salary less than or equal to 150000 in each dept