SQL语句练习

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(+)