7-多数据表连接和集合运算.ppt

* * * * SELECT e.ename,e.ename,e.sal,deptno,d.loc FROM emp e JOIN dept d USING (deptno) WHERE deptno = 20 ;--括号不能省略 * * SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno); --;--括号能省略 update emp set deptno=null where empno=7499 * create table manager as select * from emp where job=upper(manager); EMPNO ENAMELOCENAME ---------- ---------- ------------- ----------7788 SCOTTDALLASJONES7902 FORDDALLASJONES7499 ALLENCHICAGOBLAKE7521 WARDCHICAGOBLAKE7900 JAMESCHICAGOBLAKE7844 TURNERCHICAGOBLAKE7654 MARTINCHICAGOBLAKE7934 MILLERNEW YORKCLARK 已选择8行。 SELECT e.empno, e.ename, d.loc,m.ename FROMemp e JOINdept d ONe.deptno = d.deptno JOINmanager m ONe.mgr = m.empno; * update emp set deptno=null where empno=7499 * * update emp set deptno=null where empno=7499 * EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7369 SMITHCLERK7902 17-12月-80800207499 ALLENSALESMAN7698 20-2月 -811600300307521 WARDSALESMAN7698 22-2月 -81125050030…..已选择16行。 * SELECT * FROM EMPUNION ALL SELECT * FROM MANAGER EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7369 SMITHCLERK7902 17-12月-80800207499 ALLENSALESMAN7698 20-2月 -811600300307521 WARDSALESMAN7698 22-2月 -81125050030……7698 BLAKEMANAGER7839 01-5月 -812850307782 CLARKMANAGER7839 09-6月 已选择17行。 * SELECT * FROM EMP INTERSECT SELECT * FROM MANAGER EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7566 JONESMANAGER7839 02-4月 -812975207698 BLAKEMANAGER7839 01-5月 -812850307782 CLARKMANAGER7839 09-6月 已选择3行。 * SELECT * FROM EMP MINUS SELECT * FROM MANAGER EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7369 SMITHCLERK7902 17-12月-80800207499 ALLENSALESMA