实验三到五数据库实验整合.docx
《实验三到五数据库实验整合.docx》由会员分享，可在线阅读，更多相关《实验三到五数据库实验整合.docx（11页珍藏版）》请在冰豆网上搜索。
实验三到五数据库实验整合
实验三：
管理SQLServer表数据
1.修改student表中姓名为“李勇”的学生姓名为“李咏”；
UPDATEStudentSETSname='李咏'WHERESname='李勇'
2.修改课程“数据处理”的学分为3学分；
UPDATECourseSETCcredit=3whereCno=6
3.将选修课程“1”的同学成绩加5分；
UPDATEscSETGrade=Grade+5whereCno=1
4.将选修课程“大学英语”的同学成绩加5分；
UPDATEscSETGrade=Grade+5whereCno=8
5.将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；
UPDATEStudentSETSname='王丹丹',Ssex='女',Sage=20,Sdept='MA'WHERESno='200515010'
6.向student表中增加记录：
（200515026，王婧婧、女、21，CS）；
INSERTINTOStudentVALUES（'200515026','王婧婧','女',21,'CS'）
7.删除数据表student中无系别的学生记录；
deletefromstudentwhereSdeptisnull
8.删除数据表student中计算机系年龄大于25的男同学的记录；
DeletefromstudentwhereSdept='CS'andSsex='男'andSage>25
9.删除数据表course中学分低于1学分的课程信息；
DeletefromcoursewhereCcredit<1
实验四：
数据库单表查询
1）查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。
selectSnum,Sname,Sex,Sage
fromstudent
whereDnum='D2';
2） 查询学号为S006的学生的姓名。
selectSname
fromstudent
whereSnum='S006';
3）查询成绩在60-85之间的学生的学号。
selectSnum
fromsc
whereScorebetween60and85;
4）查询所有姓王，并且姓名为两个字的学生的信息。
select*
fromstudent
whereSnamelike'王_';
5）查询选修课程号为‘C1’且成绩非空的学生学号和成绩，成绩按150分制输出（每个成绩乘以系数1.5）。
selectSnum,Score=Score*1.5
fromsc
whereCnum='C1'andScoreisnotnull;
6）查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重复。
selectdistinctSnum
fromsc
whereCnumisnotnull;
7）查询选修课程‘C1’的学生学号和成绩，结果按成绩的升序排列，如果成绩相同则按学号的降序排列。
selectSnum,Score
fromsc
whereCnum='C1'
orderbyScore,Snumdesc;
（二）、以数据库原理实验3数据库中数据为基础，请使用T-SQL语句实现以下操作：
1.列出所有不姓刘的所有学生；
selectSname
fromstudent
whereSnamelike'[^刘]%';
2.列出姓“沈”且全名为3个汉字的学生；
selectSname
fromstudent
whereSnamelike'沈__';
3.显示在1985年以后出生的学生的基本信息；
select*
fromstudent
whereSage<=2013-1985;
4.按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：
性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”；
selectSsex=
case
whenSsex='男'then'男生'
whenSsex='女'then'女生'
else'条件不明'
end
Sno,Sname,Sage,Sdept
fromstudent;
5.查询出课程名含有“数据”字串的所有课程基本信息；
select*
fromcourse
whereCnamelike'%数据%';
6.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
selectSno,Sname,Ssex,Sage,Sdept
fromstudent
whereSnolike'_______[1,2,3,4,9][1,2,3,4,9]%'
7.列出选修了‘1’课程的学生，按成绩的降序排列；
selectstudent.*,sc.*
fromstudent,sc
wherestudent.Sno=sc.Snoandsc.Cno='1'
orderbyGradeDESC
8.列出同时选修“1”号课程和“2”号课程的所有学生的学号；
selectSno
fromsc
whereCno='1'andSnoin（
selectSno
fromsc
whereCno='2'
9.列出课程表中全部信息，按先修课的升序排列；
select*
fromcourse
orderbyCpnoAsc
10.列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select*
fromstudent
whereSage>
（selectAVG（Sage）
fromstudent）
orderbySageDESC
11.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
selectSno学号,Sname姓名,Ssex性别,YEAR（GETDATE（））-Sage出生年份,Sdept所在院系
fromstudent
orderbyYEAR（GETDATE（））-Sage
12.按照院系降序显示所有学生的“院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：
院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；
selectSdept=
case
whenSdept='CS'then'计算机系'
whenSdept='IS'then'信息系'
whenSdept='MA'then'数学系'
whenSdept='EN'then'外语系'
whenSdept='CM'then'中医系'
whenSdept='WM'then'西医系'
else'条件不明'
end,Sno,Sname,Ssex,Sage
fromstudent
orderbySdeptDESC
13.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
selectSdept,院系规模=
case
whenCOUNT（Sno）>=5then'规模很大'
whenCOUNT（Sno）>=4then'规模一般'
whenCOUNT（Sno）>=2then'规模稍小'
else'规模很小'
end
fromstudent
whereSdeptisnotNull
groupbySdept
14.按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
selectSno,Cno,Grade
fromsc
whereGradebetween70and80
orderbyCno,GradeDESC
15.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
selectcount（*）学生总人数,AVG（Sage）平均年龄
fromstudent
16.显示选修的课程数大于3的各个学生的选修课程数；
selectSno学号,COUNT（Sno）选修课程数
fromsc
groupbySno
havingCOUNT（*）>=3
17.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
usestudent
selectCno'课程号',COUNT（*）'总人数',MAX（Grade）'最高分',MIN（Grade）'最低分',AVG（Grade）'平均分'
fromsc
groupbyCno
orderbyCnodesc
（三）、选做题
18.显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；
usestudent
selectSno'学号',AVG（Grade）'平均成绩'
fromsc
groupbySno
havingAVG（Grade）>（
selectAVG（Grade）
fromsc
whereSno='200515001'）
19.显示选修各个课程的及格的人数;
usestudent
selectCno'课程号',COUNT（*）'及格人数',cast（cast（COUNT（casewhenGrade>=60then1end）asfloat）/COUNT（*）ASfloat
（1））'及格率'
fromsc
groupbyCno
20.显示选修最多的课程数和最少的课程数；
usestudent
selectSno'学号',COUNT（*）'选修课程数'
fromsc
groupbySno
havingCOUNT（Cno）>=all
（selectCOUNT（*）
fromsc
groupbySno）
union
selectSno'学号',COUNT（*）'选修课程数'
fromsc
groupbySno
havingCOUNT（Cno）<=all
（selectCOUNT（*）
fromsc
groupbySno）
21.显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
selectSdept,COUNT（casewhenSsex='女'then1end）'女生人数',
COUNT（casewhenSsex='男'then1end）'男生人数'
fromstudent
groupbySdept,Ssex
22.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
selectSno学号,AVG（Grade）平均成绩
fromsc
实验五：
数据库综合查询
.利用Transact-SQL嵌套语
升级会员 