2018/12/20 oracle-sql练习

--查询比自己职位平均工资高的员工姓名、职位
--，部门名称，职位平均工资
select ename,sal,emp.deptno,avgsal
from emp,(select deptno,avg(sal) avgsal
          from emp
          group by deptno) xb
where emp.deptno=xb.deptno
and sal>avgsal;

--查询职位和经理同员工scott或blake完全相同的员工姓名、
--职位，不包括scott和blake本人
select ename,job,mgr
from emp
where (job,mgr) in(select job,mgr
                  from emp
                  where ename='SCOTT' or ename='BLAKE');
--查询不是经理的员工姓名
select ename from
emp
where ename not in (select distinct e.ename--子查询查询出是经理的员工姓名
from emp e join emp ee
on e.empno=ee.mgr);
--查询入职日期最早的前五名员工姓名，入职日期。
select ename,hiredate
from (select ename,hiredate
from emp
order by hiredate asc)
where rownum<=5;
--查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期
select ename,hiredate
from(select ename,hiredate
from emp join dept
on emp.deptno=dept.deptno
where loc='CHICAGO'
order by hiredate asc)
where rownum<=2;
--分页查询
--emp总共14行，如果每页显示5行，要查询第三页
--（介于11行到15行之间，由于emp只有14行只显示到14行）
select b.*
from (select ename,deptno,rownum rn
from emp
where rownum<=3*5) b
where rn>2*5;
--第二种写法
select b.*
from (select ename,deptno,rownum rn
from emp) b
where rn <=3*5 and rn>2*5;
--第一种效率更高，如果子查询中一共有100行，要查询第三页，
--第一种方法先查询出rownum小于等于15的所有行然后在外部查询出rownum大于10的行
--第二种方法先在子查询里查询出所有的100行，再在外部查询查询出rownum大于10小于等于15的行

--按照每页显示5条记录，分别查询第1页，第2页，第3页信息，
--要求显示员工姓名，入职日期、部门名称
--第1页
select b.*
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno=dept.deptno
where rownum<=1*5) b
where rn>0*5       
--第2页
select b.*
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno=dept.deptno
where rownum<=2*5) b
where rn>1*5
--第3页
select b.*
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno=dept.deptno
where rownum<=3*5) b
where rn>2*5  

--按照每页显示5条记录，分别查询工资最高的第1页，第2页
--第3页信息，要求显示员工姓名，入职日期、部门名称、工资
--第1页
select b.*,rownum
from(select a.*,rownum rn
    from (select ename,hiredate,dname,sal
          from emp e join dept d
          on e.deptno=d.deptno
          order by sal desc
          ) a
    where rownum<=1*5
    ) b
where rn>0*5
--第2页
select b.*,rownum
from(select a.*,rownum rn
    from (select ename,hiredate,dname,sal
          from emp e join dept d
          on e.deptno=d.deptno
          order by sal desc
          ) a
    where rownum<=2*5
    ) b
where rn>1*5
--第3页
select b.*,rownum
from(select a.*,rownum rn
    from (select ename,hiredate,dname,sal
          from emp e join dept d
          on e.deptno=d.deptno
          order by sal desc
          ) a
    where rownum<=3*5
    ) b
where rn>2*5

--查询工资高于编号为7782的员工工资，并且和7369编号的员工
--从事相同工作的员工编号、姓名以及工资
select ename,empno,sal
from emp
where sal>(select sal
           from emp
           where empno=7782)
           and
      job=(select job
           from emp
           where empno=7369)
--查询工资最高的员工和工资
select ename,sal
from emp
where sal=(select max(sal)
from emp);
--查询部门最低工资高于10号部门最低工资的部门编号、
--名称以及部门最低工资
select d.deptno,dname,min(sal)
from emp e join dept d
on d.deptno=e.deptno
group by d.deptno,dname
having min(sal)>(select min(sal)
                 from emp
                 where deptno=10)

--查询员工工资为其他部门最低工资的员工的编号及姓名及工资
select empno,ename,sal
from emp
where sal in (select min(sal)
             from emp
             group by deptno)

--显示经理室king的员工姓名，工资
select ename,sal
from emp
where mgr=(select empno
           from emp
           where ename='KING')
--显示比SMITH入职时间晚的员工姓名，工资，入职时间
 select ename,sal,hiredate
 from emp
 where hiredate>(select hiredate
                  from emp
                  where ename='SMITH');
--使用子查询的方式查询那些职员在NEWYORK工作
select ename
from emp e join dept d
on e.deptno=d.deptno
where d.loc=(select loc
             from dept
             where loc='NEW YORK')
--写一个查询 显示和员工SMITH工作在同一个部门的员工姓名，
--雇佣如期，查询结果中排出SMITH
select ename,hiredate
from emp e join dept d
on e.deptno=d.deptno
where ename <> 'SMITH' and
loc=(select loc
     from emp e join dept d
     on e.deptno=d.deptno
     where ename='SMITH')
--写一个查询显示其工资比全体职员平均工资高的员工编号、
--姓名
select empno,ename,sal
from emp
where sal>(select avg(sal)
           from emp)
--写一个查询显示其上级领导是king的员工姓名，工资
select ename,sal
from emp
where mgr=(select empno
          from emp
          where ename='KING')
--显示所有工作在RESEARCH部门的员工姓名，职位
select ename,job
from emp e join dept d
on e.deptno=d.deptno
where dname='RESEARCH'
--查询每个部门的部门编号、平均工资，要求部门的
--平均工资高于部门20的平均工资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>(select avg(sal)
                from emp
                group by deptno
                having deptno=20)
--查询大于自己部门平均工资的员工姓名，工资，所在部门
--平均工资，高于部门平均工资的额度
select ename,sal,dname,d.deptno
from emp e join (select deptno,avg(sal) avgsal
                from emp
                group by deptno) b
on e.deptno=b.deptno
join dept d
on b.deptno=d.deptno
where sal>avgsal;
--列出至少有一个雇员的所有部门
select deptno,count(*)
from emp
group by deptno
having count(*)>=1
--列出薪金比'SMITH'多的所有雇员
select ename,sal
from emp
where sal>(select sal
           from emp
           where ename='SMITH')
--找员工姓名和直接上级的名字
select e.ename 上级名字,ee.ename 员工名字
from emp e join emp ee
on e.empno=ee.mgr
--显示部门名称和人数
select dname,count(*)
from emp e join dept d
on e.deptno=d.deptno
group by dname;
--显示每个部门的最高工资的员工
select ename,sal,e.deptno
from emp e,(select deptno,max(sal) maxavg
            from emp
            group by deptno) b

where e.deptno=b.deptno and e.sal=b.maxavg

--显示出和员工号7369部门相同的员工姓名，工资
select ename,sal
from emp
where deptno=(select deptno
              from emp
              where empno=7369)
--显示出和姓名中包含'W'的员工相同部门的员工姓名
select ename
from emp
where deptno in (select deptno
              from emp
              where ename like '%W%')

--显示出工资大于平均工资的员工姓名，工资
select ename,sal
from emp
where sal>(select round(avg(sal),2)
           from emp)
--显示出工资大于本部门平均工资的员工姓名，工资
select e.ename,e.sal
from emp e join (select deptno,avg(sal) avgsal
                 from emp
                 group by deptno) b
on e.deptno=b.deptno
where sal>avgsal;
--显示每位经理管理员工的最低工资，及最低工资的员工姓名
select a.empno,b.salary
from emp a join (select e.empno,min(ee.sal) salary
from emp e join emp ee
on e.empno=ee.mgr
group by e.empno) b
on a.mgr=b.empno
where sal=salary;

--显示比工资最高的员工入职时间晚的员工姓名，入职时间
select ename,hiredate
from emp
where hiredate>(select hiredate
                from emp
                where sal=(select max(sal)
                          from emp))
--显示出平均工资最高的部门平均工资及部门名称
select d.dname,avgsal
from(select *
      from(select deptno,avg(sal) avgsal
            from emp
            group by deptno
            order by avgsal desc)
      where rownum=1) b join dept d
on b.deptno=d.deptno;