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

* * * * 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 ENAME LOC ENAME ---------- ---------- ------------- ---------- 7788 SCOTT DALLAS JONES 7902 FORD DALLAS JONES 7499 ALLEN CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7900 JAMES CHICAGO BLAKE 7844 TURNER CHICAGO BLAKE 7654 MARTIN CHICAGO BLAKE 7934 MILLER NEW YORK CLARK 已选择8行。 SELECT e.empno, e.ename, d.loc,m.ename FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN manager m ON e.mgr = m.empno; * update emp set deptno=null where empno=7499 * * update emp set deptno=null where empno=7499 * EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 ….. 已选择16行。 * SELECT * FROM EMP UNION ALL SELECT * FROM MANAGER EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 …… 769