Oracle 多表连接子查询

本文精选了多个复杂的SQL查询案例,包括查找部门最高薪水员工、计算部门平均薪水等级、确定平均薪水最高的部门等。同时,还提供了求解特定业务问题的SQL语句,如找出未选某老师课程的学生姓名、列出两门以上不及格的学生及其平均成绩等。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值