Oracle 多表连接子查询

sjfgenzqvs

　　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
)
);