Oracle经典20题

--1.	列出至少有一个员工的所有部门。
select * from dept where deptno in(
  select distinct deptno from emp
);

--2.	列出薪金比“SMITH”多的所有员工。
select * from emp where sal > (
  select distinct sal  from emp where ename = 'SMITH'
);

--3.	列出所有员工的姓名及其直接上级的姓名。
select a.ename ,b.ename from emp a,emp b where a.mgr = b.empno;

--4.	列出受雇日期早于其直接上级的所有员工。
select a.* from emp a,emp b where a.mgr = b.empno and a.hiredate < b.hiredate;
select a.ename ,b.ename from emp a join emp b on a.mgr = b.empno where a.hiredate < b.hiredate;

--5.	列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select dept.dname ,emp.* from dept left join emp on dept.deptno = emp.deptno ;

--6.	列出所有“CLERK”(办事员)的姓名及其部门名称。
select ename ,dname from emp,dept where emp.deptno=dept.deptno and job='CLERK'  ;
 
--7.	列出最低薪金大于1500的各种工作。
select job 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  a.ename from emp a,emp b where b.job=a.job and b.ename='SCOTT';
select ename from emp where  job=(
  select job from emp where ename='SCOTT')  and ename!='SCOTT' ;

--11.	列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename ,sal from emp where  deptno!=30 and  sal in (
  select sal from emp where deptno=30);

--12.	列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename ,sal from emp where deptno!=30 and sal >(
select min(sal) from emp where deptno=30);

--13.	列出在每个部门工作的员工数量、平均工资和平均服务期限。trunc(99.9)==99截断函数
select count(*) ,trunc(avg(sal)),trunc(avg(sysdate-hiredate)/365) from emp group by deptno;

--14.	列出所有员工的姓名、部门名称和工资。
select emp.ename,dept.dname,emp.sal from emp,dept where emp.deptno = dept.deptno;

--15.	列出所有部门的详细信息和部门人数。
select a.* ,b.num from 
(select * from  dept) a left join 
(select deptno,count(*) num from emp group by deptno) b on a.deptno=b.deptno;

select dept.deptno,dept.dname,count(*) from dept,emp 
where dept.deptno=emp.deptno group by dept.deptno,dept.dname ;

--16.	列出各种工作的最低工资。
select job,min(sal) from emp group by job;

--17.	列出各个部门的MANAGER(经理)的最低薪金。
select deptno, min(sal) from emp where job='MANAGER' group by deptno;

--18.	列出所有员工的年工资,按年薪从低到高排序。
select ename,12*(sal) from emp order by sal asc;

--19. 求各种工作工资最低的员工
select deptno, min(sal) from emp  group by deptno;

--20.列出各种工作工资前3名的员工
select * from ( 
  select empno,ename,sal,job,dense_rank() over(partition by job order by sal desc) rank from emp ) a   where a.rank<=3   order by job;