Demonstrate use of Union, Intersect, Minus and Create views.

--TABLE USED
select * from exports;
EMPNO EMPNAME SAL JOB
4001 Qamar T.. 3600 Salesman
4002 Raghav S.. 3900 Analyst
4003 Anirudh K. 4600 Manager
select * from sales;
EMPNO EMPNAME SAL JOB
3051 Ekagra K. 6000 Manager
3052 Saurabh T. 4000 Salesman
3053 Richa T. 4000 Salesman
3054 Nikhil S. 2000 Clerk
select * from research;
EMPNO EMPNAME SAL JOB
2001 Parul T. 4100 Analyst
2002 Khushboo T. 3800 Analyst
2003 Sahil K. 4700 Manager
2004 Tahir A. 3600 Analyst
select * from accounts;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1022 Sarika M. 3000 Clerk
1023 Preeti A.. 3300 D. Mgr.
1024 Manisha P. 4500 Analyst
---------------------------------------------------------------------------------------------------
UNION STATEMENTS
select * from accounts union select * from research union select * from sales union select * from exports;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1022 Sarika M. 3000 Clerk
1023 Preeti A.. 3300 D. Mgr.
1024 Manisha P. 4500 Analyst
2001 Parul T. 4100 Analyst
2002 Khushboo T. 3800 Analyst
2003 Sahil K. 4700 Manager
2004 Tahir A. 3600 Analyst
3051 Ekagra K. 6000 Manager
3052 Saurabh T. 4000 Salesman
3053 Richa T. 4000 Salesman
3054 Nikhil S. 2000 Clerk
4001 Qamar T.. 3600 Salesman
4002 Raghav S.. 3900 Analyst
4003 Anirudh K. 4600 Manager
15 rows selected.
---------------------------------------------------------------------------------------------------
select job from accounts union all select job from research union all select job from sales union all select job from exports;
JOB
Manager
Clerk
D. Mgr.
Analyst
Analyst
Analyst
Manager
Analyst
Manager
Salesman
Salesman
Clerk
Salesman
Analyst
Manager
15 rows selected.
---------------------------------------------------------------------------------------------------
INTERSECT STATEMENTS
select job from accounts intersect select job from research ;
JOB
Analyst
Manager
---------------------------------------------------------------------------------------------------
select job from accounts intersect select job from research intersect select job from sales intersect select job from exports;
JOB
Manager
---------------------------------------------------------------------------------------------------
MINUS STATEMENTS
select job from accounts minus select job from research ;
JOB
Clerk
D. Mgr.
---------------------------------------------------------------------------------------------------
select * from accounts minus select * from research ;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1022 Sarika M. 3000 Clerk
1023 Preeti A.. 3300 D. Mgr.
1024 Manisha P. 4500 Analyst
---------------------------------------------------------------------------------------------------
CREATING VIEWS
create view TAcc as select * from Accounts where sal>3500;
View created.
---------------------------------------------------------------------------------------------------
select * from Tacc;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1024 Manisha P. 4500 Analyst
insert into tacc values(1025,'Ramesh C.',5000,'Peon');
1 row created.
---------------------------------------------------------------------------------------------------
select * from tacc;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1024 Manisha P. 4500 Analyst
1025 Ramesh C. 5000 Peon
---------------------------------------------------------------------------------------------------
select * from accounts;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1022 Sarika M. 3000 Clerk
1023 Preeti A.. 3300 D. Mgr.
1024 Manisha P. 4500 Analyst
1025 Ramesh C. 5000 Peon
---------------------------------------------------------------------------------------------------
insert into tacc values(1026,'Mahesh J.',1000,'Peon');
1 row created.
---------------------------------------------------------------------------------------------------
select * from tacc;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1024 Manisha P. 4500 Analyst
1025 Ramesh C. 5000 Peon
---------------------------------------------------------------------------------------------------
select * from Accounts;
EMPNO EMPNAME SAL JOB
1021 Meenu K. 4500 Manager
1022 Sarika M. 3000 Clerk
1023 Preeti A.. 3300 D. Mgr.
1024 Manisha P. 4500 Analyst
1025 Ramesh C. 5000 Peon
1026 Mahesh J. 1000 Peon
6 rows selected.
---------------------------------------------------------------------------------------------------
create view TEmp as select * from accounts where sal>3500 union select * from research where sal>3500 union select * from sales where sal>3500 union select * from exports where sal>3500 ;
View created.
---------------------------------------------------------------------------------------------------
select * from temp;
EMPNO EMPNAME SAL JOB
---------- ------------------------- ---------- -------------------------
1021 Meenu K. 4500 Manager
1024 Manisha P. 4500 Analyst
1025 Ramesh C. 5000 Peon
2001 Parul T. 4100 Analyst
2002 Khushboo T. 3800 Analyst
2003 Sahil K. 4700 Manager
2004 Tahir A. 3600 Analyst
3051 Ekagra K. 6000 Manager
3052 Saurabh T. 4000 Salesman
3053 Richa T. 4000 Salesman
4001 Qamar T.. 3600 Salesman
4002 Raghav S.. 3900 Analyst
4003 Anirudh K. 4600 Manager
13 rows selected.
---------------------------------------------------------------------------------------------------