SQL语句练习

原创 2017年01月04日 01:24:33

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(+)
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

数据库sql语句练习

  • 2015-02-06 10:52
  • 42KB
  • 下载

SQL语句练习册

  • 2015-09-25 17:35
  • 3.86MB
  • 下载

SQL语句练习

选择部门30中的所有员工 列出所有办事员(CLERK)的姓名,编号和部门编号 找出佣金高于薪金的员工 找出佣金高于薪金的60%的员工 找出部门10中所有经理(MANAGER)和部门20中所有办事员...

sql语句练习 及 答案

SQL语句练习系统

  • 2008-04-08 10:43
  • 235KB
  • 下载

SQL语句练习-使用函数

SQL支持利用函数来处理数据,这些函数也都是非常高效的。不过,对于不同的DBMS,它们可能支持不同的函数,这会给SQL代码的可移植性带来问题。

SQL语句练习提高

  • 2013-06-13 22:10
  • 7.29MB
  • 下载

SQL查询语句练习-山威

  • 2012-05-05 13:42
  • 22KB
  • 下载

Oracle sql语句练习(转)

1. 创建TableSpace、用户及给用户分派权限       可以通过Oracle客户端工具Enterprise Manager Console连接相应数据库,通过图形化操作来完成,这里我记录通...

sql语句练习

  • 2015-11-03 16:49
  • 14KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)