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语句练习题(+sql脚本文件)

欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来,用它写博客,将会带来全新的体验哦: Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和...
  • qq_22075041
  • qq_22075041
  • 2017年04月02日 17:42
  • 4816

常见sql语句练习

-- 教师表 CREATE TABLE teacher(     tno INT NOT NULL PRIMARY KEY,     tname VARCHAR(20) NOT NULL ); I...
  • Oliver_wq
  • Oliver_wq
  • 2017年10月28日 11:47
  • 1262

经典的SQL语句,适合新手练习使用(二)-提升

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b froma where 1(仅用于SQlServer) 法二:select to...
  • u012496929
  • u012496929
  • 2015年07月26日 22:23
  • 1577

【MySQL】经典数据库SQL语句编写练习题——SQL语句扫盲

【MySQL】数据库原理复习——SQL语言
  • WolfOfSiberian
  • WolfOfSiberian
  • 2014年09月17日 19:23
  • 13978

Oracle sql语句练习(转)

1. 创建TableSpace、用户及给用户分派权限       可以通过Oracle客户端工具Enterprise Manager Console连接相应数据库,通过图形化操作来完成,这里我记录通...
  • topviewers
  • topviewers
  • 2011年09月12日 19:50
  • 5787

经典的SQL语句,适合新手练习使用(一)

1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sqlserver -...
  • u012496929
  • u012496929
  • 2015年07月26日 21:28
  • 766

MySQL环境搭建及SQL查询语句练习

前言这个学期有数据库原理这门课,其中很重要的一个部分就是SQL查询语句的练习。但是自己手写的查询语句如果不测试的话是不知道是否正确的,而也不可能指望老师会检查每个人的查询语句的正确性。所以只能自力更生...
  • qq379548839
  • qq379548839
  • 2016年10月07日 21:39
  • 449

练习用基础SQL语句

本文语句大部分SQL语句来自《数据库系统概论》(第四版)王珊&萨师煊 ,是我们上课用的教材,感觉很不错,总结了其中第三章的SQL语句,比较实用,希望对大家有帮助。总结如下,可以用来学习基础的SQL语句...
  • u013700340
  • u013700340
  • 2014年12月20日 23:14
  • 1049

(SQL) 第八章 用SQL语句操作数据

/****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [StudentNo] ,[Log...
  • qq_36074150
  • qq_36074150
  • 2016年11月20日 20:00
  • 360

MySQL查询语句练习题(面试时可能会遇到哦!)

Sutdent表的定义 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增...
  • wanghongbiao1993
  • wanghongbiao1993
  • 2016年12月12日 21:12
  • 4118
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL语句练习
举报原因:
原因补充:

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