关闭

SQL语句练习

标签: sql
24人阅读 评论(0) 收藏 举报
分类:

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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:107次
    • 积分:23
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档