国家开放大学《数据库应用技术》形考任务4参考答案

国家开放大学《数据库应用技术》形考任务4参考答案实验目的:通过使用SSMS工具，练习对数据库中的数据表进行单表査询、多表连接査询、子音询。对数据表中的数据进行更改和删除等操作。实验要求:将相关的SQL语句和运行结果的截屏保存在文件中，或填写在下面的实验报告中，并通过网络提交。实验内容:使用SSMS工具，在“可用数据库”中选中Students数据库，完成如下实验，1.单表查询。写出实现如下查询的SQL语句。（1）查询学生选课表中的全部数据。（2）查询计算机系的学生的姓名、年龄。（3）查询成绩在70~80分的学生的学号、课程号和成绩（4）查询计算机系年龄在18~20岁男生的姓名、年龄。（5）查询C001课程的考试成绩最高分。（6）查询计算机系学生的最大年龄和最小年龄。（7）统计各系的学生人数。（8）统计每门课程的选课人数和考试成绩最高分。（9）统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。（10）查询总成绩超过200分的学生，要求列出其学号和总成绩。（11）查询选课门数超过2门的学生的学号、平均成绩和选课门数。2.多表连接查询。写出实现如下音询的SQL语句。（12）查询选了C002课程的学生的姓名和所在系。（13）查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果（14）查询计算机系男生选修了“数据库基础”的学生的姓名和成绩。（15）查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。（16）查询哪些课程没有人选修，要求列出课程号和课程名，（17）查询计算机系没有选课的学生，列出学生的姓名。3.使用TOP和CASE的查询。写出实现如下查询的SQL语句。（18）列出“数据库基础”课程考试成绩前三名的学生的。参考答案：一、单表查询查询学生选课表中的全部数据。SQL语句：SELECT*FROM学生选课表名称；（假设学生选课表名为StudentCourse）查询计算机系的学生的姓名、年龄。SQL语句：SELECT姓名，年龄FROM学生表名称WHERE所在系='计算机系'；（假设学生表名为Student）查询成绩在70～80分的学生的学号、课程号和成绩。SQL语句：SELECT学号，课程号，成绩FROM学生选课表名称WHERE成绩BETWEEN70AND80；查询计算机系年龄在18～20岁男生的姓名、年龄。SQL语句：SELECT姓名，年龄FROM学生表名称WHERE所在系='计算机系'AND年龄BETWEEN18AND20AND性别='男'；查询C001课程的考试成绩最高分。SQL语句：SELECTMAX(成绩)AS最高分FROM学生选课表名称WHERE课程号='C001'；查询计算机系学生的最大年龄和最小年龄。SQL语句：SELECTMAX(年龄)AS最大年龄，MIN(年龄)AS最小年龄FROM学生表名称WHERE所在系='计算机系'；统计各系的学生人数。SQL语句：SELECT所在系，COUNT(*)AS学生人数FROM学生表名称GROUPBY所在系；统计每门课程的选课人数和考试成绩最高分。SQL语句：SELECT课程号，COUNT(*)AS选课人数，MAX(成绩)AS最高分FROM学生选课表名称GROUPBY课程号；统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。SQL语句：SELECT学号，COUNT(*)AS选课门数，SUM(成绩)AS总成绩FROM学生选课表名称GROUPBY学号ORDERBY选课门数ASC；10.查询总成绩超过200分的学生，要求列出其学号和总成绩。SQL语句：SELECT学号，SUM(成绩)AS总成绩FROM学生选课表名称GROUPBY学号HAVINGSUM(成绩)>200；11.查询选课门数超过2门的学生的学号、平均成绩和选课门数。SQL语句：SELECT学号，AVG(成绩)AS平均成绩，COUNT(*)AS选课门数FROM学生选课表名称GROUPBY学号HAVINGCOUNT(*)>2；二、多表连接查询12.查询选了C002课程的学生的姓名和所在系。SQL语句：SELECTs.姓名，s.所在系FROM学生表名称sJOIN学生选课表名称scONs.学号=sc.学号WHEREsc.课程号='C002'；13.查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。SQL语句：SELECTs.姓名，sc.课程号，sc.成绩FROM学生表名称sJOIN学生选课表名称scONs.学号=sc.学号WHEREsc.成绩>80ORDERBYsc.成绩DESC；14.查询计算机系男生选修了“数据库基础”的学生的姓名和成绩。SQL语句：SELECTs.姓名，sc.成绩FROM学生表名称sJOIN学生选课表名称scONs.学号=sc.学号JOIN课程表名称cONsc.课程号=c.课程号WHEREs.所在系='计算机系'ANDs.性别='男'ANDc.课程名='数据库基础'；（假设存在课程表Course）15.查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。SQL语句：SELECTs.学号，s.姓名，sc.课程号，sc.成绩FROM学生表名称sLEFTJOIN学生选课表名称scONs.学号=sc.学号；16.查询哪些课程没有人选修，要求列出课程号和课程名。SQL语句：SELECTc.课程号，c.课程名FROM课程表名称cLEFTJOIN学生选课表名称scONc.课程号=sc.课程号WHEREsc.学号ISNULL；17.查询计算机系没有选课的学生，列出学生的姓名。SQL语句：SELECTs.姓名FROM学生表名称sWHEREs.所在系='计算机系'ANDs.学号NOTIN(SELECT学号FROM学生选课表名称)；三、使用TOP和CASE的查询18.列出“数据库基础”课程考试成绩前三名的学生的