2022年附录C数据库上机实验 .pdf

《2022年附录C数据库上机实验 .pdf》由会员分享，可在线阅读，更多相关《2022年附录C数据库上机实验 .pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、364 附录 C 上机实验C.1 第 4 章上机实验-陈宇超，仅供参考 - 下列实验均使用SQL Server 的 SSMS 工具实现。1用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：数据库名为：学生数据库主要数据文件的逻辑文件名为：Students_data，存放在 D:Data 文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB ，增长方式为自动增长，每次增加1MB 。日志文件的逻辑文件名字为：Students_log，也存放在 D:Data 文件夹下，初始大小为： 2MB ，增长方式为自动增长，每次增加10% 。2选用已

2、建立的“学生数据库”，写出创建满足表C-1到4-4 条件的表的 SQL 语句，并执行所写代码。（注：“说明”部分不作为表定义内容）表 C-1 Student表结构列名说明数据类型约束Sno 学号普通编码定长字符串，长度为7 主键Sname 姓名普通编码定长字符串，长度为10 非空Ssex 性别普通编码定长字符串，长度为2 取值范围： 男，女 Sage 年龄微整型（ tinyint）取值范围： 15-45 Sdept 所在系普通编码不定长字符串，长度为20 默认值为“计算机系”Sid 身份证号普通编码定长字符串，长度为10 取值不重Sdate 入学日期日期默认为系统当前日期表 C-2 Cours

3、e表结构列名说明数据类型约束Cno 课程号普通编码定长字符串，长度为10 主键Cname 课程名普通编码不定长字符串，长度为20 非空Credit 学时数整型取值大于0 Semester 学分小整型表 C-3 SC 表结构列名说明数据类型约束Sno 学号普通编码定长字符串，长度为7 主键，引用Student 的外键Cno 课程号普通编码定长字符串，长度为10 主键，引用Course 的外键Grade 成绩小整型取值范围为0-100 表 C-4 Teacher表结构名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 -

4、- - - - - - 第 1 页，共 15 页 - - - - - - - - - 365 列名说明数据类型约束Tno 教师号普通编码定长字符串，长度为8 非空Tname 教师名普通编码定长字符串，长度为10 非空Salary 工资定点小数，小数点前4 位，小数点后2 位3. 写出实现如下功能的SQL 语句，并执行所写代码，查看执行结果。（1） 在 Teacher 表中添加一个职称列，列名为：Title，类型为nchar(4)。ALTERTABLETeacher ADDTitleVARCHAR( 4) （2） 为 Teacher 表中的 Title列增加取值范围约束，取值范围为： 教授，副教

5、授， 讲师 。ALTERTABLETeacher ADDCONSTRAINT TitleCHECK (TitleIN( 教授 , 副教授 , 讲师 ) （3） 将 Course 表中 Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束 , 修改完数据类型后再重新添加约束项ALTERTABLECourse DROP CONSTRAINTCK_Course_Credit_2B3F6F97ALTERTABLECourse ALTERCOLUMN CreditTINYINTALTERTABLECourse ADDCONSTRAINT CreditCHECK ( Cre

6、dit 0) （4） 删除 Student 表中的 Sid 和 Sdate 列。注意：这里首先需要先删除约束ALTERTABLEStudent DROP CONSTRAINTUQ_Student_CA1E5D79B3204FCFALTERTABLEStudentDROP CONSTRAINTDF_Student_Sdate_3B75D760ALTERTABLEStudentDROP COLUMN Sdate, Sid （5） 为 Teacher 表添加主键约束，其主键为：Tno。ALTERTABLETeacher ADDPRIMARY KEY (Tno) 建立数据库主要文件和日志文件我们可以看

7、到D盘增加了主要数据文件.mdf 和日志文件 .ldf 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 15 页 - - - - - - - - - 366 给 students_data建立表格createtableStudent (Sno char ( 7),Sname char ( 10) notnull,Ssex char ( 2) check( Ssex= 男or Ssex= 女),Sage tinyintcheck( Sage 14 AND Sage0),Se

8、mester tinyint,) createtableSC (Sno char ( 7),Cno char ( 10),Grade tinyintcheck( Grade=0 ANDGrade200 11.查询选了 C002课程的学生姓名和所在系。SELECTSname , Sdept FROM Studentst , SCsc WHERE st . Sno=sc. Sno ANDCno =C002 或者 SELECT Sname , Sdept FROM StudentWHERE Sno IN( SELECTSno FROM SCWHERE Cno =C002 ) 或者SELECTSnam

9、e , Sdept FROM StudentWHERE EXISTS (SELECT* FROM SCWHERE Sno = Student . Sno ANDCno = C002 )名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 15 页 - - - - - - - - - 368 12.查询考试成绩80 分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。SELECT Sname , Cno , Grade FROM Student , SCWHERE Studen

10、t . Sno=SC . Sno ANDGrade80 ORDER BY Grade DESC 13.查询与 VB在同一学期开设的课程的课程名和开课学期。SELECTc2. Cname , c2. Semester FROM Course c1, Course c2WHERE c1. Cname =c2. CnameANDc1. Cname =VBANDc2. Cname !=VB 14.查询与李勇年龄相同的学生的姓名、所在系和年龄。SELECTs2. Sname , s2. Sdept, s2. Sage FROM Students1, Students2WHERE s1. Sage =s2

11、. Sage ANDs1. Sname = 李勇 ANDs2. Sname != 李勇或者SELECTSname , Sdept, Sage FROM StudentWHERE Sage =(SELECTSage FROMStudentWHERE Sname = 李勇 ) ANDSname != 李勇 15.查询哪些课程没有学生选修，列出课程号和课程名。SELECTCourse. Cno , Course. CnameFROM Course LEFT JOIN SC onSC . Cno =Course. Cno WHERE SC .Cno IS NULL 16.查询每个学生的选课情况，包括未

12、选课的学生，列出学生的学号、姓名、选的课程号。SELECTStudent . Sno, Student . Sname , Cno FROM StudentLEFT JOIN SC ONSC . Sno=Student . Sno 17.查询计算机系哪些学生没有选课，列出学生姓名。SELECT Sname FROM Student WHERE Sdept= 计算机系 AND SnoNOT IN( SELECTSno FROM SC ) 或者SELECTSnameFROM StudentS LEFT JOIN SC ONS. Sno = SC . SnoWHERE Sdept = 计算机系 AN

13、DSC .Cno IS NULL18.查询计算机系年龄最大的三个学生的姓名和年龄。SELECT Top 3 Sname , Sage FROM Student WHERE Sdept= 计算机系 ORDER BYSage DESC 19.列出“ VB ”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。SELECT TOP 3 WITHTIES Sname , Sdept, Grade FROM Student S JOIN SConS. Sno = SC . Sno JOIN Course C ONC . Cno = SC . Cno WHERE Cname= VBORDER BY G

14、rade DESC 20.查询选课门数最多的前2 位学生，列出学号和选课门数。SELECT top 2 Sno, COUNT(Cno ) AS 选课数 FROM SCGROUP BY Sno ORDER BYCOUNT( Cno ) DESC 21.查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 15 页 - - - - - - - - - 369 示“偏小”；如果年龄在18-22，则显示 “合适

15、”；如果年龄大于22，则显示“偏大”。 SELECTSno, Sage ,CASEWHEN Sage =18 ANDSage22 THEN 偏大ENDAS 年龄情况 FROM Student 22.统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号， 选课人数及选课情况， 其中选课情况为： 如果此门课程的选课人数超过100 人，则显示“人多”；如果此门课程的选课人数在40100，则显示 “一般”；如果此门课程的选课人数在140，则显示“人少” ；如果此门课程没有人选，则显示“无人选”。 SELECTCourse. Cno , COUNT(Sno) AS 选课人数 ,CASEW

16、HEN COUNT(Sno)100 THEN 人多WHEN COUNT(Sno)0 THEN 人少WHEN COUNT(Sno)=40 THEN 一般WHEN COUNT(Sno)=0 THEN 无人选 endAS 人数情况 FROM SCRIGHTJOIN Course ONCourse. Cno =SC . Cno GROUP BYCourse. Cno 23.查询计算机系选了VB 课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。局部临时表： #新表名。局部于当前连接，生命期同连接期。全局临时表： # 新表名。可在所有连接中使用，生命期同用户连接期永久表：新表名，存

17、储在磁盘上格式： SELECT 选择列表INTO 新表名FROM 子句,SELECTSname , Sdept, Grade INTO VB_GradeFROM Student , SC , CourseWHERE Student . Sno=SC . Sno ANDCourse. Cno =SC . Cno ANDCname =VB 24.统计每个系的女生人数，并将结果保存到新表Girls中。SELECT Sdept, Ssex, COUNT( Ssex) AS 人数 IntoGirlsFROM Student WHERESsex= 女 GROUP BY Sdept, Ssex25.用子查询

18、实现如下查询：（1）查询选了“ C001”课程的学生姓名和所在系。SELECTSname , Sdept FROM StudentWHERE Sno IN( SELECTSno FROM SC WHERE Cno =C001) （2）查询通信工程系成绩80 分以上的学生的学号和姓名。SELECTSno , SnameFROM StudentWHERE Sdept= 通信工程系 ANDSno IN (SELECTSno FROM SC WHERE Grade80)（3）查询计算机系考试成绩最高的学生的姓名。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - -

19、- - - - - - 名师精心整理 - - - - - - - 第 6 页，共 15 页 - - - - - - - - - 370 SELECTSnameFROM Student , SC WHERE SC . Sno=Student . SnoANDSdept= 计算机系 ANDGrade in(SELECTMAX ( Grade)FROMSC , StudentWHERE SC . Sno=Student . Sno ANDSdept= 计算机系 ) （4）查询年龄最大的男生的姓名、所在系和年龄。SELECTSname , Sdept, Sage FROM StudentWHERE S

20、sex= 男ANDSage in(SELECTMAX (Sage) FROM Student WHERE Ssex= 男 ) 26.查询 C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。SELECTSno, GradeFROM SC WHERE Cno = C001ANDGrade ( SELECTAVG ( Grade) FROM SC WHERE Cno = C001 )27.查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。SELECTSname , Sdept, Cname , Grade FROM Student , SC , Cour

21、se WHEREStudent . Sno=SC . Sno ANDCourse. Cno =SC . Cno ANDSdept= 计算机系 ANDGrade(SELECTAVG ( Grade) FROM SC , Course, StudentWHERESC . Cno =Course. Cno ANDStudent . Sno=SC . Sno ANDSdept= 计算机系 )28.查询 VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。SELECTSname , Grade FROM Student , SC , Course WHERE Student . Sno=SC . Sn

22、o ANDCourse. Cno =SC . Cno ANDCname =VBANDGrade(SELECTAVG (Grade) FROM SC , Course WHERE Course. Cno =SC . Cno ANDCname =VB ) 29.查询没选 VB的学生姓名和所在系。SELECTSname , Sdept FROM StudentWHERE Sno NOTIN (SELECTSno FROMSC , Course WHERE SC . Cno =Course. Cno ANDCname =VB )或者SELECTSname , Sdept FROM StudentWHE

23、RE NOTEXISTS (SELECT* FROMSC , Course WHERE Course. Cno =SC . Cno ANDSno=Student . Sno ANDCname =VB )30.查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。SELECTCname , Semester, CreditFROM Course c1 WHERE NOTEXISTS (SELECT* FROM Course c2 WHERE c1. Semester=c2. Semester ANDc1. Credit c2. Credit) 31.查询每门课程考试成绩最高的学生信息，列出

24、课程号、 学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。SELECTCname , sname , SC . Cno , GradeFROM studentjoinsc on student . sno=sc. sno joincourse oncourse . cno=sc. cnoWHERE grade=(SELECTmax ( grade)名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 15 页 - - - - - - - - - 371 FROM

25、sc WHERE cno=course . cno ) orderBY SC . Cno ASc 或者SELECT sc1. sno, Sname , Cno, Grade FROM SCsc1, studentWHERE not exists(SELECT * FROM SCsc2 WHERE sc1. Cno =sc2. CnoANDsc1. Gradesc2. Grade)ANDGrade IS NOTNULLANDStudent . Sno=sc1. Sno orderBY Cno ASC32.查询选了全部课程的学生姓名SELECTSnameFROM StudentWHERE Sno

26、in(SELECTSnoFROM SC GROUP BY Sno having COUNT(*)=(SELECTCOUNT(*)FROM Course) 或者SELECTSname FROM studentWHERE NOTexists(SELECT * FROM courseWHERE NOT exists(SELECT * FROM SCWHERE Sno=student . sno ANDcno=Course. Cno )33.创建一个新表，表名为test ，其结构为（COL1, COL 2, COL 3 ） ，其中，COL1 ：整型，允许空值。COL2 ：普通编码定长字符型，长度为10

27、，不允许空值。COL3 ：普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）。COL1 COL2 COL3 B1 1 B2 C2 2 B3 createtabletest (C0L1 int,C0L2 char( 10) not null,C0L3 char( 10) insertintotest ( C0L2 ) values (B1 )insertintotestvalues (1, B2 , C2 )insertintotest ( C0L1 , C0L2 ) values ( 2, B3 ) 34.利用 23 题建立的VB_Grade表，将信息管

28、理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。INSERTINTOVB_GradeSELECT Sname , Sdept, Grade FROM Student , SC , CourseWHERE Student . Sno=SC . Sno ANDSC . Cno =Course. Cno ANDSdept= 信息管理系 ANDCname =VB 35.将所有选修C001课程的学生的成绩加10 分。UPDATE SC SET Grade=Grade+10 WHERE Cno =C001 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - -

29、 - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 15 页 - - - - - - - - - 372 36.将计算机系所有学生的“计算机文化学”的考试成绩加10 分。UPDATE SC SET Grade=Grade+10 WHERESno in( SELECTSC . Sno FROM Student , SC , CourseWHERE Student . Sno=SC . Sno ANDSC . Cno =Course. CnoANDSdept= 计算机系 ANDCname = 计算机文化学 ) 37.修改“ VB ”课程的考试成绩，如果是

30、通信工程系的学生，则增加10 分；如果是信息管理系的学生则增加5 分，其他系的学生不加分。UPDATE SC SET Grade=Grade+CASESdeptWHEN 通信工程系 THEN 10 WHEN 信息管理系 THEN 5 else 0 endFROM Student , SC , CourseWHERE SC . Sno=Student . Sno ANDCourse. Cno =SC . Cno ANDCname =VB 38.删除成绩小于50 分的学生的选课记录。DELETEFROM SC WHERE Grade 50 39.删除计算机系VB考试成绩不及格学生的VB选课记录。D

31、ELETEFROM SCFROM SC , Student , Course WHERE SC . Sno=Student . SnoANDSC . Cno =Course. Cno ANDSdept= 计算机系 ANDCname =VBANDGrade=60 GROUP BYSnoGO3.利用第 2 题建立的视图，完成如下查询。（1）查询考试成绩大于等于90 分的学生的姓名、课程名和成绩。SELECTSname , Cname , Grade FROM v2 WHERE Grade=90 （2）查询选课门数超过3 门的学生的学号和选课门数。SELECTSname , Cname , Grad

32、e FROM v2 WHERE Grade=90 （3）查询计算机系选课门数超过3 门的学生的姓名和选课门数。SELECTSname , CnumFROM Student , v3 WHERE v3. Sno =Student . Sno AND Sdept= 计算机系 ANDCoursenum 3 （4）查询修课总学分超过10 分的学生的学号、姓名、所在系和修课总学分。SELECTStudent . Sno, Sname , Sdept, CreditsumFROM Student , v4WHERE v4. Sno=Student . Sno ANDCreditsum 10 （5）查询年龄

33、大于等于20 岁的学生中，修课总学分超过10 分的学生的姓名、年龄、所在系和修课总学分。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 15 页 - - - - - - - - - 376 SELECTSname , Sage, Sdept, CreditsumFROM Student , v4WHERE v4. Sno=Student . Sno ANDCreditsum 10 ANDSage=20 4.修改第 3 题（ 4）定义的视图，使其查询每个学生的学号、总学分

34、以及总的选课门数。ALTERVIEWv4 AS SELECTSno, SUM ( Credit ) Creditsum , COUNT( SC . Cno ) CoursenumFROM SC , Course WHERE SC . Cno =Course. Cno GROUP BY Sno C.4 第 7 章上机实验利用第 5 章建立的学生数据库以及Student 、Coures 和 SC表，完成下列操作。1.创建满足如下要求的后触发型触发器。说明 : 对于 insert into 表名 values() 单行插入语句，触发器每次都会被触发对于 insert into 表名 selec FR

35、OM表名或者是 insert into 表名values (),(),().多行插入语句，触发器只会触发一次，所有的插入数据都会一并备份到inserted表中，然后执行触发筛选-chenyuchao（1）限制学生的考试成绩必须在0-100 之间。CREATE TRIGGER tri1ONSC afterinsertASifexists(SELECTGrade FROM insertedWHERE Grade not between 0 AND 100)rollback go （2）限制不能删除成绩不及格的考试记录。CREATE TRIGGER tri2ONSC AFTERDELETEASife

36、xists(SELECT* FROM deletedWHERE Grade20)rollback go （4）限制每个学生每学期选的课程不能超过5 门。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 15 页 - - - - - - - - - 377 CREATE TRIGGER tri4ONSC AFTERINSERTASifexists(SELECTCOUNT(SC . Cno ) FROM SC , CourseWHERE SC . Cno=Course. Cn

37、o GROUP BY Semester, SnohavingCOUNT( SC . Cno)5 )ROLLBACKgo 2.创建满足如下要求的存储过程。（1）查询每个学生的修课总学分，要求列出学生学号及总学分。CREATE PROC p1ASSELECTStudent . Sno, SUM ( Credit ) CreditSum FROM Student , SC , CourseWHERE Student . Sno=SC . Sno ANDSC . Cno =Course. Cno GROUP BYStudent . Sno执行： exec p1 （2）查询学生的学号、姓名、修的课程号、

38、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值。CREATE PROC p2 dept char (20)= 信息管理系 ASSELECTStudent . Sno, Sname , SC . Cno , Course. Cname , CreditFROMStudent , SC , CourseWHERE Student . Sno =SC . Sno ANDSC . Cno =Course. Cno ANDSdept=dept 执行: execp2 执行: exec p2 dept = 计算机系 （3）查询指定系的男生人数，其中系为输入参数，

39、人数为输出参数。CREATE PROC p3 dept char (20), numberintoutputASSELECT number =COUNT( Sno) FROM StudentWHERE Sdept=dept ANDSsex= 男执行: declarenum intexec p3 计算机系 , num outputprintnum （4）删除指定学生的修课记录，其中学号为输入参数。CREATE PROC p4 snochar ( 20)ASdeleteFROM SC WHERE Sno=sno执行:execp4 0811101 （5）修改指定课程的开课学期。输入参数为：课程号和修

40、改后的开课学期。Create Proc p5 cnochar ( 20), semester IntASupdate Course set Semester=semester WHERE Cno =cno执行:execp5 C001 , 4 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 15 页 - - - - - - - - - 378 C.5 第 11 章上机实验利用第 4、5 章建立的学生数据库和其中的Student、 Course、SC 表，并利用SSMS 工

41、具完成下列操作。1.用 SSMS 工具建立SQL Server 身份验证模式的登录名：log1、log2 和 log3。2.用 log1 建立一个新的数据库引擎查询，在“可用数据库”下列列表框中是否能选中学生数据库？为什么？3.用系统管理员身份建立一个新的数据库引擎查询，将log1、log2 和 log3 映射为学生数据库中的用户，用户名同登录名。4.在 log1 建立的数据库引擎查询中，现在在“可用数据库”下列列表框中是否能选中学生数据库？为什么？5.在 log1 建立的数据库引擎查询中，选中学生数据库，执行下述语句，能否成功？为什么？SELECT * FROM Course 6.在系统管理

42、员的数据库引擎查询中，执行合适的授权语句，授予log1 具有对Course 表的查询权限，授予log2 具有对 Course 表的插入权限。7.用 log2 建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？INSERT INTO Course VALUES(C1001，数据库基础，4，5) 再执行下述语句，能否成功？为什么？SELECT * FROM Course 8.在 log1 建立的数据库引擎查询中，再次执行下述语句：SELECT * FROM Course这次能否成功？但如果执行下述语句：INSERT INTO Course VALUES(C103, 软件工程, 4, 5)

43、 能否成功？为什么？9.log3 建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？CREATE TABLE NewTable( C1 int, C2 char(4) 10. 授予 log3 在学生数据库中具有创建表的权限。11. 在系统管理员的数据库引擎查询中，执行下述语句：GRANT CREATE TABLE TO log3 GO CREATE SCHEMA log3 AUTHORIZATION log3 GO ALTER USER log3 WITH DEFAULT_SCHEMA = log3 12. 在 log3 建立一个新的数据库引擎查询中，再次执行第9 题的语句，能否成功？为什么？如果执行下述语句：SELECT * NewTable 能否成功？为什么？名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 15 页 - - - - - - - - -