附录C 数据库上机实验.doc

《附录C 数据库上机实验.doc》由会员分享，可在线阅读，更多相关《附录C 数据库上机实验.doc（16页珍藏版）》请在装配图网上搜索。

1、附录C 上机实验C.1 第4章上机实验-陈宇超，仅供参考-下列实验均使用SQL Server 的SSMS工具实现。1用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：l 数据库名为：学生数据库l 主要数据文件的逻辑文件名为：Students_data，存放在D:Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。l 日志文件的逻辑文件名字为：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-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-2 Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主

3、键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3 SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围为0-100表C-4 Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前4位，小数点后2位3. 写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。（1）

4、在Teacher表中添加一个职称列，列名为：Title，类型为nchar(4)。ALTER TABLE Teacher ADD Title VARCHAR(4)（2） 为Teacher表中的Title列增加取值范围约束，取值范围为：教授，副教授，讲师。ALTER TABLE Teacher ADD CONSTRAINT Title CHECK(Title IN(教授,副教授,讲师)（3） 将Course表中Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束,修改完数据类型后再重新添加约束项ALTER TABLE Course DROP CONSTRAINT C

5、K_Course_Credit_2B3F6F97ALTER TABLE Course ALTER COLUMN Credit TINYINTALTER TABLE Course ADD CONSTRAINT Credit CHECK (Credit0)（4） 删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束 ALTER TABLE Student DROP CONSTRAINT UQ_Student_CA1E5D79B3204FCFALTER TABLE Student DROP CONSTRAINT DF_Student_Sdate_3B75D760ALTER TA

6、BLE Student DROP COLUMN Sdate,Sid（5） 为Teacher表添加主键约束，其主键为：Tno。ALTER TABLE Teacher ADD PRIMARY KEY(Tno)建立数据库主要文件和日志文件我们可以看到D盘增加了主要数据文件.mdf和日志文件.ldf给students_data建立表格create table Student(Sno char(7),Sname char(10) not null,Ssex char(2) check( Ssex=男 or Ssex=女 ) ,Sage tinyint check(Sage 14 AND Sage0),

7、Semester tinyint, )create table SC(Sno char(7),Cno char(10),Grade tinyint check( Grade=0 AND Grade20011. 查询选了C002课程的学生姓名和所在系。 SELECT Sname,Sdept FROM Student st,SC sc WHERE st.Sno=sc.Sno AND Cno=C002 或者 SELECT Sname,Sdept FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE Cno=C002)或者SELECT Sname,Sde

8、pt FROM Student WHERE EXISTS (SELECT * FROM SCWHERE Sno = Student.Sno AND Cno = C002) 12. 查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。SELECT Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno AND Grade80 ORDER BY Grade DESC13. 查询与VB在同一学期开设的课程的课程名和开课学期。SELECT c2.Cname,c2.Semester FROM Course c1,Course c

9、2 WHERE c1.Cname=c2.Cname AND c1.Cname=VB AND c2.Cname!=VB14. 查询与李勇年龄相同的学生的姓名、所在系和年龄。SELECT s2.Sname,s2.Sdept,s2.Sage FROM Student s1,Student s2WHERE s1.Sage=s2.Sage AND s1.Sname=李勇 AND s2.Sname!=李勇或者SELECT Sname,Sdept,Sage FROM Student WHERE Sage=(SELECT Sage FROM Student WHERE Sname=李勇) AND Sname!

10、=李勇15. 查询哪些课程没有学生选修，列出课程号和课程名。SELECT Course.Cno,Course.Cname FROM Course LEFT JOIN SC on SC.Cno=Course.Cno WHERE SC.Cno IS NULL16. 查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。SELECT Student.Sno,Student.Sname,Cno FROM Student LEFT JOIN SC ON SC.Sno=Student.Sno17. 查询计算机系哪些学生没有选课，列出学生姓名。SELECT Sname FROM Stu

11、dent WHERE Sdept=计算机系 AND Sno NOT IN(SELECT Sno FROM SC)或者SELECT Sname FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = 计算机系 AND SC.Cno IS NULL18. 查询计算机系年龄最大的三个学生的姓名和年龄。SELECT Top 3 Sname,Sage FROM Student WHERE Sdept=计算机系 ORDER BY Sage DESC19. 列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。SELECT TOP

12、 3 WITH TIES Sname, Sdept, Grade FROM Student S JOIN SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = VBORDER BY Grade DESC20. 查询选课门数最多的前2位学生，列出学号和选课门数。SELECT top 2 Sno,COUNT(Cno) AS 选课数 FROM SC GROUP BY Sno ORDER BY COUNT(Cno) DESC21. 查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果

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

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

15、： 新表名，存储在磁盘上v 格式：SELECT 选择列表INTO 新表名 FROM 子句 SELECT Sname,Sdept,Grade INTO VB_Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB24. 统计每个系的女生人数，并将结果保存到新表Girls中。SELECT Sdept,Ssex,COUNT(Ssex) AS 人数 Into Girls FROM Student WHERE Ssex=女GROUP BY Sdept,Ssex 25. 用子查询实现如

16、下查询：（1）查询选了“C001”课程的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=C001 )（2）查询通信工程系成绩80分以上的学生的学号和姓名。SELECT Sno,Sname FROM Student WHERE Sdept=通信工程系 AND Sno IN (SELECT Sno FROM SC WHERE Grade80)（3）查询计算机系考试成绩最高的学生的姓名。SELECT Sname FROM Student,SC WHERE SC.Sno=Studen

17、t.Sno AND Sdept=计算机系 AND Grade in(SELECT MAX(Grade) FROM SC,Student WHERE SC.Sno=Student.Sno AND Sdept=计算机系)（4）查询年龄最大的男生的姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Ssex=男 AND Sage in(SELECT MAX(Sage) FROM Student WHERE Ssex=男)26. 查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。SELECT Sno, Grade FROM SC WH

18、ERE Cno = C001 AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = C001)27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。SELECT Sname,Sdept,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sdept=计算机系 AND Grade(SELECT AVG(Grade) FROM SC,Course,Student WHERE SC.Cno=Cou

19、rse.Cno AND Student.Sno=SC.Sno AND Sdept=计算机系)28. 查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。SELECT Sname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB AND Grade(SELECT AVG(Grade) FROM SC,Course WHERE Course.Cno=SC.Cno AND Cname=VB)29. 查询没选VB的学生姓名和所在系。SELECT Sname,Sdept

20、FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC,Course WHERE SC.Cno=Course.Cno AND Cname=VB)或者SELECT Sname,Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC,Course WHERE Course.Cno=SC.Cno AND Sno=Student.Sno AND Cname=VB)30. 查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。SELECT Cname,Semester,Credit FROM Cours

21、e c1 WHERE NOT EXISTS(SELECT * FROM Course c2 WHERE c1.Semester=c2.Semester AND c1.Creditc2.Credit )31. 查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。SELECT Cname,sname,SC.Cno,Grade FROM student join sc on student.sno=sc.sno join course on o=o WHERE grade=(SELECT max(grade)FROM scWHERE cno=

22、o ) order BY SC.Cno ASc或者SELECT sc1.sno,Sname,Cno ,Grade FROM SC sc1,student WHERE not exists(SELECT * FROM SC sc2 WHERE sc1.Cno=sc2.Cno AND sc1.Gradesc2.Grade) AND Grade IS NOT NULL AND Student.Sno=sc1.Sno order BY Cno ASC32. 查询选了全部课程的学生姓名SELECT Sname FROM Student WHERE Sno in (SELECT Sno FROM SC G

23、ROUP BY Sno having COUNT(*)=(SELECT COUNT(*) FROM Course)或者SELECT Sname FROM student WHERE NOT exists(SELECT * FROM courseWHERE NOT exists(SELECT * FROM SC WHERE Sno=student.sno AND cno=Course.Cno)33. 创建一个新表，表名为test，其结构为（COL1, COL 2, COL 3），其中，COL1：整型，允许空值。COL2：普通编码定长字符型，长度为10，不允许空值。COL3：普通编码定长字符型，长

24、度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）。COL1COL2COL3B11B2C22B3create table test(C0L1 int ,C0L2 char(10) not null,C0L3 char(10)insert into test(C0L2) values(B1)insert into test values(1,B2,C2)insert into test(C0L1,C0L2) values(2,B3)34. 利用23题建立的VB_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。INSERT INTO

25、VB_Grade SELECT Sname,Sdept,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=信息管理系 AND Cname=VB35. 将所有选修C001课程的学生的成绩加10分。UPDATE SC SET Grade=Grade+10 WHERE Cno=C00136. 将计算机系所有学生的“计算机文化学”的考试成绩加10分。UPDATE SC SET Grade=Grade+10 WHERE Sno in (SELECT SC.Sno FROM Stude

26、nt,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=计算机系 AND Cname=计算机文化学)37. 修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。UPDATE SC SET Grade=Grade+CASE SdeptWHEN 通信工程系 THEN 10WHEN 信息管理系 THEN 5else 0endFROM Student,SC,Course WHERE SC.Sno=Student.Sno AND Course.Cno=S

27、C.Cno AND Cname=VB38. 删除成绩小于50分的学生的选课记录。DELETE FROM SC WHERE Grade 5039. 删除计算机系VB考试成绩不及格学生的VB选课记录。DELETE FROM SC FROM SC,Student,Course WHERE SC.Sno=Student.SnoAND SC.Cno=Course.Cno AND Sdept=计算机系 ANDCname=VB AND Grade=60 GROUP BY SnoGO3. 利用第2题建立的视图，完成如下查询。（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。 SELECT Sname

28、,Cname,Grade FROM v2 WHERE Grade=90（2）查询选课门数超过3门的学生的学号和选课门数。SELECT Sname,Cname,Grade FROM v2 WHERE Grade=90（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。 SELECT Sname,Cnum FROM Student,v3 WHERE v3.Sno=Student.Sno AND Sdept=计算机系 AND Coursenum3（4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。 SELECT Student.Sno,Sname,Sdept,Creditsu

29、m FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10（5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。 SELECT Sname,Sage,Sdept,Creditsum FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10 AND Sage=204. 修改第3题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。ALTER VIEW v4 ASSELECT Sno,SUM(Credit) Creditsum,

30、COUNT(SC.Cno) CoursenumFROM SC,Course WHERE SC.Cno=Course.Cno GROUP BY SnoC.4 第7章上机实验利用第5章建立的学生数据库以及Student、Coures和SC表，完成下列操作。1. 创建满足如下要求的后触发型触发器。说明:对于insert into 表名 values() 单行插入语句，触发器每次都会被触发 对于 insert into 表名 selec FROM表名 或者是 insert into 表名 values (),(),().多行插入语句，触发器只会触发一次，所有的插入数据都会一并备份到inserted表中

31、，然后执行触发筛选-chenyuchao（1）限制学生的考试成绩必须在0-100之间。 CREATE TRIGGER tri1 ON SC after insertASif exists(SELECT Grade FROM inserted WHERE Grade not between 0 AND 100)rollbackgo（2）限制不能删除成绩不及格的考试记录。 CREATE TRIGGER tri2 ON SC AFTER DELETEASif exists(SELECT * FROM deleted WHERE Grade20)rollbackgo（4）限制每个学生每学期选的课程不能

32、超过5门。 CREATE TRIGGER tri4 ON SC AFTER INSERTASif exists(SELECT COUNT(SC.Cno) FROM SC,Course WHERE SC.Cno=Course.Cno GROUP BY Semester,Sno having COUNT(SC.Cno)5 )ROLLBACKgo 2. 创建满足如下要求的存储过程。（1）查询每个学生的修课总学分，要求列出学生学号及总学分。CREATE PROC p1ASSELECT Student.Sno,SUM(Credit) CreditSum FROM Student,SC,CourseWHE

33、RE Student.Sno=SC.Sno AND SC.Cno=Course.Cno GROUP BY Student.Sno执行：exec p1（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值。CREATE PROC p2 dept char(20)=信息管理系ASSELECT Student.Sno,Sname,SC.Cno,Course.Cname,Credit FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AN

34、D Sdept=dept执行: exec p2执行: exec p2 dept=计算机系（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。 CREATE PROC p3 dept char(20),number int outputASSELECT number=COUNT(Sno) FROM Student WHERE Sdept=dept AND Ssex=男执行: declare num intexec p3 计算机系,num outputprint num（4）删除指定学生的修课记录，其中学号为输入参数。CREATE PROC p4 sno char(20)ASdelete

35、 FROM SC WHERE Sno=sno执行:exec p4 0811101（5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。Create Proc p5 cno char(20),semester IntASupdate Course set Semester=semester WHERE Cno=cno执行:exec p5 C001,4C.5 第11章上机实验利用第4、5章建立的学生数据库和其中的Student、Course、SC表，并利用SSMS工具完成下列操作。1. 用SSMS工具建立SQL Server身份验证模式的登录名：log1、log2和log3。2. 用

36、log1建立一个新的数据库引擎查询，在“可用数据库”下列列表框中是否能选中学生数据库？为什么？3. 用系统管理员身份建立一个新的数据库引擎查询，将log1、log2和log3映射为学生数据库中的用户，用户名同登录名。4. 在log1建立的数据库引擎查询中，现在在“可用数据库”下列列表框中是否能选中学生数据库？为什么？5. 在log1建立的数据库引擎查询中，选中学生数据库，执行下述语句，能否成功？为什么？SELECT * FROM Course6. 在系统管理员的数据库引擎查询中，执行合适的授权语句，授予log1具有对Course表的查询权限，授予log2具有对Course表的插入权限。7. 用

37、log2建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？INSERT INTO Course VALUES(C1001，数据库基础，4，5)再执行下述语句，能否成功？为什么？SELECT * FROM Course8. 在log1建立的数据库引擎查询中，再次执行下述语句： SELECT * FROM Course这次能否成功？但如果执行下述语句：INSERT INTO Course VALUES(C103, 软件工程, 4, 5)能否成功？为什么？9. log3建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？CREATE TABLE NewTable( C1 int, C2 char(4)10. 授予log3在学生数据库中具有创建表的权限。11. 在系统管理员的数据库引擎查询中，执行下述语句：GRANT CREATE TABLE TO log3GOCREATE SCHEMA log3 AUTHORIZATION log3GOALTER USER log3 WITH DEFAULT_SCHEMA = log312. 在log3建立一个新的数据库引擎查询中，再次执行第9题的语句，能否成功？为什么？如果执行下述语句：SELECT * NewTable能否成功？为什么？379