美文网首页
高级子查询

高级子查询

作者: 孙竞博 | 来源:发表于2017-12-13 09:16 被阅读0次

    练习1.1 查询比所在职位平均工资高的员工姓名,职位。

    select ename, job
    from emp e
    where sal>
    (select avg(sal) 
    from emp 
    where e.job=job);
    

    1.2 查询工资为其部门最低工资的员工编号,姓名,工资。

    select empno, ename, sal 
    from emp outer
    where sal in 
    (select min(sal)
    from emp 
    where outer.deptno=deptno);
    

    练习2.1 查询所有雇员编号,名字和部门名字。

    select empno, ename, (select dname
    from dept 
    where e.deptno=deptno)
    from emp e
    

    2.2 查询哪些员工是经理。

    select e.ename
    from emp e
    where e.empno in (select mgr 
    from emp 
    where e.empno=mgr) 
    

    2.3 查询哪些员工不是经理。

    select e.ename 
    from emp e
    where e.empno not in(select mgr 
    from emp 
    where e.empno=mgr)
    

    2.4 查询每个部门工资最低的两个员工编号,姓名,工资。

    select empno, ename, sal 
    from emp 
    where sal in (select min(sal)
    from 
    (select ename, sal, deptno
    from emp 
    where (ename,sal) not in
    (select e.ename, e.sal 
    from emp e
    where e.sal in(select min(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno))) b
    group by b.deptno)
    
    union 
    
    select e.empno, e.ename, e.sal 
    from emp e
    where e.sal in(select min(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno);
    
    select e.empno, e.ename, e.sal 
    from emp e
    where (select count(*)
    from emp 
    where deptno=e.deptno and sal<e.sal)<2
    

    练习3.1 (exists)列出至少有一个雇员的所有部门名称。

    select d.dname
    from dept d
    where exists(select count(*)
    from emp 
    where deptno=d.deptno 
    group by deptno 
    having count(*)>=1)
    

    练习3.2 列出一个雇员都没有的所有部门名称。

    select d.dname 
    from dept d 
    where not exists(select count(*)
    from emp 
    where deptno=d.deptno 
    group by deptno 
    having count(*)>=1)
    

    课后作业

    1. 查询薪水多于他所在部门平均薪水的雇员名字,部门号。
    select e.ename, e.deptno
    from emp e
    where e.sal>
    (select avg(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno)
    
    1. 查询员工姓名和直接上级的名字。
    select e.ename, (select ename 
    from emp 
    where empno=e.mgr)
    from emp e
    
    1. 查询每个部门工资最高的员工姓名,工资。
    select e.ename, e.sal 
    from emp e
    where e.sal in (select max(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno )
    
    1. 查询每个部门工资前两名高的员工姓名,工资。
    • 结果多出一个
    select ename, sal 
    from emp 
    where sal in (select max(sal)
    from 
    (select ename, sal, deptno
    from emp 
    where (ename,sal) not in
    (select e.ename, e.sal 
    from emp e
    where e.sal in(select max(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno))) b
    group by b.deptno)
    
    union 
    
    select e.ename, e.sal 
    from emp e
    where e.sal in(select max(sal)
    from emp 
    where deptno=e.deptno 
    group by deptno);
    
    select e.ename, e.sal, e.deptno
    from emp e
    where (select count(*)
    from emp 
    where deptno=e.deptno and sal>e.sal)<2
    

    相关文章

      网友评论

          本文标题:高级子查询

          本文链接:https://www.haomeiwen.com/subject/nvtaixtx.html