06-Oracle学习_练习-子查询和连接查询

淡淡的烟草味﹌ 2021-11-02 11:24 353阅读 0赞

# 1, 求部门中那些人的薪水最高 #

select e.deptno, e.ename, e.sal  
from (select deptno, max(sal) max\_sal from emp group by deptno) t  
join emp e on (e.deptno = t.deptno and e.sal = t.max\_sal)

# 2, 求部门平均薪水的等级 #

I , 先求出部门的平均薪水  
II, 再求其平均薪水的等级  
select d.deptno, avg\_sal, grade from dept d  
join (select deptno, avg(sal) avg\_sal from emp group by deptno) t  
on (t.deptno = d.deptno)  
join salgrade s on (t.avg\_sal between losal and hisal)

# 3, 求部门平均的薪水等级 #

I , 先求出所有人的薪水等级  
II, 再求薪水等级的平均值  
select e.deptno, avg(s.grade) from emp e  
join salgrade s on (e.sal between s.losal and s.hisal)  
group by e.deptno

# 4, 雇员中有哪些人是经理人 #

① in  
I , 先求出所有经理人的编号mgr  
II, 在根据e1.empno = e2.mgr 求出经理人  
select ename  
from emp  
where empno in (select distinct mgr from emp)  
② 连接查询  
select e1.empno, e1.ename, e2.empno, e2.mgr  
from emp e1  
join emp e2 on (e1.empno = e2.mgr)

# 5, 不用组函数, 求薪水的最高值 #

①  
左表薪水最高的那条记录 匹配不到, 对应的e2.sal = null  
select e1.ename, e1.sal from emp e1  
left join emp e2 on (e1.sal < e2.sal)  
where e2.sal is null  
and e1.sal is not null  
② 先求出能比别人小的薪水集合, 不在该集合的薪水即为最大薪水  
select ename, sal from emp  
where sal not in  
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))

# 6, 平均薪水最高的部门的编号 #

I , 先求部门平均薪水  
II , 再求部门平均薪水的最大值  
III, 最后求出部门平均薪水最大值的部门编号  
①  
select deptno, avg(sal) from emp  
group by deptno  
having avg(sal) =  
(  
select max(avg\_sal)  
from  
(select deptno, avg(sal) avg\_sal from emp group by deptno) t  
)  
②  
select deptno, avg(sal) from emp  
group by deptno  
having avg(sal) =  
(select max(avg(sal)) from emp group by deptno)

# 7, 平均薪水的等级最低的部门的部门名称 #

I , 先求部门的平均薪水  
II , 再求其平均薪水的等级  
III, 进而求出最低的等级  
VI , 最后求部门名称  
select d.deptno, d.dname, tt.grade, tt.avg\_sal from dept d  
join  
\-- 部门平均薪水对应的等级  
(select t.deptno, t.avg\_sal, s.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)  
) tt  
on (d.deptno = tt.deptno)  
where tt.grade =  
(  
\-- 最低的薪水等级  
select min(tt.grade) from  
\-- 部门平均薪水对应的等级  
(select t.deptno, t.avg\_sal, s.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)  
) tt  
)

# 8, 求部门经理人中平均薪水最低的部门名称 #

select d.dname, t2.avg\_sal from  
(  
\-- 经理人按部门分组的平均薪水  
select t.deptno, avg(sal) avg\_sal from  
( -- 经理人的编号,工资,部门  
select distinct e1.empno, e1.sal, e1.deptno from emp e1  
join emp e2 on e1.empno = e2.mgr  
) t  
group by t.deptno  
) t2  
join dept d on t2.deptno = d.deptno  
where t2.avg\_sal =  
(  
select min(t2.avg\_sal) from  
( -- 经理人按部门分组的平均薪水  
select t.deptno, avg(sal) avg\_sal from  
( -- 经理人的编号,工资,部门  
select distinct e1.empno, e1.sal, e1.deptno from emp e1  
join emp e2 on e1.empno = e2.mgr  
) t  
group by t.deptno  
) t2  
)

# 9, 求比普通员工的最高薪水还高的经理人名称 #

select empno, ename, sal from emp  
where empno in (select distinct mgr from emp)  
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, 求薪水最高的第6到第10名雇员 #

select r, ename, sal from  
(  
select rownum r, ename, sal  
from (select ename, sal from emp order by sal desc) t1  
) t2  
where r >=6 and r <= 10

# 12, 比较效率 #

①select \* from emp where deptno = 10 and ename like '%A%';  
②select \* from emp where ename like '%A%' and deptno = 10;  
解:

第一个效率高,

# 13, 有三个表 #

S(SNO, SNAME) 学生表(学号, 姓名)  
C(CNO, CNAME, CTEACHER) 老师表(课号, 课名, 教师)  
SC(SNO, CNO, SCGRADE) 选课表(学号, 课号, 成绩)  
问题  
1, 找出没选过"黎明"老师的所有学生姓名  
select S.SNAME  
from S  
join SC on (S.SNO = SC.SNO)  
join C on (SC.CNO = C.CNO)  
where C.CTEACHER <> '黎明'  
  
  
2, 列出2门以上(含2门)不及格学生 姓名及平均成绩  
select S.SNAME, avg(SC.SCGRADE) avg\_grade  
from S  
join SC on (S.SNO = SC.SNO)  
where SC.SNO in  
( -- 2门及以上不及格的学生编号  
select SC.SNO  
from SC  
where SC.SCGRADE < 60  
group by SC.SNO  
having count(\*) >= 2  
)  
group by S.SNO, S.SNAME  
  
  
3, 既学过1号课程又学过2号课程所有学生的姓名  
select S.SNAME from S  
where S.SNO in  
(-- 学过 1 和 2 课程的学生编号  
select sc1.SNO  
from SC sc1  
join SC sc2 on (sc1.CNO = sc2.CNO)  
where sc1.CNO = 1 and sc2.CNO = 2  
)

# 14,一个简单的表TABLE 有100条以上的信息，其中包括： #

产品 颜色 数量  
产品1 红色 123  
产品1 蓝色 126  
产品2 蓝色 103  
产品2 红色 NULL  
产品2 红色 89  
产品1 红色 203  
。。。。。。。。。。。。  
请用SQL语句完成以下问题：  
(1) 按产品分类，将数据按下列方式进行统计显示  
产品 红色 蓝色  
(2) 按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：  
  
  
create table product  
(  
name varchar2(10),  
color varchar2(10),  
amount number(4)  
);  
insert into product values('产品1', '红色', 123);  
insert into product values('产品1', '蓝色', 126);  
insert into product values('产品2', '蓝色', 103);  
insert into product values('产品2', '红色', NULL);  
insert into product values('产品2', '红色', 89);  
insert into product values('产品1', '红色', 203);  
  
  
create view v$product  
as  
select name,  
sum(case when color = '红色' then amount else 0 end) red\_amount,  
sum(case when color = '蓝色' then amount else 0 end) blue\_amount  
from product  
group by name;  
  
  
select \* from v$product;  
  
  
NAME RED\_AMOUNT BLUE\_AMOUNT  
\----- ---------- -----------  
产品1 326 126  
产品2 89 103  
  
  
select name, (red\_amount - blue\_amount) difference\_amount  
from v$product  
where red\_amount > blue\_amount;  
  
  
NAME DIFFERENCE\_AMOUNT  
\----- -----------------  
产品1 200  
  
  
drop view v$product;