oracl练习题.docVIP

oracle练习题 ROWNUM的应用： (1)查询emp表的前5条数据 SELECT * FROM emp WHERE ROWNUM=5; (2)查询emp表的第6到第10条记录 SELECT * FROM (SELECT ROWNUM rn FROM emp WHERE ROWNUM =10) TEST WHERE test.rn5; ROWID的应用：删除表中的重复记录 --哪些记录有重复的记录 SELECT dname,loc,MIN(ROWID) FROM copy_dept GROUP BY dname,loc HAVING COUNT(deptno) 1; --不能删除的数据 SELECT dname,loc,MIN(ROWID) FROM copy_dept GROUP BY dname,loc DELETE FROM copy_dept WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM copy_dept GROUP BY dname,loc) 1、列出至少有一个员工的部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资 SELECT d.deptno,d.dname,COUNT(empno),AVG(sal),MIN(sal),MAX(sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname HAVING COUNT(empno)1 ORDER BY deptno; 2、列出薪金比“SMITH” 或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导的姓名。 SELECT e.empno,e.ename,d.dname FROM emp e,dept d,emp m WHERE e.deptno=d.deptno AND e.sal ALL (SELECT sal FROM emp WHERE ename IN (SMITH,ALLEN)) AND e.mgr=m.empno(+); 3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排序； SELECT e.empno,e.ename,m.empno 上级编号,m.ename 上级姓名,(m.sal+NVL(m,0))*12 income FROM emp e,emp m WHERE e.mgr=m.empno(+) ORDER BY income DESC; 4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数 SELECT e.empno,e.ename, e.deptno,m.ename 上级领导,d.dname,d.loc FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) cou FROM emp GROUP BY deptno) temp WHERE e.mgr=m.empno(+) AND e.hiredate m.hiredate AND e.deptno=d.deptno AND e.deptno=temp.dno; 当查询显示的时候需要统计信息，但是又不能直接使用统计函数查询的话，通过子查询在from子句之后进行统计。 5、列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 SELECT d.deptno, d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ORDER BY d.deptno 6、列出所有“CLERK”的姓名及部门名称，部门人数，工资等级 SELECT e.ename,d.dname,temp.income,s.grade FROM emp e,dept d,(SELECT deptno dno, COUNT(empno) income FROM emp GROUP BY deptno) temp,salgrade s WHERE job=CLERK AND e.deptno=d.deptno AND e.deptno=temp.dno AND e.sal BETWEEN s.losal AND s.hisal; 7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资 SELECT temp.job,temp.count,d.dname,d.loc,e.ename,res.AVG FRO