Oracle 多表连接子查询

softgoto

1.求部门中哪些人薪水最高：
select ename,sal
from emp join
(
    select max(sal) max_sal, deptno
    from emp
    group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);

2.求部门平均薪水的等级：
select deptno, avg_sal, grade
from
(
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);

3.求部门平均的薪水等级：
select deptno, avg(grade)
from (
    select deptno, ename, grade
    from emp join salgrade s
    on emp.sal between s.losal and s.hisal
) t
group by deptno;

4.求哪些人是经理人：
select ename from emp where empno in (select distinct mgr from emp);

5.不准用聚集函数，求薪水的最高值：
select distinct sal
from emp
where sal not in
(
    select distinct e1.sal
    from emp e1 join emp e2 on (e1.sal < e2.sal)
);

6.求平均薪水最高部门的部门编号：
select deptno, avg_sal
from (
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) where avg_sal = (
    select max(avg_sal)
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    )
);

7.求平均薪水最高部门的部门名称：
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg_sal)
        from (
            select deptno, avg(sal) avg_sal
            from emp
            group by deptno
        )
    )
);

#：聚集函数嵌套（最多只能嵌套两层）：
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg(sal))
        from emp
        group by deptno
    )
);

8.求平均薪水的等级最低部门的部门名称：
select dname, t1.deptno, grade, avg_sal
from (
    select deptno, grade, avg_sal
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) t join salgrade s
    on (t.avg_sal between s.losal and s.hisal)
)t1 join dept
on (t1.deptno = dept.deptno)
where t1.grade = (
    select min(grade)
    from (
        select deptno, grade, avg_sal
        from (
            select deptno, avg(sal) avg_sal
            from emp
            group by deptno
        ) t join salgrade s
        on(t.avg_sal between s.losal and s.hisal)
    )
);

#：简化办法-创建视图：
1)create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal
from (
      select deptno, avg(sal) avg_sal
      from emp
      group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);

2)select dname, t1.deptno, grade, avg_sal
from v$_dept_avg_sal_info t1 join dept
on (t1.deptno = dept.deptno)
where t1.grade =
(
      select min(grade)
      from v$_dept_avg_sal_info t2
);

3)若权限不足，则：
conn sys/密码 as sysdba;
grant create table, creat view to scott;

9.求比普通员工的最高薪水还要高的经理人名称：
select ename
from emp
where empno in (
        select distinct mgr
        from emp
        where mgr is not null
    ) and sal > (
        select max(sal)
        from emp
        where empno not in (
            select distinct mgr
            from emp
            where mgr is not null
        )
    );

10. 求薪水最高的前5名雇员
select ename, sal
from
(
    select ename, sal
    from emp
    order by sal desc
) where rownum <= 5;

11. 求薪水最高的第六到第十名雇员
select ename, sal
from (
    select ename, sal, rownum r
    from (
        select ename, sal
        from emp
        order by sal desc
    )
) where r >=6 and r <= 10;

12.有三个表格S,C,SC
S(SNO, SNAME) 代表（学号，姓名）
C(CNO, CNAM, CTEACHER)代表（课号，课名，教师）
SC(SNO, CNO, SCGRADE)代表（学号，课号，课号成绩）

问题：
⑴.找出没选过“liming”老师课程的所有学生姓名
select sname
from s join sc on (s.sno = sc.sno)
       join c on (c.cno = sc.cno)
where c.cteacher <> 'liming';

⑵.列出两门以上（含两门）不及格学生姓名及平均成绩
select sname
from s
where sno in (
    select sno
    from sc
    where scgrade < 60
    group by sno
    having count(*) >= 2
);

⑶.既学过1号课程又学过2号课程所有学生的姓名
select sno from sc where cno = 1 and
select sname
from s
where sno in (
    select sno
    from sc
    where cno = 1 and sno in (
        select sno from sc where cno = 2
    )
);