有关oracle中scott用户下表的基本查询（一）

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 ;

有关oracle中scott用户下表的基本查询（一）,古老的榕树,5-wow.com