Qube: Display all possible combinations with in group by.
Q. Display total salary given to department Trans,total salary given to each project with in it and total salary given to each employee in trans.?
Data:
Table
SQL> create table amz_dept(dept_id number,dept_name varchar2(10),proj_id number,
proj_name varchar2(10),emp_id number,emp_name varchar2(20),salary number,BS char
(2));
Insert:
insert into amz_dept values(1,'Trans',10,'Support',3872,'Anand',2000,'B');
insert into amz_dept values(1,'Trans',10,'Support',3872,'Anand',3000,'S');
insert into amz_dept values(1,'Trans',10,'CCS',3875,'Anand',2000,'S');
insert into amz_dept values(1,'Trans',10,'Support',3874,'Sarath',4000,'B');
insert into amz_dept values(1,'Trans',11,'Shipping',3876,'Kevin',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3878,'Gireesh',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3879,'Lynda',3000,'S');
insert into amz_dept values(1,'Trans',11,'Shipping',3832,'Steven',3000,'S');
insert into amz_dept values(1,'Trans',12,'GTS',3845,'Sahid',3000,'S');
insert into amz_dept values(1,'Trans',12,'GTS',3834,'Amar',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3812,'Anil',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3839,'Putla',3000,'S');
insert into amz_dept values(1,'Trans',13,'CCS',3812,'Kolimi',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3898,'bhanu',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3867,'sridhar',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3858,'Rajat',3000,'S');
insert into amz_dept values(1,'Trans',14,'AVS',3809,'JJJJ',3000,'S');
Results:
SQL> set lin 20000;
SQL> set pagesize 200;
SQL> select proj_name,emp_name,sum(salary) from amz_dept group by rollup(proj_na
me,emp_name);
PROJ_NAME EMP_NAME SUM(SALARY)
---------- -------------------- -----------
AVS JJJJ 3000
AVS Rajat 3000
AVS bhanu 3000
AVS sridhar 3000
AVS 12000
CCS Anil 3000
CCS Anand 2000
CCS Putla 3000
CCS Kolimi 3000
CCS 11000
GTS Amar 3000
GTS Sahid 3000
GTS 6000
Support Anand 5000
Support Sarath 4000
Support 9000
Shipping Kevin 3000
Shipping Lynda 3000
Shipping Steven 3000
Shipping Gireesh 3000
Shipping 12000
50000
22 rows selected.
SQL> select proj_name,emp_name,sum(salary) from amz_dept group by cube(proj_name
,emp_name);
PROJ_NAME EMP_NAME SUM(SALARY)
---------- -------------------- -----------
50000
Amar 3000
Anil 3000
JJJJ 3000
Anand 7000
Kevin 3000
Lynda 3000
Putla 3000
Rajat 3000
Sahid 3000
bhanu 3000
Kolimi 3000
Sarath 4000
Steven 3000
Gireesh 3000
sridhar 3000
AVS 12000
AVS JJJJ 3000
AVS Rajat 3000
AVS bhanu 3000
AVS sridhar 3000
CCS 11000
CCS Anil 3000
CCS Anand 2000
CCS Putla 3000
CCS Kolimi 3000
GTS 6000
GTS Amar 3000
GTS Sahid 3000
Support 9000
Support Anand 5000
Support Sarath 4000
Shipping 12000
Shipping Kevin 3000
Shipping Lynda 3000
Shipping Steven 3000
Shipping Gireesh 3000
37 rows selected.
SQL>
Connect By Prior:It will display tree structured query. It will start tree with START WITH condition and display all it's child nodes.
SQL> select * from trans_emp;
EMP_NAME EMP_ID MGR_ID
-------------------- ---------- ----------
Jay 1000
Sekhar 1001 1000
Shiva 1002 1000
Ramesh 1003 1001
Sravani 1004 1003
Anand 1005 1004
Sarat 1006 1004
Debo 1007 1002
Ankit 1008 1007
9 rows selected.
SQL> select lpad(' ', LEVEL*4)||emp_name emp_hirarchy from trans_emp connect by
prior emp_id=mgr_id start with mgr_id is null;
EMP_HIRARCHY
--------------------------------------------------------------------------------
Jay
Sekhar
Ramesh
Sravani
Anand
Sarat
Shiva
Debo
Ankit
9 rows selected.
SQL>
Different results when we use where condition or not.
SQL> SELECT lpad(' ',level*4)||emp_name FROM trans_emp START WITH emp_id = 1000
CONNECT BY PRIOR emp_id = mgr_id AND mgr_id != 1004 ;
Jay
Sekhar
Ramesh
Sravani
Shiva
Debo
Ankit
7 rows selected.
SQL> SELECT lpad(' ',level*4)||emp_name FROM trans_emp START WITH emp_id = 1000
CONNECT BY PRIOR emp_id = mgr_id-- AND mgr_id != 1004 ;
Jay
Sekhar
Ramesh
Sravani
Anand
Anand:Shalini
Anand
Anand:Shalini
Sarat
Shiva
Debo
Ankit
12 rows selected.
SQL> SELECT lpad(' ',level*4)||emp_name FROM trans_emp where mgr_id!=1004 START
WITH emp_id = 1000 CONNECT BY PRIOR emp_id = mgr_id ;
Sekhar
Ramesh
Sravani
Anand:Shalini
Anand:Shalini
Shiva
Debo
Ankit
8 rows selected.
