CLAUSES TASK
===========================
GROUP BY:- Group by clause is used to divide similar data items
========= into set of logical groups.and also group by clause
reduces no of rows in each group.
Syntax:-select columnname.......
====== from tablename
group by columnname;
Execution order:-
==============
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.DISTINCT
7.ORDER BY
8.OFFSET
9.FETCH
RULE:- Other than group function columns specified after select
===== then those all columns must be specified after group by.
Otherwise oracle server returns an error not a GROUP BY
expression.
eg1:-
====
SQL>select deptno,job
from emp
group by deptno,job;
DEPTNO JOB
---------- ---------
20 MANAGER
20 ANALYST
10 PRESIDENT
10 CLERK
30 SALESMAN
10 MANAGER
20 CLERK
30 MANAGER
30 CLERK
eg2:-
====
SQL> select deptno,job,sum(sal)
from emp
group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 4175
20 ANALYST 8400
10 PRESIDENT 6300
10 CLERK 2600
30 SALESMAN 10900
10 MANAGER 3750
20 CLERK 4300
30 MANAGER 3950
30 CLERK 2050
Note:- In all relational databases we can also use group by clause
===== without using group functions.
EG:-
==
SQL>select deptno from emp
group by deptno;
DEPTNO
----------
30
10
20
step1:-group by deptno;
deptno Resultset table
------ ----------------
10 10 it reduces into '1' 10 deptno
20 10---------------------> 10
30 10
10 similar data items into 20 it reduces into '1' 20
20 set of logical groups 20----------------------> 20
30-----------------------> 20
10 30 it reduces into '1' 30
20 30 ----------------------> 30
30 30
step2:-select deptno from....
===== |
|-------->This deptno is selected from result set table
not from the original table.
DEPTNO
----------
30
10
20
individual group functions means there is no problem
---------------------------------------------------
SQL>select sum(sal) from emp;
SUM(SAL)
----------
46425
individual group functions there is no problem.
SQL>select deptno,sum(Sal) from emp;//it won't work
|| ||
|| ||
normal column means group function means always
record by record all values at a time executed.
executed
synchronization (MEANS RECORDS BY RECORD EXRECUTED) is not there
||
||Solution is
||
we must use group by
error:not agroup by expression
solution:-
=========
SQL>select deptno,sum(sal)
from emp
group by deptno;
||
it takes other than group function column.
HAVING Clause:- In all relational databses after group by clause we
============== are not allowed to use where clause. In place of this
one ansi/iso SQL provided another clause having.
Generally where clause is used to restrict the rows in a table.
where as having clause is used to restricting the groups
after group by.
in where clause we are not allowed to use group functions
where as in having clause we can also use group functions.
-----------------------------------------------------------------------------------
1.Write a query to display no of persons in each state
from following PERSON table
-->simply i want state wise count
group by
EG: Detailed data-------------------------------------->Summarized data
pid pname state state count(*)
--------- ------- -------- ------- ---------
1 SUBBARAO AP AP 4
2 KIRAN TS TS 2
3 RAJU TS MP 1
4 ADITYA MP MH 1
5 GOWTHAM AP
6 VISWAJITH MH
7 SURYA AP
8 SAI TEJA AP
when ever group clas dont use *
IT RADUCES NUMBER OF ROWS IN EACH GROPU
SQL> select state ,count(*) from person group by state ;
2.Find state wise,gender wise count(population)
from the following PERSONS table?
PID PNAME GENDER STATE
------ --------- -------- --------
1 SUBBARAO M AP
2 KAVYA F TS
3 KIRAN M TS
4 GOWTHAM M AP
5 KERTHI F AP
6 SURYA M AP
7 ANUSHKA F AP
8 RAJU M TS
select state,gender,count(*) from persons where state in('TS','AP') and
gender in('M','F') group by state,gender;
or
SQL> SELECT STATE, GENDER, COUNT(PID) AS population
2 FROM PERSONS
3 GROUP BY STATE, GENDER;
3.Write a query to display number of employees in each department
from emp table using group by?
SELECT DEPTNO ,COUNT(*) AS NO_EMPLOYEES FROM EMP GROUP BY DEPTNO;
4.Write a query to display number of employees in each job from emp
table using group by?
SELECT JOB ,COUNT(*) AS NO_EMPLOYEES FROM EMP GROUP BY JOB;
5.Write a query to display deptno, minimum and maximum salary from
emp using group by?
SELECT deptno, MIN(sal) AS min_salary, MAX(sal) AS max_salary
2 FROM emp
3 GROUP BY deptno;
6.Write a query to display those departments having more than 3
employees from emp table?
SQL> SELECT DEPTNO,COUNT(*) AS NUM_EMPS FROM EMP GROUP BY DEPTNO HAVING COU
NT(*)>3;
==== clause.
7. Write a query to display those departments sum(sal) more than 9000
from emp table by using group by clause?
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>9000;
8.Write a query to display duplicate records from the
following TEST table ?
SQL> SELECT DEPTNO,COUNT(*) AS DUPLICATE FROM TEST GROUP BY DEPTNO;
SQL> SELECT TO_CHAR(HIREDATE,'YYYY') AS YEAR,COUNT(*) FROM EMP GROUP BY
TO_CHAR(HIREDATE,'YYYY');
SQL>select * from test;
deptno
----------
10
10
10
20
20
20
30
30
40
50
8.Write a query to display year, no of employees per year
in which more than one employee was hired from
emp table using group by?
SQL> SELECT HIREDATE,COUNT(*) AS NO_OF_EMPS FROM EMP GROUP BY HIREDATE HAVIN
G COUNT(*)>1;
9.How many employees are joined in january or september month by
using group by?
SQL> SELECT TO_CHAR(HIREDATE, 'MON') AS MONTH, COUNT(*)
2 FROM EMP
3 WHERE TO_CHAR(HIREDATE, 'MON') IN ('JAN', 'SEP')
4 GROUP BY TO_CHAR(HIREDATE, 'MON');
10.Display the sum of salaries of 10th and 30th department by using
group by?
SELECT DEPTNO,SUM(SAL) AS TOTAL_SAL FROM EMP GROUP BY DEPTN0 HAVING DEPTNO IN
(10,30);
11.Write a query to display no of employees joined in each QUARTER
from emp table using group by?
SQL> SELECT TO_CHAR(hiredate, 'Q') AS quarter, COUNT(*) AS num_employees
2 FROM emp
3 GROUP BY TO_CHAR(hiredate, 'Q')
4 ORDER BY quarter;
12.Write a query to display those departments sum(Sal) having more
than 3 employees from emp table by using group by clause?
SELECT deptno, SUM(sal) AS total_salary
2 FROM emp
3 GROUP BY deptno
4 HAVING COUNT(*) > 3;
Note:-in all realtional databases we can also use invisible group
===== functions with in having clause.whenever we are implimenting
group function reports by using group by clause then
database internally having all other group functions.
SQL>select deptno,sum(sal)
from emp
group by deptno
having count(*)>3;
|---> we can also use invisible group functions in having clause.
13.Find year wise sales from the following ORDERS table
SQL>select to_char(orderdate,'yyyy'),sum(orderamt)
from orders
group by to_char(orderdate,'yyyy');
B.Find quarter wise sales from the following ORDERS table
SQL>select to_char(orderdate,'q'),sum(orderamt)
from orders
group by to_char(orderdate,'q');
orderid ordername orderdate ORDERAMT
------- ---------- ------------ ---------
1 SURYA 12-DEC-24 1300
2 GOWTHAM 17-JUN-22 1800
3 RAJU 16-JAN-22 800
4 GOWTHAM 23-DEC-23 1000
5 SURYA 08-MAR-24 1200
6 KIRAN 02-NOV-23 600
7 RAJU 04-FEB-24 900
-----------------------------------------------------------------------------------
What are the differences b/w where and having clause?
-----------------------------------------------------------------------------------
where | having
-----------------------------------------------------------------------------------
1.where clause condition is applied on |1.having clause condition is applied on
every row. | every group.
2.it filters the rows OR RECORDS |2.it filters the groups
3.group fuctions can not be used in |3.group fuctions can be used in having
where clause | clause
4.where gets executed before group by |4.having gets executed after group by
5.it can be used with out group by |5.it can not be used with out group by
------------------------------------------------------------------------------------
