--------4．列出受雇日期早于其直接上级的所有员工。----------
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK
6 rows selected
--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门----------
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
2 from dept a left join emp b on a.deptno=b.deptno;
DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH 7369 SMITH CLERK 7902 1980-12-17 800.00 20
SALES 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 30
SALES 7521 WARD SALESMAN 7698 1981-2-22 1250.00 30
RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
SALES 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 30
SALES 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
ACCOUNTING 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
ACCOUNTING 7839 KING PRESIDENT 1981-11-17 5000.00 10
SALES 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 30
RESEARCH 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SALES 7900 JAMES CLERK 7698 1981-12-3 950.00 30
RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
ACCOUNTING 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
ACCOUNTING 102 EricHu Developer 1455 2011-5-26 1 5500.00 10
ACCOUNTING 104 huyong PM 1455 2011-5-26 1 5500.00 10
ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500.00 10
50abc
OPERATIONS
Developer
20 rows selected
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK
6 rows selected
--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门----------
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
2 from dept a left join emp b on a.deptno=b.deptno;
DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH 7369 SMITH CLERK 7902 1980-12-17 800.00 20
SALES 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 30
SALES 7521 WARD SALESMAN 7698 1981-2-22 1250.00 30
RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
SALES 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 30
SALES 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
ACCOUNTING 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
ACCOUNTING 7839 KING PRESIDENT 1981-11-17 5000.00 10
SALES 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 30
RESEARCH 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SALES 7900 JAMES CLERK 7698 1981-12-3 950.00 30
RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
ACCOUNTING 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
ACCOUNTING 102 EricHu Developer 1455 2011-5-26 1 5500.00 10
ACCOUNTING 104 huyong PM 1455 2011-5-26 1 5500.00 10
ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500.00 10
50abc
OPERATIONS
Developer
20 rows selected
--------6．列出所有“CLERK”（办事员）的姓名及其部门名称。----------
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
ENAME DNAME
---------- --------------
SMITH RESEARCH
ADAMS RESEARCH
JAMES SALES
MILLER ACCOUNTING
--------7．列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
HIGHSALJOB
----------
ANALYST
Developer
MANAGER
PM
PRESIDENT
--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected
--------9．列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp);
ENAME
----------
JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING
8 rows selected
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
ENAME DNAME
---------- --------------
SMITH RESEARCH
ADAMS RESEARCH
JAMES SALES
MILLER ACCOUNTING
--------7．列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
HIGHSALJOB
----------
ANALYST
Developer
MANAGER
PM
PRESIDENT
--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected
--------9．列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp);
ENAME
----------
JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING
8 rows selected
