Oracle learning-Set Operations

I. Set operation operator UNION: (UNION) returns the UNION ALL records of the Two sets that remove duplicate values. (UNION) returns the records INTERSECT (intersection) of the Two sets that remove duplicate values) returns all records of the two sets. duplicate records only take the MINUS once: (difference set) return belongs to the first set, however, all records that do not belong to the second set must have the same number of columns and have the same type. The result of the set operation uses the header of the first set as the final header, order by must be placed behind each set. 2. Use the UNION operator to query the employee information of 10 and 20 departments in the scott table. SQL code> select * from emp 2 where deptno = 10 3 union 4 select * from emp 5 where deptno = 20; empno ename job mgr hiredate sal comm deptno ----------- --------- ---------- ------------ ---------- 7369 smith clerk 7902-17-12 months-80 880 20 7566 jones manager 7839 02-month-81 2975 20 7782 clark manager 7839 09-6 months-81 2450 10 7788 SCOTT ANALYST 7566 19-4 month-87 3000 20 7839 king president 17-11 month-81 5000 10 7876 adams clerk 7788 month-87 1100 20 7902 ford analyst 7566 03-12 month-81 3000 20 7934 miller clerk 7782 23-1 month-82 1300 10. Eight rows have been selected. Use the UNION operator to calculate the total salary of each department in scott's emp table and the salary of each department's position. SQL code> break on deptno skip 2; SQL> select deptno, job, sum (sal) from emp group by deptno, job 2 union 3 select deptno, to_char (null), sum (sal) from emp group by deptno 4 union 5 select to_number (null ), to_char (null), sum (sal) from emp; deptno job sum (SAL) ----------- ---------- 10 CLERK 1300 MANAGER 2450 PRESIDENT 5000 8750 20 ANALYST 6000 CLERK 1980 MANAGER 2975 10955 30 CLERK 950 MANAGER 2850 SALESMAN 5600 9400 29105 selected 13 rows. Use the union all operator to query information in the dept table of scott user SQL code> select * from dept 2 union all 3 select * from dept; deptno dname loc ---------- ------------ ------------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston has selected 8 rows. Use the INTERSECT operator to query the Department numbers in the dept and emp tables under scott. SQL code> select deptno from emp 2 intersect 3 select deptno from dept; DEPTNO ---------- 10 20 30 use the MINUS operator to query the Department number that exists in the dept table under the scott user and does not exist in the emp table SQL code> select deptno from dept 2 minus 3 select deptno from emp; DEPTNO ---------- 40 use the MINUS operator to query the employee salaries in the emp table of the scott user (1200-2000), not (1400-3000) SQL> select empno, ename, job, sal 2 from emp 3 where sal between 1200 and 2000 4 minus 5 select empno, ename, job, sal 6 from emp 7 where sal between 1400 and 3000; empno ename job sal ------------ ----------- ---------- 7521 ward salesman 1250 7654 martin salesman 1250 7934 miller clerk 1300
Author: dong_dong