山东大学威海第九章实验代码《数据库原理与应用教程 SQL Server》(尹志宇 郭晴 著).doc

第九章数据库查询实验报告一 实验准备建立学生表use 教学库gocreate table 学生(学生号 char(7) primary key,姓名 char(8) not null,性别 char(2) ,年龄 int,) 建立选课表use 教学库gocreate table 选课(学生号 char(8) ,课程号 char(16) not null,成绩 intconstraint pk_js primary key(学生号,课程号) )建立课程表use 教学库gocreate table 课程(课程号 char(8) primary key,课程名 char(16) not null,学分 int)学生表插入数据use 教学库insert into 学生 (学生号,姓名,性别,年龄) values (0101001,王华,男,19),(0101002,王通,男,20),(0101003,王菲,女,21), (0101004,冷芳,男,21), (0101005,王密,女,20),(0101006,刘浩,女,19),(0101007,孙艳,女,19)选课表中插入数据use 教学库insert into 选课 (学生号,课程号,成绩) values (0101001,C001,91), (0101001,C002,89), (0101001,C003,93), (0101001,C004,85), (0101002,C002,81), (0101002,C003,84), (0101002,C005,83), (0101002,C004,89), (0101003,C004,91), (0101004,C001,85), (0101004,C003,81), (0101005,C001,94), (0101005,C003,86), (0101006,C001,87), (0101007,C003,91), (0101007,C005,95), (0101007,C004,90)课程表中插入数据use 教学库insert into 课程 (课程号,课程名,学分) values (C001,计算机网络,3), (C002,计算机文化基础,3), (C003,专业英语,2), (C004,信号与系统,4), (C005,数字信号处理,4)二1 针对教学库中的三个表完成下面问题（1）统计有学生选修的课程门数use 教学库select COUNT (distinct 课程号) 课程门数from 选课结果（2）求选修C004课程学生的平均年龄use 教学库select avg (年龄) 平均年龄from 学生,选课where 学生.学生号=选课.学生号 and 课程号=C004(3)求学分为3的每一门课程的学生平均成绩use 教学库select 课程.课程名,avg(成绩) 平均成绩from 课程,选课where 课程.课程号=选课.课程号 and 学分=3group by 课程.课程名(4)统计每一门课程的学生选修人数，超过3人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，而人数相同按降序排列use 教学库select 课程号,COUNT(*) as 选修人数 from 选课group by 课程号having count(*)3order by COUNT(*) desc,课程号(5)检索学号比王菲同学大，而年龄比他小的学生姓名use 教学库select 姓名 from 学生where 学生号(select 学生号 from 学生 where 姓名=王菲)and 年龄(select avg(年龄) 平均年龄 from 学生 where 性别=女)(9) 求年龄所有大于女同学的平均年龄的男生的姓名和年龄use 教学库select 姓名,年龄 from 学生where 性别=男 and 年龄 all (select avg(年龄) 平均年龄 from 学生 where 性别=女)(10)检索所有比王华年龄大的学生的姓名，年龄，性别use 教学库select 姓名,年龄 性别 from 学生where 年龄 (select 年龄 from 学生 where 姓名=王华)(11)检索选修课程C001的学生的成绩中最高的学生的学号use 教学库select 学生号 from 选课where 课程号=C001 and 成绩=(select MAX(成绩) from 选课where 课程号=C001)(12)检索学生姓名及其所选修课程课程的课程号和成绩use 教学库select 姓名,课程号,成绩 from 学生,选课where 学生.学生号=选课.学生号(13)检索选修4门以上课程的学生总成绩（不统计不及格课程），并要求按总成绩降序排名use 教学库select 学生号,sum(成绩) 总成绩 from 选课where 成绩=60group by 学生号having count(*)=4order by sum(成绩) desc2利用控制流语句，查询学号为0101001的学生的各科成绩，如果没有这个学生的成绩，就显示“此学生无成绩”use 教学库if exists (select 成绩 from 选课 where 学生号=0101001)select 课程号,成绩 from 选课 where 学生号=0101001else print 此学生无成绩3 用函数实现：求某个专业选修了某门课的学生人数因为3.4题所以加了专业这一例USE 教学库GOCREATE FUNCTION studen_fun(a char(8),b char(10) RETURNS tableASRETURN(SELECT COUNT(选课.学生号) AS 选修人数FROM 学生,选课,课程WHERE 学生.专业=a AND 课程.课程名=b AND 学生.学生号=选课.学生号 AND 选课.课程号=课程.课程号)USE 教学库GOSELECT 选修人数FROM studen_fun (计算机,信号系统)4用函数实现：查询某个专业所有学生所选的每门课的平均成绩USE 教学库GOCREATE FUNCTION average(m char(7) RETURNS tableASRETURN (SELECT 课程.课程名,AVG(成绩) AS 平均成绩FROM 选课,课程,学生WHERE 专业=m AND 选课.课程号=课程.课程号 AND 学生.学生号=选课.学生号GROUP BY 课程名)USE 教学库GO SELECT 课程名,平均成绩FROM averag(通信)5 针对仓库库存中的商品表，查询商品的价格等级，商品号、商品名和价格等级（单价1000元以内的为低价商品，价格10003000为中等价位商品，价格大于3000为高价商品）use 仓库库存1Gocreate table 商品(商品号 char(10) primary key,商品名 char(20) not null,商品价格 int ,)use 仓库库存insert into 商品 (商品号,商品名,商品价格) values (0101001,电脑桌,1000), (0101002,电脑椅,1200), (0101003,海尔电脑,2200), (0101004,华硕电脑,3200), (0101005,联想电脑,4000), (0101006,戴尔电脑,4200), (0101007,索尼电脑,1100)use 仓库库存select 商品号,商品名,casewhen 商品价格1000 then 低价商品wh