1,列出至少有三个员工的所有部门。
select a.dname,a.loc,b.counts from dept a join (select deptno,count(*) counts from emp group by deptno having count(*) >=3 ) b on a.deptno=b.deptno;
2,列出薪水比"SMITH"多的所有员工。
select ename,empno,sal from emp where sal > (select sal from emp where ename=‘SMITH‘);
3,列出所有员工的姓名及其直接上级的姓名。
select a.ename emp_name,b.ename manager_name from emp a join emp b on a.mgr=b.empno;
4,列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。
select a.empno,a.ename emp_name,c.dname from emp a join emp b on a.mgr=b.empno join dept c on a.deptno=c.deptno where a.hiredate < b.hiredate ;
5,列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select a.deptno,a.dname,b.ename from dept a left join emp b on a.deptno=b.deptno;
6,列出所有job=CLERK的姓名及其部门名称，部门的人数。
select a.ename,b.dname,t.counts from emp a join dept b on a.deptno=b.deptno join (select b.dname,count(a.deptno) counts from emp a join dept b on a.deptno=b.deptno group by a.deptno, b.dname) t on t.dname=b.dname where a.job=‘CLERK‘;
7,列出最低薪水大于1500的各种工作及此从事此工作的全部雇员人数。
select job,count(*) from emp group by job having min(sal) > 1500;
8,列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno in (select deptno from dept where dname=‘SALES‘);
select e.ename from emp e
9,列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，公司的工资等级。
select a.ename emp_name,b.ename leader_name,c.dname,d.grade from emp a join emp b on a.mgr=b.empno join dept c on c.deptno=a.deptno join salgrade d on a.sal between d.losal and d.hisal where a.sal> (select round(avg(sal),2) avg_sal from emp);
10,列出与"SCOTT"从事相同工作的所有员工及部门名称。
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno where a.job in (select job from emp where ename=‘SCOTT‘);
11,列出薪水等于部门号=30中员工的薪水的所有员工的姓名和薪水。
select ename,sal,deptno from emp where deptno=30;
12,列出薪水高于在部门号=30工作的所有员工的薪水的员工姓名和薪水,部门名称。
select ename,sal from emp where sal > all (select sal from emp where deptno=30);
13,列出在每个部门工作的员工数量,平均工资和平均服务期限。
//第一种方法
select a.dname,a.counts,b.avg_salary,c.avg_year from (select b.dname,count(a.deptno) counts from emp a right join dept b on a.deptno=b.deptno group by a.deptno, b.dname) a join (select b.dname,round(avg(nvl(a.sal,0)),2) avg_salary from emp a right join dept b on a.deptno=b.deptno group by b.dname) b on a.dname=b.dname join (select b.dname,round(nvl(avg(sysdate-hiredate)/365,0)) avg_year from emp a right join dept b on a.deptno=b.deptno group by b.dname) c on a.dname=c.dname;
//第二种方法
select a.deptno,b.dname,count(a.deptno),round(avg(a.sal),2) avg_sal,round(nvl(avg(sysdate-a.hiredate)/365,0)) avg_year from emp a join dept b on a.deptno=b.deptno group by a.deptno,b.dname;
14,列出所有员工的姓名,部门名称和工资。
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno;
15,列出所有部门的详细信息和部门人数。
select a.*,b.counts
from dept a
join (select deptno,count(*) counts from emp group by deptno) b
on a.deptno=b.deptno;
16,列出各种工作的最低工资及从事此工作的雇员姓名。
SCOTT@orcl 07-APR-14> select ename,b.min_salary,b.job from emp a join (select job,min(sal) min_salary from emp group by job) b on a.sal=b.min_salary;
17,列出各个部门的MANAGER（经理）的最低薪水。
select min(sal) min_salary from (select ename,sal from emp where mgr in (select empno from emp where mgr is null));
18,列出所有员工的年工资，按年薪(salary+comm)从高到底排序。
SCOTT@orcl 07-APR-14> select ename,sal*12+sal total_salary from emp order by total_salary desc ;
select ename,sal*12+sal total_salary from emp order by total_salary desc ;
//列出每个部门的平均薪金，(sal+comm)
select deptno,avg(sal*12+nvl(comm,0)*12) avg_sal from emp group by deptno;
19,查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select a.ename emp_name,b.ename manager_name,b.sal manager_salary from emp a join emp b on a.mgr=b.empno where b.sal > 3000;
20,求出部门名称中，带‘S’字符的部门员工的部门名称,工资合计,部门人数
select b.deptno,sum(nvl(sal,0)) sum_salary,count(a.deptno) counts from emp a right join (select deptno,dname from dept where dname like ‘%S%‘) b on a.deptno=b.deptno group by b.deptno;
21.求平均薪水最高的部门，显示部门名称和部门平均薪水。
select b.deptno,sum(nvl(sal,0)) sum_salary,count(a.deptno) counts from emp a right join (select deptno,dname from dept where dname like ‘%S%‘) b on a.deptno=b.deptno group by b.deptno;
21.求平均薪水最高的部门，显示部门名称和部门平均薪水。
select c.dname,b.avg_sal from
(select round(avg(sal),2) avg_sal,deptno from emp group by deptno) a
join (select max(round(avg(sal),2)) avg_sal from emp group by deptno) b
on a.avg_sal = b.avg_sal
join dept c on c.deptno=a.deptno;
//如果部门不一样，平均薪水并列第一下边这种方法就不合适了。
select a.dname,b.avg_sal
from dept a join (select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc) b
on a.deptno=b.deptno
where rownum=1 ;