7个SQL语句小练习
涉及内容较广, 有一定难度, 值得反复琢磨.
有表如下:
班级表 t_class
c_no | c_name | s_qty |
---|---|---|
001 | 小学生班 | 20 |
学生表 t_student
s_no | s_name | c_no | gen_rmk | birth_date(yyyyMMdd) |
---|---|---|---|---|
no1 | 小妹妹 | 001 | 2 | 20000101 |
保险表
s_no | buy_date(yyyyMM) | qty |
---|---|---|
no1 | 小妹妹 | 17 |
1查询某个班级中男生和女生的个数
格式:
班级 | 性别 | 个数 |
---|---|---|
A | 男 | 10 |
B | 女 | 19 |
C | 男 | 13 |
select a.c_name,decode(b.gen_rmk,'1','男','2','女','不明') as 性别,
count(1) as qty
from t_class a , t_student b
where a.c_no = b.c_no
group by a.c_name,b.gen_rmk
order by c_name
2查询班级中男生比率最级
班级 | 男生个数 | 女生个数 | 男生比 |
---|---|---|---|
A | 6 | 4 | 0.4 |
这里得分3层来查询:
第1层, 把每个班的男生和女生分开
select a.c_name,
decode(b.gen_rmk, '1', 1, 0) as 男生,
decode(b.gen_rmk, '2', 1, 0) as 女生,
decode(b.gen_rmk, '0', 1, 0) as 不明,
1 学生数
from t_class a, t_student b
where a.c_no = b.c_no
第2层, 对第1层进行汇总
select c_name, sum(男生), sum(女生), sum(不明), sum(男生) / sum(学生数) as p
from (select a.c_name,
decode(b.gen_rmk, '1', 1, 0) as 男生,
decode(b.gen_rmk, '2', 1, 0) as 女生,
decode(b.gen_rmk, '0', 1, 0) as 不明,
1 学生数
from t_class a, t_student b
where a.c_no = b.c_no)
group by c_name
第3层, 根据第2层获取答案
select *
from (select c_name, sum(男生), sum(女生), sum(不明), sum(男生) / sum(学生数) as p
from (select a.c_name,
decode(b.gen_rmk, '1', 1, 0) as 男生,
decode(b.gen_rmk, '2', 1, 0) as 女生,
decode(b.gen_rmk, '0', 1, 0) as 不明,
1 学生数
from t_class a, t_student b
where a.c_no = b.c_no)
group by c_name)
where p = (select max(p)
from (select c_name, sum(男生), sum(女生), sum(不明), sum(男生) / sum(学生数) as p
from (select a.c_name,
decode(b.gen_rmk, '1', 1, 0) as 男生,
decode(b.gen_rmk, '2', 1, 0) as 女生,
decode(b.gen_rmk, '0', 1, 0) as 不明,
1 学生数
from t_class a, t_student b
where a.c_no = b.c_no)
group by c_name))
3查询90后学生最多的班级
格式:
班级 | 90后个数 |
---|---|
A | 9 |
这里主要是看对sql函数是否熟悉
select * from (
select a.c_name,count(1) qty
from t_class a, t_student b
where a.c_no = b.c_no
and birth_date> to_date('199001','yyyyMM')
group by a.c_name
) where qty =(
select max(qty) from(
select a.c_name,count(1) qty
from t_class a, t_student b
where a.c_no = b.c_no
and birth_date> to_date('199001','yyyyMM')
group by a.c_name)
)
4查询所有班级的学生,没有学生的班级也要显示
格式:
班级 | 学生个数 |
---|---|
A | 10 |
B | 0 |
C | 19 |
关键还是全连接以及decode函数的妙用
select a.c_name,sum(decode(b.s_name,null,0,1))
from t_class a,t_student b
where a.c_no = b.c_no(+)
group by a.c_name
5更新班级表,根据学生表信息,更新班级表中的学生个数
外层套里层, 批量修改
update t_class a set s_qty = (select count(1) from t_student b where b.c_no = a.c_no)
6删除没有学生的班级
又一个函数
delete t_class a where not exists (select * from t_student b where b.c_no = a.c_no)
7最后一次购买的保险
班级 | 学号 | 姓名 | 最后购买保险日期 | 最后购买金额 | 班级平均购买金额 |
---|---|---|---|---|---|
A | A017 | TOM | 201607 | 100 | 80 |
A | A018 | JACK | 从未购买 | 80 |
分层解答:
第1层 获取每个班级的平均消费数额
select aa.c_no,aa.c_name,avgqty from t_class aa,
(select a.c_no,a.c_name, avg(qty) avgqty from t_class a,t_student b,t_instruance c
where a.c_no=b.c_no
and c.s_no =b.s_no
group by a.c_no,a.c_name) bb
where aa.c_no = bb.c_no(+)
第2层 获取最后一次购买保险的学生
select * from t_instruance where s_no || buy_date in (
select s_no || max(buy_date) from t_instruance
group by s_no))aa,t_student bb
where bb.s_no = aa.s_no
第3层 综合前面两层, 数据汇总
select aaa.c_no,c_name,s_name,buy_date,qty,avgqty from (
select aa.c_no,aa.c_name,avgqty from t_class aa,
(select a.c_no,a.c_name, avg(qty) avgqty from t_class a,t_student b,t_instruance c
where a.c_no=b.c_no
and c.s_no =b.s_no
group by a.c_no,a.c_name) bb
where aa.c_no = bb.c_no(+)
) aaa,
(
select bb.c_no,aa.s_no,bb.s_name,buy_date,qty from
(select * from t_instruance where s_no || buy_date in (
select s_no || max(buy_date) from t_instruance
group by s_no))aa,t_student bb
where bb.s_no = aa.s_no
) bbb where aaa.c_no = bbb.c_no(+)