数据库原理中SQL语句实验指导书及答案Word版Word文档格式.docx
《数据库原理中SQL语句实验指导书及答案Word版Word文档格式.docx》由会员分享，可在线阅读，更多相关《数据库原理中SQL语句实验指导书及答案Word版Word文档格式.docx（18页珍藏版）》请在冰豆网上搜索。
张向丽
200515007
王芳
200515008
王民生
25
MA
200515009
王小民
200515010
李晨
22
200515011
张毅
WM
200515012
杨磊
EN
200515013
200515014
张丰毅
200515015
李蕾
21
200515016
刘社
CM
200515017
刘星耀
200515018
李贵
200515019
林自许
200515020
马翔
200515021
刘峰
200515022
牛站强
200515023
李婷婷
200515024
严丽
200515025
朱小鸥
30
课程信息表（course）
Cno
Cname
Cpno
Ccredit
1
数据库
5
4
2
数学
3
信息系统
操作系统
6
数据结构
7
数据处理
PASCAL语言
8
大学英语
9
计算机网络
10
人工智能
选课信息表（sc）
Grade
75
85
53
86
74
58
84
46
89
65
72
76
96
62
54
70
Null
insertintostudent.dbo.scvalues（'
20051501'
'
1'
75）
insertintostudent.dbo.coursevalues（'
数据库'
5'
4）
insertintostudent.dbo.studentvalues（'
201015001'
赵菁菁'
女'
23,'
CS'
）
2.修改CS系姓名为“李勇”的学生姓名为“李咏”；
updatestudent
setSname='
李咏'
whereSname='
李勇'
3.修改课程“数据处理”的学分为3学分；
3.updatecourse
setCcredit=3
whereCname='
数据处理'
4.将选修课程“1”的同学成绩加5分；
4.updatesc
setGrade=Grade+5
whereCno='
5.将选修课程“大学英语”的同学成绩加5分；
updatesc
setGrade=Grade+5
fromcourse,sc
wherecourse.Cno=sc.Cnoandcourse.Cname='
大学英语'
6.将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；
王丹丹'
Ssex='
Sage=20,Sdept='
MA'
whereSno='
200515010'
7.修改借书证号为2005001的学生记录重新设置：
名字为王婧婧、专业为信息管理、借书量增加5本；
（因为无些相关的数据表帮无法实现）
8.删除数据表student中无专业的学生记录；
delete
fromstudent
whereSdeptisnull
9.删除数据表student中计算机系年龄大于25的男同学的记录；
whereSsex='
男'
andSage>
25andSdept='
10.删除数据表course中学分低于1学分的课程信息；
fromcourse
whereCcredit<
实验五：
数据库单表查询
1.掌握SELECT语句的基本语法和查询条件表示方法；
2.掌握查询条件表达式和使用方法；
3.掌握GROUPBY子句的作用和使用方法；
4.掌握HAVING子句的作用和使用方法；
5.掌握ORDERBY子句的作用和使用方法。
1.了解数据库查询；
2.了解数据库查询的实现方式；
3.完成实验报告；
以数据库原理实验4数据库中数据为基础，请使用T-SQL语句实现以下操作：
1.列出所有不姓刘的所有学生；
1.select*
whereSnamenotlike'
刘%'
2.列出姓“沈”且全名为3个汉字的学生；
select*
fromstudent1
whereSnamelike'
沈__'
3.显示在1985年以后出生的学生的基本信息；
whereYEAR（GETDATE（））-Sage>
1985
4.按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：
性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”；
select性别=
case
whenSsex='
then'
男生'
女生'
else'
条件不明'
end,Sno学号,Sname码,Sage年龄,Sdept院系
5.查询出课程名含有“数据”字串的所有课程基本信息；
whereCnamelike'
%数据%'
7.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
6.selectSno,Sname,Ssex,Sage,Sdept
whereSnolike'
_______[1,2,3,4,9][1,2,3,4,9]%'
8.列出选修了‘1’课程的学生，按成绩的降序排列；
selectstudent.*,sc.*
fromstudent,sc
wherestudent.Sno=sc.Snoandsc.Cno='
orderbyGradeDESC
9.列出同时选修“1”号课程和“2”号课程的所有学生的学号；
selectSno
fromsc
whereCno='
andSnoin（
selectSno
2'
10.列出课程表中全部信息，按先修课的升序排列；
orderbyCpnoAsc
11.列出年龄超过平均值的所有学生名单，按年龄的降序显示；
whereSage>
（
selectAVG（Sage）
orderbySageDESC
12.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
selectSno学号,Sname姓名,Ssex性别,YEAR（GETDATE（））-Sage出生年份,Sdept所在院系
orderbyYEAR（GETDATE（））-Sage
13.按照院系降序显示所有学生的“院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：
院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；
selectSdept=
case
whenSdept='
then'
计算机系'
IS'
信息系'
数学系'
EN'
外语系'
CM'
中医系'
WM'
西医系'
end,Sno,Sname,Ssex,Sage
orderbySdeptDESC
14.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>
=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
selectSdept,院系规模=
whenCOUNT（Sno）>
=5then'
规模很大'
=4then'
规模一般'
=2then'
规模稍小'
规模很小'
end
whereSdeptisnotNull
groupbySdept
15.按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
selectSno,Cno,Grade
fromsc
whereGradebetween70and80
orderbyCno,GradeDESC
16.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
selectcount（*）学生总人数,AVG（Sage）平均年龄
17.显示选修的课程数大于3的各个学生的选修课程数；
selectSno学号,COUNT（Sno）选修课程数
groupbySno
havingCOUNT（*）>
=3
18.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
usestudent
selectCno'
课程号'
COUNT（*）'
总人数'
MAX（Grade）'
最高分'
MIN（Grade）'
最低分'
AVG（Grade）'
平均分'
groupbyCno
orderbyCnodesc
19.显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；
selectSno'
学号'
平均成绩'
fromsc
havingAVG（Grade）>
selectAVG（Grade）
200515001'
20.显示选修各个课程的及格的人数、及格比率；
selectCno'
COUNT（*）'
及格人数'
cast（cast（COUNT（casewhenGrade>
=60then1end）asfloat）/COUNT（*）ASfloat
（1））'
及格率'
groupbyCno
21.显示选修课程数最多的学号及选修课程数最少的学号；
选修课程数'
havingCOUNT（Cno）>
=all
（selectCOUNT（*）
groupbySno
union
havingCOUNT（Cno）<
22.显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
selectSdept,COUNT（casewhenSsex='
then1end）'
女生人数'
COUNT（casewhenSsex='
男生人数'
groupbySdept,Ssex
23.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
selectSno学号,AVG（Grade）平均成绩
havingCOUNT（casewhenGrade<
60then1end）>
=2
实验六：
数据库综合查询
2.掌握查询条件种类和表示方法；
3.掌握连接查询的表示及使用；
4.掌握嵌套查询的表示及使用；
5.了解集合查询的表示及使用。
1.了解SELECT语句的基本语法格式和执行方法；
2.了解连接查询的表示及使用；
3.了解嵌套查询的表示及使用；
4.了解集合查询的表示及使用；
5.完成实验报告；
以数据库原理实验5数据为基础，请使用T-SQL语句实现进行以下操作：
1.查询以‘数据_’开头,且倒数第3个字符为‘结’的课程的详细情况；
whereCnoin（
selectCno
数据\_%'
ESCAPE'
\'
2.查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
selectSname,student.Sno,sc.Cno,Cname
fromsc,course,student
wheresc.Cno=course.Cnoandstudent.Sno=sc.SnoandSnamelike'
_阳'
3.列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
selectstudent.Sno,Sname,Sdept,sc.Cno,Grade
fromstudent,sc,course
wherestudent.Sno=sc.Snoandsc.Cno=course.CnoandCnamein（'
数学'
4.查询缺少成绩的所有学生的详细情况;
Selectstudent.*
Wherestudent.Sno=sc.SnoandGradeisnull
5.查询与‘张力’（假设姓名唯一）年龄不同的所有学生的信息；
whereSage!
=（
selectSage
张力'
6.查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；
selectstudent.Sno,Sname,AVG（Grade）
wherestudent.Sno=sc.Sno
groupbystudent.Sno,Sname
whereSno=（
7.按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。
其中已修学分为考试已经及格的课程学分之和；
selectstudent.Sno,Sname,Sdept,SUM（Ccredit）总学分
wherestudent.Sno=sc.Snoandcourse.Cno=sc.CnoandGrade>
=60
groupbystudent.Sno,Sname,Sdept
8.列出只选修一门课程的学生的学号、姓名、院系及成绩；
selectstudent.Sno,Sname,Sdept,sum（Grade）成绩
wherestudent.Sno=sc.Sno
havingCOUNT（*）=1
9.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；
selectstudent.Sno,Sname,Cno
wherestudent.Sno=sc.Snoandstudent.Snoin（
selectstudent.Sno
wherestudent.Sno=sc.SnoandCnoin（
whereSnoin（
10.只选修“数据库”和“数据结构”两门课程的学生的基本信息；
selectstudent.Sno,Sname
wherestudent.Sno=sc.Snoandsc.Cno=course.CnoandCname='
andstudent.Snoin（
selectsc.Sno
fromsc,course
wheresc.Cno=course.CnoandCname='
数据结构'
）andstudent.Snonotin（
selectSno
fromsc,course
wheresc.Cno=course.CnoandCnamenotin（'
11.至少选修“数据库”或“数据结构”课程的学生的基本信息；
fromsc,student,course
wherestudent.Sno=sc.Snoandcourse.Cno=sc.CnoandCname='
andstudent.Snoin（
whereCno=（
fromcourse
whereCname='
12.列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；
selectsc.Cno,Cname,sc.Sno,Sname,Grade
fromcourseleftouterjoinscon（course.Cno=sc.Cno）,student
13.查询只被一名学生选修的课程的课程号、课程名；
selectcourse.Cno,Cname
wheresc.Cno=course.Cno
groupbycourse.Cno,Cname
14.检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；
selectSno,Sname
whereexists（
张向东'
andexists（
fromscy
wherey.Sno=student.Snoandy.Cno=sc.Cno
15.使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；
whereSnoin（
16.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
selectSname,Sdept,Sage
whe
升级会员 