ORACLE数据库习题(有答案)

--1.列出至少有一个雇员的所有部门。 SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP); --2.列出薪金比“SMITH”多的所有雇员。 SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH'); --3.列出所有雇员的姓名及其上级的姓名。 SELECT ygb.ename AS empName,sjb.ename AS mgrName FROM emp ygb,emp sjb WHERE ygb.mgr=sjb.empno; --4.列出入职日期早于其直接上级的所有雇员。 SELECT * FROM emp ygb WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE sjb.empno=ygb.mgr); --5.列出所有部门的名称和这些部门的雇员编号及姓名（没有雇员的部门包含在内） SELECT dname,ename FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno; --6.列出所有JOB为“CLERK”（办事员）的员工姓名及其部门名称 SELECT ename,dname FROM dept a,emp b WHERE a.deptno=b.deptno AND job='CLERK'; --7.列出各种类型的最低薪金，并使最低薪金大于。 SELECT job,MIN(sal) 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 * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP); --10.列出与“SCOTT”从事相同工作的所有雇员。 SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT'; --11.列出薪金等于部门中任何一个雇员薪金的员工姓名和薪金 SELECT ENAME,SAL FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30); SELECT ENAME,SAL FROM EMP WHERE SAL =ANY(SELECT SAL FROM EMP WHERE DEPTNO=30); --12.列出薪金高于部门中所有雇员薪金的员工姓名和薪金 SELECT * FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE DEPTNO=30); --13.列出所有部门的部门名称、地点以及该部门中雇员的数量 SELECT DNAME,DEPTNO,LOC, (SELECT COUNT(*) FROM EMP E WHERE E.DEPTNO=DEPT.DEPTNO) AS TOTAL FROM DEPT; --14.列出各种类别工作的最低工资 SELECT job,MIN(sal) FROM emp GROUP BY job; --15.列出各个部门的MANAGER（经理）的最低薪金。 SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY DEPTNO; --1. 查询所有部门员工的编号、姓名、月薪及奖金。 select empno,ename,sal,comm from emp where deptno=10; --2. 查询员工WHITE的员工编号。 select empno from emp where ename='WHITE'; --3. 查询所有员工的编号及收入。（提示：收入=月薪+奖金） select empno 编号,sal+nvl(comm,0) as 收入from emp; --4. 查询所有员工的编号、姓名、工种及其工龄，按工龄降序排序，工龄相同时按编号升序排序。 Select empno,ename,job,trunc(months_between(sysdate,hiredate)/12) 工龄from emp order by 工龄desc,empno asc; --5. 统计各部门的员工人数及工种类型数。 select deptno,count(*) 人数,count(distinct job) 工种类型数from emp group by deptno; --6. 查询部门人数多于人的部门编号。 select deptno,count(*) from emp group by deptno having count(*) > 3; --7. 统计各部门各种工作类别的最低月薪，显示最低薪金大于的记录。 select deptno,job,min(sal) from emp group by deptno,job having min(sal)>1500; --8. 统计、及部门员工平均工资。 select deptno,coung(*) from emp where deptno in (10,20,30) group by deptno; --9. 查询工资水平在至间的各部门员工人数。 select deptno,count(*) from emp where sal between 2000 and 5000 group by deptno; --10. 查询公司最高领导人的员工编号及姓名。 select empno,ename from emp where mgr is null; --1、编写一查询，显示与Blake在同一部门工作的雇员的姓名和受雇日期，但是Blake不包含在结果内。 select ename,hiredate from emp where deptno in (select deptno from emp where lower(ename)='black') and lower(ename)!='black'; --2、创建一查询，显示工资大于平均工资水平的雇员的编号及姓名，输出结果按工资降序排列。 select empno,ename,sal from emp where sal>(select avg(sal) from emp) order by sal desc; --3、显示位置在Dallas的部门内的雇员的姓名、编号及工作。 select ename,empno,job from emp inner join dept on emp.deptno=dept.deptno where lower(loc)='dallas'; --4、显示被King直接管理的雇员的姓名及其工资。 select e2.ename,e2.sal from emp e1 inner join emp e2 on e1.empno= e2.mgr where lower(e1.ename)='king'; --5、显示在Sales部门工作的雇员的部门编号、姓名及工作。 select empno,ename,job from emp inner join dept on emp.deptno=dept.deptno where lower(dname)='sales'; --6、编写一查询，显示与能获得奖金的雇员的奖金和工资同时匹配的雇员的编号和工资。 select e2.empno,e2.sal,e2.comm from emp e1 inner join emp e2 on e1.sal= e2.sal and e1.comm=e2.comm where e1.empno!=e2.empno; --7、显示与工作在Dallas的雇员的工资及奖金同时匹配的雇员的姓名、部门名称及工资。 select e.ename,d.dname,e.sal from emp e inner join dept d on e.deptno=d.deptno where (sal,nvl(comm,0)) in (select sal,nvl(comm,0) from emp where deptno in (select deptno from dept where lower(loc)='dallas') ); --8、创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期及工资。 select ename,hiredate,sal from emp where (sal,nvl(comm,0)) in (select sal,nvl(comm,0) from emp where upper(ename)='SCOTT'); --9、显示比其上级管理员更早进入公司的雇员的姓名（Employee）、受雇日期（Emp Hiredate）--以及其上级管理员的姓名（Manager）及受雇日期（Mgr Hiredate）。

select w.ename Employee,w.hiredate \"Emp Hiredate\ m.ename Manager, m.hiredate \"Mgr Hiredate\"

from emp w inner join emp m on m.empno= w.mgr where m.hiredate>w.hiredate;

--1.列出至少有一个雇员的所有部门。 SELECT * FROM DEPT

WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP); --2.列出薪金比“SMITH”多的所有雇员。

SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH'); --3.列出所有雇员的姓名及其上级的姓名。

SELECT ygb.ename AS empName,sjb.ename AS mgrName FROM emp ygb,emp sjb WHERE ygb.mgr=sjb.empno; --4.列出入职日期早于其直接上级的所有雇员。 SELECT * FROM emp ygb

WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE sjb.empno=ygb.mgr); --5.列出所有部门的名称和这些部门的雇员编号及姓名（没有雇员的部门包含在内） SELECT dname,ename

FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno; --6.列出所有JOB为“CLERK”（办事员）的员工姓名及其部门名称 SELECT ename,dname FROM dept a,emp b

WHERE a.deptno=b.deptno AND job='CLERK'; --7.列出各种类型的最低薪金，并使最低薪金大于1500。 SELECT job,MIN(sal) 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 * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP); --10.列出与“SCOTT”从事相同工作的所有雇员。 SELECT * FROM EMP

WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT'; --11.列出薪金等于30部门中任何一个雇员薪金员工姓名和薪金 SELECT ENAME,SAL FROM EMP

WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30); --12.列出薪金高于30部门中所有雇员薪金的员工姓名和薪金 SELECT * FROM EMP

WHERE SAL >ALL(SELECT SAL FROM EMP WHERE DEPTNO=30); --13.列出所有部门的部门名称、地点以及该部门中雇员的数量 SELECT DNAME,DEPTNO,LOC,

(SELECT COUNT(*) FROM EMP E WHERE E.DEPTNO=DEPT.DEPTNO) AS TOTAL FROM DEPT; --14.列出各种类别工作的最低工资 SELECT job,MIN(sal) FROM emp GROUP BY job; --15.列出各个部门的MANAGER（经理）的最低薪金。 SELECT MIN(sal) FROM emp

WHERE job='MANAGER' GROUP BY DEPTNO;