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

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;