数据库实验答案樊辰自制仅供参考Word格式.docx

数据库实验答案樊辰自制仅供参考Word格式.docx

《数据库实验答案樊辰自制仅供参考Word格式.docx》由会员分享，可在线阅读，更多相关《数据库实验答案樊辰自制仅供参考Word格式.docx（15页珍藏版）》请在冰豆网上搜索。

吴家硕'

女'

1987-03-24'

爱好：

音乐'

0602003'

吴春斌'

1988-07-01'

NULL）

0701001'

王腾飞'

1988-05-04'

机电'

0701002'

林世伟'

1987-04-03'

体育'

0701003'

李乐仪'

1986-03-03'

null）

0703001'

李奇'

1988-09-17'

工商管理'

insertcoursesvalues（'

c1'

数据结构'

null,4）

c2'

数据库原理'

4）

c3'

大型数据库'

3）

c4'

高尔夫'

null,1）

insertscvalues（'

61）

72）

88）

50）

78）

52）

87）

--查询表中数据

select*fromstudents

--删除表中数据

deletesc

--删除表

droptablestudents

--3①查询计算机系全体学生的信息

select*fromStudents

--3②查询姓“李”的学生的学号和姓名。

selectSno,SnamefromStudentswhereSnamelike'

李%'

--3③查询课程表中先行课为空的课程名。

selectCnamefromCourseswherePreCnoisnull

--3④查询考试成绩有不及格的学生的学号。

selectSnofromSCwhereGrade<

60orGradeisnull

--3⑤求选修了C1课程或C2课程的学生的学号及成绩。

selectSno,GradefromSCwhereCno='

C1'

orCno='

C2'

--3⑥查询全体计算机系学生的姓名及其年龄。

selectSname,year（getdate（））-year（Sbirthday）fromStudentswhereSdept='

--3⑦查询计算机系在1986－1987年之间出生的学生的姓名。

selectSnamefromStudentswhereSdept='

andyear（Sbirthday）>

=1986andyear（Sbirthday）<

=1987

--3⑧查询姓“李”的前两个学生（按姓名升序排列）的学号和姓名。

selecttop2Sno,SnamefromStudentswhereSnamelike'

orderbySnameasc

--3⑨查询选修了两门以上课程的学生学号与课程数。

selectSno,count（Sno）as课程数fromSCgroupbySnohavingcount（Sno）>

2

--3⑩查询选修课程数大于等于2的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。

selectsno,avg（grade）,count（*）fromscgroupbysnohavingcount（*）<

=2orderbyavg（grade）desc

--

（1）查询选修了【数据库原理】的计算机系的学生学号和姓名。

selectStudents.Sno,Sname

fromStudents,SC,Courses

whereCname='

andSC.Cno=Courses.CnoandSC.Sno=Students.Sno

--

（2）查询每一门课的间接先行课（即先行课的先行课）。

selecta.Cno,b.PreCno

fromCoursesa,Coursesb

wherea.PreCno=b.Cno

--（3）查询学生的学号、姓名、选修课程的名称和成绩。

selectStudents.Sno,Sname,Cname,Grade

fromStudents,SC,Students.Sno=SC.SnoandSC.Cno=Courses.Cno

--（4）查询选修了课程的学生姓名。

selectdistinctSname

fromStudents,SC

whereSC.Sno=Students.Sno

--（5）查询所有学生的信息和所选修的课程,没有选课的同学对应的选课信息为空。

selectstudents.*,courses.*,sc.*

fromstudentsleftjoin（scINNERJOINcourseson（o=o））on（students.sno=sc.sno）

--（6）查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示为0。

selectCourses.Cno,Cname,count（Sno）as选课人数

fromCoursesleftjoinSConCourses.Cno=SC.Cno

groupbyCourses.Cno,Courses.Cname

--（7）列出学生所有可能的选修情况。

selectStudents.Sno,Courses.Cno

fromStudents,Courses

orderbySno

--（8）查找计算机系的学生选修课程数大于2的学生的姓名、平均成绩和选课门数，并按平均成绩降序排列。

selectSname,avg（Grade）as平均成绩,count（Cno）as选课门数

fromStudentsleftjoinSConSC.Sno=Students.Sno

groupbyStudents.Sname,Students.Sdept

havingcount（Cno）>

2andStudents.Sdept='

orderbyavg（Grade）desc

--2、嵌套查询和组合查询操作。

--

（1）统计选修了【数据库原理】课程的学生人数。

selectcount（Sno）as选修数据库原理的学生人数

fromSC

whereCno=（selectCnofromCourseswhereCname='

--

（2）查询没有选修【数据库原理】课程的学生信息。

--可以用notexists

selectcount（Students.Sno）as没有选修数据库原理的学生人数

fromStudents

whereSnonotin（selectSnofromSCwhereCno=（selectCnofromCourseswhereCname='

））

--（3）查询其他系中比计算机系学生年龄都小的学生。

select*

whereSdept!

='

andyear（Sbirthday）<

（selecttop1year（Sbirthday）fromStudentswhereSdept='

orderbyyear（Sbirthday）desc）

--（4）查询被0602001学生或0602002学生所选修的课程的课程号

--（用UNION组合查询与IN条件查询两种方法实现）。

selectCno

whereSno='

UNION

selectdistinctCno

whereSnoin（selectSnofromSCWhereSno='

orSno='

--（5）查询0602001学生和0602002学生同时选修的课程的课程号

--（用IN组合查询与EXISTS嵌套子查询两种方法实现）。

andCnoin

where

exists（

）andSno='

--（6）查询被学号0602001学生选修，但没有被0602002学生所选修的课程的课程号。

whereCnonotin（selectCnofromSCwhereSno='

）andSno='

--①向表Students中插入（0601001,赵林,男,1985-09-08,计算机）的记录。

insertStudentsvalues（'

0601001'

赵林'

'

1985-09-08'

--②向SC表中添加一个学生的选课记录，学号为0601001，所选的课程号为C2。

--SC表中有Sno、Cno、Grade这3个列。

这里只知道学号和课程号，不知道成绩值。

insertSCvalues（'

--③向表Students中插入（0601002，张修雨，default）记录，该记录的数据中default表示默认值‘男’，其他数据为空值。

insertStudents（Sno,Sname,Ssex）values（'

0601002'

张修雨'

default）

--④用CREATE语句建立表StudentBAK1，包含（与Students的Sno、Sname、Sdept相同）3个字段，

--然后用INSERTSELECT语句实现向StudentBAK1添加Students表中的计算机系学生的学号、姓名、所在系的信息。

createtableStudentBAK1（

Snochar（9）primarykey,

Snamechar（20）,

Sdeptchar（20）

insertintoStudentBAK1selectSno,Sname,SdeptfromStudentswheresdept='

select*fromStudentBAK1

--⑤用SELECT…INTO语句实现把Students表中1986年后（包含1986年）出生的学生的学号、姓名存储到一个新表StudentBAK2。

createtableStudentBAK2（

Snochar（9）,

Snamechar（20）

selectSno,SnameintoStudentBAK2fromStudentswhereyear（Sbirthday）>

=1986

select*fromStudentBAK2

--⑥将Students表中姓名为【赵林】的同学所在系改为【机电】，爱好改为【足球】。

updateStudentssetSdept='

Memo='

足球'

whereSname='

--⑦将选修了课程名为【数据库原理】并且有成绩的学生成绩加5分。

updateSCsetGrade=Grade+5whereCno=（selectCnofromCourseswhereCname='

--⑧将StudentCourse数据库的StudentBAK1表中所有姓赵的同学删除。

deletefromStudentBAK1whereSnamelike'

赵%'

--⑨删除机电系课程成绩不及格或者没有登记成绩的学生选课记录。

deletefromSCwhere

exists（select*fromSC,StudentswhereSdept='

andGrade<

60andSC.Sno=Students.Sno）orGradeisNULL

--⑩将StudentCourse数据库的StudentBAK2表中的所有行删除。

deleteStudentBAK2

--实验（6）、第四章数据库安全性

--1.在数据库student中创建7个用户U1,U2,U3,U4,U5,U6,U7

--<

记录下创建用户的过程，需要自己完成>

记录你的实际操作过程

execsp_addlogin'

U1'

U2'

U3'

U4'

U5'

U6'

U7'

execsp_grantdbaccess'

--2.完成教材4.2.4小节[例1]～[例7]（P138~139）的授权语句。

记录下授权的过程，需要自己完成>

grantselect

onstudenttoU1

grantallprivileges

onstudenttoU2,U3

oncoursetoU2,U3

onsctopublic

grantselect,update（sno）

onstudenttoU4

grantinsert

onsctoU5withgrantoption

onsctoU6withgrantoption

onsctoU7

--3.在授权完成以后，验证用户是否拥有相应的权限

--注意：

验证时需要分别以不同的用户（U1～U7）身份登录到数据库，进行相关的操作，

--检查他们是否具有教材表4.4（P139）中的相应权限。

记录下验证过程，需要自己完成>

--4.完成教材4.2.4小节[例8]～[例10]（P140）的权限回收语句，并验证在权限回收以后，

--该用户是否真正丧失了对数据的相应权限。

记录下权限回收及验证过程，需要自己完成>

revokeupdate（sno）onstudentfromu4

revokeselectonscfrompublic

revokeinsertonscfromU5cascade

--5.数据库角色的使用

--包括:

创建角色,为角色授权，将用户添加到角色中，从角色移除用户等操作，角

--色概念介绍参考教材4.2.5小节数据库角色的内容（P142），

--而在SQLServer上机操作需要参考教学博客上提供的示例代码自行完成。

记录下角色的使用过程，需要自己完成>

execsp_addroleR1

grantselect,insert,updateonstudenttoR1

execsp_addrolememberR1,u5

revokeselectonstudentfromR1

--实验（七）、第五章数据库完整性

服务器:

消息547，级别16，状态1，行1

INSERT语句与COLUMNFOREIGNKEY约束'

FK__sc__sno__00551192'

冲突。

该冲突发生于数据库'

Practice7'

，表'

student'

column'

sno'

。

语句已终止。

消息2627，级别14，状态1，行1

违反了PRIMARYKEY约束'

PK__student__79A81403'

不能在对象'

中插入重复键。

所影响的行数为1行）

分数不能大于100

（所影响的行数为1行）

--在开始实验之前，先创建一个名为Practice7的数据库，

--下面所有的SQL语句都是在Practice7这个数据库内执行的

createdatabasePractice7

usePractice7

--1.在数据库Practice7中创建教材P82的student,course,sc三张表，先不设置主键，

--检查能否向表中插入重复值。

记录下验证的过程，需要自己完成>

createtablestudent

snochar（9）,

snamevarchar（10）,

ssexchar

（2）,

sagetinyint,

sdeptchar（20）

createtablecourse

cnochar（4）,

cnamevarchar（16）,

cpnochar（4）,

ccredittinyint

gradetinyint

insertintostudentvalues（'

200215121'

李勇'

20,'

CS'

select*fromstudent

--2.实体完整性：

删除上一步创建的3张表，重新创建，对每张表都设置主键，先

--不为SC表设置外键。

再次检查能否向表中插入重复值，能否在SC表中插入不存在的学号（Sno）或者课程号（Cno）

droptablestudent

droptablecourse

droptablesc

snochar（9）primarykey,

cnochar（4）primarykey,

insertintoscvalues（'

0001'

98）

200215122'

0002'

99）

select*fromsc

--3.参照完整性：

删除上一步创建的SC表，重新创建SC表并为其设置主键和外键。

--再次验证能否在SC表中插入不存在的学号（Sno）或者课程号（Cno）

snochar（9）foreignkeyreferencesstudent（sno）,

cnoc