06-Oracle学习_练习-子查询 和 连接查询
来源:互联网 发布:24周胎儿发育正常数据 编辑:程序博客网 时间:2024/05/16 11:19
1, 求部门中那些人的薪水最高
select e.deptno, e.ename, e.salfrom (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, 雇员中有哪些人是经理人
① inI , 先求出所有经理人的编号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 empwhere 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, salfrom (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;
- 06-Oracle学习_练习-子查询 和 连接查询
- 05-Oracle学习_子查询 和 连接查询
- 【oracle学习】3.子查询和集合运算练习
- oracle中查询:子查询,连接查询
- oracle子查询和表连接
- oracle练习之 子查询
- 【从零开始学习Oracle数据库】(3)函数与子查询和连接查询
- 子查询和连接
- MySQL学习笔记-子查询和连接
- oracle 连接查询,子查询,分组函数
- pg学习_子查询
- 分组查询和子查询练习
- oracle学习笔记(6)_子查询
- Oracle学习笔记摘录4-----子查询和关联查询
- MySql学习 - 查询/子查询/连接查询/联合查询
- Oracle学习笔记 -- day05 多表查询、连接查询、子查询、分页、行转列、集合运算
- Oracle: 四、Oracle连接查询,子查询(相关子查询,嵌套子查询)
- Oracle数据库高级查询(四)子查询和连接查询的综合案例
- Cocos2d-x学习笔记(二)浅析动作类CCAction(1)
- MySQL的mysql_insert_id和LAST_INSERT_ID
- hufuman 编码实现(赫夫曼编码)
- java内部类了解
- Java 静态类
- 06-Oracle学习_练习-子查询 和 连接查询
- 嵌入式C开发人员的最好的0x10道笔试题
- Cocos2d-x学习笔记(三)浅析动作类CCAction(2)
- 一走进书房的QuickDove
- 前辈华为工作感悟
- WebService之Axis2系列教程(三)Axis2复合类型数据的传递
- [poj 2115]C Looooops[扩展欧几里德][模线性方程]
- 常用java date 方法集合
- u-boot中bootargs和bootcmd常用命令(下)