Oracle数据库查询练习及答案.pdf
良辰美景奈何天，便赏心乐事谁家院。则为你如花美眷，似水流年。汤显祖人之为学，不日进则日退，独学无友，则孤陋而难成；久处一方，则习染而不自觉。顾炎武1 找出佣金高于薪金 60%的雇员。SELECT*FROM emp WHERE commsal*;2 找出部门 10 中所有经理和部门 20 中所有办事员的详细资料。SELECT*FROM emp WHERE deptno=10 AND JOB=MANAGER OR deptno=20 AND job=CLERK;3 找出部门 10 中所有经理，部门 20 中所有办事员以及既不是经理又不是办事员但其薪金大于或等 2000 的所有雇员的详细资料。SELECT*FROM emp WHERE deptno=10 AND JOB=MANAGER OR deptno=20 AND job=CLERK OR JOB NOT IN(MANAGER,CLERK)AND SAL=2000;SELECT*FROM emp WHERE deptno=10 AND JOB=MANAGER OR deptno=20 AND job=CLERK OR(JOBMANAGER AND JOBMANAGER AND SAL=2000);4 找出收取佣金的雇员的不同工作。SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL;5 找出不收取佣金或收取的佣金低于 300 的雇员。SELECT*FROM EMP WHERE COMM IS NULL OR COMM300;6 找出各月最后一天受雇的所有雇员。SELECT*FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE);-找出各月最后受雇的所有雇员 SELECT*FROM emp WHERE hiredate IN(SELECT maxh FROM(SELECT MAX(HIREDATE)maxh,EXTRACT(MONTH FROM hiredate)FROM EMP GROUP BY EXTRACT(MONTH FROM hiredate);7 找出晚于 26 年之前受雇的雇员。SELECT*FROM emp WHERE months_between(SYSDATE,hiredate)0;22 以年、月和日显示所有雇员的服务年限。SELECT HIREDATE,FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)AS Y,MOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE),12)AS M,MOD(FLOOR(SYSDATE-HIREDATE),30)AS D FROM EMP;23 列出至少有一个雇员的所有部门。SELECT*FROM DEPT WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP);24 列出薪金比“SMITH”多的所有雇员。SELECT*FROM EMP WHERE SAL(SELECT SAL FROM EMP WHERE ENAME=SMITH);25 列出所有雇员的姓名及其上级的姓名。百川东到海，何时复西归？少壮不尽力，老大徒伤悲。汉乐府长歌行志不强者智不达，言不信者行不果。墨翟SELECT AS empnm,AS mgrnm FROM emp ygb,emp sjb WHERE=;26 列出入职日期早于其直接上级的所有雇员。SELECT*FROM emp ygb WHERE hiredate1500;30 列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=SALES);31 列出薪金高于公司平均水平的所有雇员。SELECT*FROM EMP WHERE SAL(SELECT AVG(SAL)FROM EMP);32 列出与“SCOTT”从事相同工作的所有雇员。SELECT*FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=SCOTT)AND ENAMESCOTT;33 列出薪金等于在部门 30 工作的所有雇员的姓名和薪金。SELECT*FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30);34 列出薪金高于在部门 30 工作的所有雇员的姓名和薪金。SELECT*FROM EMP WHERE SAL ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);35 列出在每个部门工作的雇员的经理以及其他信息。SELECT A.*,B.*FROM DEPT A,EMP B WHERE=AND JOB=MANAGER;SELECT A.*,B.*FROM(SELECT*FROM EMP WHERE JOBMANAGER)A,(SELECT*FROM EMP WHERE JOB=MANAGER)B WHERE=AND=MANAGER ORDER BY;36 列出所有雇员的雇员名称、部门名称和薪金。SELECT ENAME,DNAME,SAL FROM DEPT A,EMP B WHERE=;37 列出从事同一种工作但属于不同部门的雇员的不同组合。丈夫志四方，有事先悬弧，焉能钧三江，终年守菰蒲。顾炎武人不知而不愠，不亦君子乎？论语SELECT*FROM emp ORDER BY job,deptno;38 列出分配有雇员数量的所有部门的详细信息，即使是分配有 0 个雇员。SELECT*FROM dept WHERE deptno IN(SELECT DISTINCT deptno FROM emp)UNION SELECT*FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp)39 列出各种类型工作的最低工资。SELECT job,MIN(sal)FROM emp GROUP BY job;40 列出各个部门的 MANAGER（经理）的最低薪金。SELECT MIN(sal)FROM emp WHERE job=MANAGER;41 列出按年薪排序的所有雇员的年薪。SELECT sal*12 AS y_sal FROM emp ORDER BY sal*12;42 列出薪金水平处于第四位的雇员。SELECT*FROM (SELECT a.*,row_number()over(ORDER BY sal DESC)AS rn FROM emp a)WHERE rn=4;43 查找 EMP 表中前 5 条记录 SELECT*FROM emp WHERE ROWNUM10;45 查找 EMP 表中薪水第 5 高的员工 SELECT*FROM(SELECT a.*,row_number()over(ORDER BY sal DESC)AS rn FROM emp a)WHERE rn=5;46 查找 EMP 表部门 30 中薪水第 3 的员工 SELECT*FROM(SELECT a.*,row_number()over(PARTITION BY deptno ORDER BY sal DESC)AS rn FROM emp a)WHERE rn=5 AND deptno=30;47 查找 EMP 表中每部门薪水第 3 的员工 SELECT*FROM(SELECT a.*,row_number()over(PARTITION BY deptno ORDER BY sal DESC)AS rn FROM emp a)WHERE rn=3;48 统计各部门的薪水总和.SELECT deptno,SUM(sal)AS sumsal FROM emp GROUP BY deptno;-每个部门员工和经理的详细信息:编号,姓名,薪水,入职日期,部门编号 SELECT AS eno,AS enm,AS ejob,AS esal,AS edate,AS edept,AS mno,AS mnm,AS mjob,AS msal,AS mdate,AS mdept FROM(SELECT*FROM emp WHERE jobMANAGER AND 先天下之忧而忧，后天下之乐而乐。范仲淹穷则独善其身，达则兼善天下。孟子jobPRESIDENT)a,(SELECT*FROM emp WHERE job=MANAGER)b WHERE=;-1列出至少有一个员工的所有部门。select*from dept where deptno in(select distinct deptno from emp);select count(*),deptno from emp group by deptno having count(*)1;-2列出薪金比“SMITH”多的所有员工。select*from emp where sal(select sal from emp where ename=SMITH);-3.列出所有员工的姓名及其直接上级的姓名 select ename,(select ename from emp where empno=from emp a;select,from emp a,emp b where=(+);-4列出受雇日期早于其直接上级的所有员工。select ename from emp e where hiredate 1500;select job,min(sal)msal from emp group by job having min(sal)1500;-8列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。select ename from emp where deptno=(select deptno from dept where dname=SALES);-9列出薪金高于公司平均薪金的所有员工。select ename from emp where sal(select avg(sal)from emp);-10列出与“SCOTT”从事相同工作的所有员工。select*from emp where job=(select job from emp where ename=SCOTT);-11列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。select ename,sal from emp where sal=any(select sal from emp where deptno=30);百学须先立志。朱熹非淡泊无以明志，非宁静无以致远。诸葛亮select*from emp where sal in(select sal from emp where deptno=30);-12列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。select ename,sal from emp where salall(select sal from emp where deptno=30);-13列出在每个部门工作的员工数量、平均工资和平均服务期限。select deptno,count(*),trunc(avg(sal+nvl(comm,0)avgsal,trunc(avg(sysdate-hiredate)avgday from emp group by deptno;-14列出所有员工的姓名、部门名称和工资。select ename,dname,(nvl(comm,0)+sal)as 工资 from emp,dept where=;-15列出从事同一种工作但属于不同部门的员工的一种组合。select distinct,from emp a,emp b where=and!=order by;-16列出所有部门的详细信息和部门人数。select a.*,(select count(*)from emp where deptno=as 人数 from dept a;-17列出各种工作的最低工资。select job,min(sal+nvl(comm,0)from emp group by job;-18列出各个部门的 MANAGER（经理）的最低薪金。select deptno,min(sal)from emp where job=MANAGER group by deptno;-19列出所有员工的年工资,按年薪从低到高排序。select empno,ename,(sal+nvl(comm,0)*12 as 年薪 from emp order by 年薪;
