oracle 数据库 练习题 3

一、使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下：

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资 ＝ 薪金 ＋ 佣金

习题解析：

 --1.列出在每个部门工作的员工数量、平均工资和平均服务期限。SELECT "COUNT"(DEPTNO),"TRUNC"("AVG"(SAL+"NVL"(COMM,0))),"TRUNC"("AVG"(SYSDATE-HIREDATE))FROM EMPGROUP  BY DEPTNO;--2.列出所有员工的姓名、部门名称和工资。 SELECT e.ENAME,d.DNAME,e.SAL+"NVL"(e.COMM,0) FROM EMP e,DEPT dWHERE e.DEPTNO=d.DEPTNO;--3.列出从事同一种工作但属于不同部门的员工的一种组合。--YETSELECT distinct a.ename,a.job,a.deptnoFROM EMP A,EMP BWHERE A.DEPTNO<>B.DEPTNO AND A."JOB"=B."JOB";--4.列出所有部门的详细信息和部门人数。 [*]SELECT DEPT.*,(SELECT "COUNT"(*)FROM EMPWHERE DEPT.DEPTNO=EMP.DEPTNO) AS NUMFROM DEPT;--5.列出各种工作的最低工资。SELECT JOB,"MIN"(SAL+"NVL"(COMM,0))FROM EMPGROUP BY "JOB";--6.列出各个部门的MANAGER（经理）的最低薪金。SELECT "MIN"(SAL)FROM EMPWHERE "JOB"='MANAGER'GROUP BY DEPTNO;--7.列出所有员工的年工资,按年薪从低到高排序。SELECT ENAME,(SAL+"NVL"(COMM,0))*12 YEARSALFROM EMPORDER BY YEARSAL ASC;--8.找出月薪相同的员工SELECT DISTINCT A.ENAME,A.SALFROM EMP A,EMP BWHERE A.SAL+"NVL"(A.COMM,0)=B.SAL+"NVL"(B.COMM,0) AND A.EMPNO<>B.EMPNO;--9.列出至少有一个员工的所有部门。SELECT DEPTNOFROM EMPGROUP BY DEPTNOHAVING "COUNT"(DEPTNO)>=1;--10.列出薪金比“SMITH”多的所有员工。SELECT EMPNO,ENAME,SALFROM EMPWHERE  SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');--11.列出所有员工的姓名及其直接上级的姓名。SELECT A.ENAME,B.ENAMEFROM EMP A LEFT JOIN EMP BON B.EMPNO=A.MGR;--12.列出受雇日期早于其直接上级的所有员工。SELECT A.ENAMEFROM EMP A LEFT JOIN EMP BON B.EMPNO=A.MGRWHERE A.HIREDATE<B.HIREDATE;--13.列出所有“CLERK”（办事员）的姓名及其部门名称。  SELECT E.ENAME,D.DNAMEFROM EMP E,DEPT DWHERE D.DEPTNO=E.DEPTNO AND E."JOB"='CLERK';--14.列出最低薪金大于1500的各种工作。SELECT "JOB"FROM EMPGROUP BY "JOB"HAVING "MIN"(SAL)>1500;--15.列出在部门“SALES”（销售部）工作的员工的姓名， 假定不知道销售部的部门编号。 SELECT E.ENAMEFROM EMP E,DEPT DWHERE D.DNAME='SALES' AND E.DEPTNO=D.DEPTNO;--16.列出薪金高于公司平均薪金的所有员工。SELECT EMPNO,ENAME,SALFROM EMPWHERE SAL>(SELECT "SUM"(SAL)/"COUNT"(SAL) FROM EMP);--17.列出与“SCOTT”从事相同工作的所有员工。    SELECT EMPNO,ENAMEFROM EMPWHERE "JOB"=(SELECT "JOB" FROM EMP WHERE ENAME='SCOTT') AND ENAME!='SCOTT';--18.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。   SELECT ENAME,SALFROM EMPWHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30);--19.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。SELECT ENAME,SALFROM EMPWHERE SAL>(SELECT "MIN"(SAL) FROM EMP WHERE DEPTNO=30);--20. 列出比“SCOTT”大两个月以上的员工姓名  add_months(sysdate,3) SELECT ENAMEFROM EMPWHERE HIREDATE<(SELECT "ADD_MONTHS"(HIREDATE,-2)FROM EMP WHERE ENAME='SCOTT');

总结：很多的函数都没接触过，费了很多的时间才做出来。比如说第一个工资，其中的COMM属性中含有NULL参数，然后进行简单的相加的时候就会将数据变成NULL，以至于解不出来，有NVL()函数，NVL (expr1, expr2)，【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致。