20180508下午H5209讲解-查询加强练习02-地信161162

–使用studentdb数据库，完成以下查询训练
restore database [studentdb] from disk=‘d:\studentdb.bak’
go
use studentdb
go

–练习使用case when then else end结构

–1 查询 计算机科学专业的 2005级的 2班的 所有的 学生信息
select * from 专业 order by 3

select 学生.*
from 学生
join 专业 on 学生.专业=专业.专业代码
where
专业名称=‘计算机科学’
and 年级=2005
and 班序号=2

–用模糊匹配
select *
from 学生
join 专业 on 学生.专业=专业.专业代码
where
专业名称 like ‘%计算机科学%’
and 年级=2005
and 班序号=2

–1 编写代码的过程
–2 编码风格
–3 数据源根据理力争要显示的列和要加载条件的列来判断

–2 查询 电商系 2005级 所有专业的 所有2班的 所有的学生信息
select *
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科=‘电商系’
and 年级=2005
and 班序号=2

select *
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005
and 班序号=2

–给出过滤条件时，要根据生活经验来给出。精确条件VS模糊条件

–3 查询 按专业简称统计 电商系 2005级 专业数量
select *
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005

select count(专业简称) as 数量
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005
–去重
select all 专业简称
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005

select distinct 专业简称
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005

–去重之后的统计
select 专业简称,count(distinct 专业简称) as 数量
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005
group by 专业简称

–4 给出第3题查出的那些专业的专业简称
select 专业简称,count(distinct 专业简称) as 数量
from 学生
join 专业 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 年级=2005
group by 专业简称

–思考，统计的结果正不正确？如何改进?提示：distinct关键字去重

–5 查询 2005级 电商系各 专业下 各班 的人数
–要求给出专业名称，年级，专业简称，班序号,班简名,班内人数
–值得思考的代码
select 专业名称,学生.年级,专业简称,学生.班序号,班简名,count(*) as 班内人数
from
专业
join 班 on
专业.专业代码=班.所属专业
join 学生 on 学生.专业=专业.专业代码
where
所属系科 like ‘%电%商%’
and 学生.年级=2005
group by
专业名称,学生.年级,专业简称,学生.班序号,班简名

–查找出来的结果有问题。年级是混乱的。原因是三张表在连接时，没有按约束来连接
–因为学生与班之间，约束请查看企业管理器下的约束
–重点：学生与班之间的那条关系线上的参考列是三列，而不是一列
–回忆一下，为什么再我们没有去重复时，专业的统计结果是12个？
–小结：表与表这间的连接条件一定要注意，与外键参考密切相关
–修改正确
select 专业名称,学生.年级,专业简称,学生.班序号,班简名,count(*) as 班内人数
from
专业
join 学生 on 学生.专业=专业.专业代码
join 班 on
学生.专业=班.所属专业
and 学生.年级=班.年级
and 学生.班序号=班.班序号
where
所属系科 like ‘%电%商%’
and 学生.年级=2005
group by
专业名称,学生.年级,专业简称,学生.班序号,班简名

–6 查看 2005级 电子商务系的 工程管理专业的 1班的 学生的 成绩
–要求列出专业名称，年级，班简名，学生姓名，课程名称，总评成绩
select
专业名称,学生.年级,班简名,姓名 as 学生姓名,课程名称,总评成绩
from
专业
join 学生 on 专业.专业代码=学生.专业
join 成绩 on 学生.学号=成绩.学号
join 课程 on 成绩.课程代码=课程.课程代码
join 班 on 学生.专业=班.所属专业
and 学生.年级=班.年级
and 学生.班序号=班.班序号
where
学生.年级=2005
and 所属系科 like ‘%电%商%’
and 专业名称 like ‘%工程管理%’
and 学生.班序号=1

–7 查看 2005级 电子商务系的 工程管理专业的 1班的 学生的 C语言课程的 成绩
–要求列出专业名称，年级，班简名，学生姓名，课程名称，总评成绩
select
专业名称,学生.年级,班简名,姓名 as 学生姓名,课程名称,总评成绩
from
专业
join 学生 on 专业.专业代码=学生.专业
join 成绩 on 学生.学号=成绩.学号
join 课程 on 成绩.课程代码=课程.课程代码
join 班 on 学生.专业=班.所属专业
and 学生.年级=班.年级
and 学生.班序号=班.班序号
where
学生.年级=2005
and 所属系科 like ‘%电%商%’
and 专业名称 like ‘%工程管理%’
and 学生.班序号=1
and 课程名称 like ‘%C语言%’

–8 查看 2005级 电子商务系的 工程管理专业的 1班的 学生的 C语言课程的 成绩
–要求列出专业名称，年级，班简名，学生姓名，课程名称，总评成绩，等级
–等级的要求是：
– 分数 100-90为优秀,
– 89-80为良好,
– 79-70为中等，
– 69-60为一般，
– 59-50为差，
– 50以下为渣
select
专业名称
,学生.年级
,班简名
,姓名 as 学生姓名
,课程名称
,总评成绩
,(
case
when 总评成绩>=90 and 总评成绩<=100 then ‘优秀’
when 总评成绩>=80 and 总评成绩<=89 then ‘良好’
when 总评成绩>=70 and 总评成绩<=79 then ‘中等’
when 总评成绩>=60 and 总评成绩<=69 then ‘一般’
when 总评成绩>=50 and 总评成绩<=59 then ‘差’
else ‘渣’
end
)as 等级
from
专业
join 学生 on 专业.专业代码=学生.专业
join 成绩 on 学生.学号=成绩.学号
join 课程 on 成绩.课程代码=课程.课程代码
join 班 on 学生.专业=班.所属专业
and 学生.年级=班.年级
and 学生.班序号=班.班序号
where
学生.年级=2005
and 所属系科 like ‘%电%商%’
and 专业名称 like ‘%工程管理%’
and 学生.班序号=1
and 课程名称 like ‘%C语言%’
order by 总评成绩 desc