SQL语句作业：（使用orcl数据库）
1、用SCOTT用户的部门表DEPT和员工表EMP，写出完成如下任务的SQL语句：
（1）列出所有员工的姓名及其直接上司的姓名。
SQL> select a.ename,b.ename mgr from emp a,emp b where a.empno=b.mgr;
ENAME MGR ---------- ---------- FORD SMITH BLAKE ALLEN BLAKE WARD KING JONES BLAKE MARTIN KING BLAKE KING CLARK JONES SCOTT BLAKE TURNER SCOTT ADAMS BLAKE JAMES
ENAME MGR ---------- ---------- JONES FORD CLARK MILLER
已选择13行。
（2）列出受雇日期早于其直接上司的员工的姓名、员工编号、部门号。
SQL> select a.ename,a.empno,a.deptno from emp a,emp b where a.empno=b.mgr and mo nths_between(a.hiredate,b.hiredate)<0;
ENAME EMPNO DEPTNO ---------- ---------- ----------
BLAKE 7698 30 JONES 7566 20 BLAKE 7698 30
SCOTT 7788 20 BLAKE 7698 30 JONES 7566 20 CLARK 7782 10
已选择7行。
（3）列出在部门“SALES”工作的员工姓名。
SQL> select emp.ename from dept,emp where dept.deptno=emp.deptno and dept.dname= 'SALES'; ENAME --------- ALLEN WARD MARTIN BLAKE TURNER JAMES
已选择6行。
（4）列出工资高于公司平均工资的所有员工的姓名、员工编号、部门号。
SQL> select emp.ename,emp.empno,emp.deptno from emp where sal>(select avg(sal) f rom emp);
ENAME EMPNO DEPTNO ---------- ---------- ----------
JONES 7566 20 BLAKE 7698 30 CLARK 7782 10 SCOTT 7788 20 KING 7839 10 FORD 7902 20
已选择6行。
（5）列出在每个部门的员工数量、平均工资和平均工作月数。
SQL> select dept.deptno,dname,cou from dept left join (select deptno,count(*) co
u from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno desc;
DEPTNO DNAME COU ---------- -------------- ---------- 40 OPERATIONS
30 SALES 6 20 RESEARCH 5 10 ACCOUNTING 3
SQL> select dept.deptno,dname,average_sal from dept left join (select deptno,avg (sal) average_sal from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno desc;
DEPTNO DNAME AVERAGE_SAL ---------- -------------- ----------- 40 OPERATIONS
30 SALES 1566.66667 20 RESEARCH 2175 10 ACCOUNTING 2916.66667
SQL> select dept.deptno,dname,average from dept left join (select deptno,avg(mon ths_between(sysdate,hiredate)) average from emp group by deptno) a on a.deptno=d ept.deptno order by dept.deptno desc;
DEPTNO DNAME AVERAGE ---------- -------------- ---------- 40 OPERATIONS
30 SALES 363.408044 20 RESEARCH 336.136001 10 ACCOUNTING 359.822023
（6）列出所有部门的详细信息和部门人数。
SQL> select dept.deptno,dname,loc,cou from dept left join (select deptno,count(* ) cou from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno d esc;
DEPTNO DNAME LOC COU ---------- -------------- ------------- ---------- 40 OPERATIONS BOSTON
30 SALES CHICAGO 6 20 RESEARCH DALLAS 5 10 ACCOUNTING NEW YORK 3
（7）列出各种职位的最低工资。
SQL> select job,min(sal) from emp group by job;
JOB MIN(SAL) --------- ----------
CLERK 800 SALESMAN 1250 PRESIDENT 5000 MANAGER 2450 ANALYST 3000
（8）列出部门经理中工资最低的那个经理的姓名、工资、部门号。
SQL> select ename,sal,deptno from emp where sal<=all(select sal from emp where j ob='MANAGER') and job='MANAGER';
ENAME SAL DEPTNO ---------- ---------- ----------
CLARK 2450 10
（9）列出所有员工的年工资（不含佣金），按年薪从低到高排序。
SQL> select empno,ename,sal*12 年薪 from emp order by sal asc;
EMPNO ENAME 年薪 ---------- ---------- ----------
7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000
7499 ALLEN 19200 7782 CLARK 29400 7698 BLAKE 34200 7566 JONES 35700
EMPNO ENAME 年薪 ---------- ---------- ----------
7788 SCOTT 36000 7902 FORD 36000 7839 KING 60000
已选择14行。
（10）将“SALES”部门所有员工的佣金提高至其月薪的30%。
SQL> update emp set comm=sal*0.3 where deptno=(select deptno from dept where dna me='SALES');
已更新6行。
SQL> select * from emp where deptno=(select deptno from dept where dname='SALES' );
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 480 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 375 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 375 30