数据库原理张红娟答案.docx
《数据库原理张红娟答案.docx》由会员分享，可在线阅读，更多相关《数据库原理张红娟答案.docx（17页珍藏版）》请在冰点文库上搜索。
数据库原理张红娟答案
数据库原理张红娟答案
11.
（1）给学生表增加一个属性Nation,数据类型为Varchar（20）：
ALTERTABLEStudent
ADDNationVARCHAR（20）NULL;
（2）删除Nation：
ALTERTABLEStudent
DropColumnNation;
（3）向成绩表中插入记录（"2001110","3",80）：
insertintoGrade
values（'2001110','3',80）;
（4）将学号为2001110的学生的成绩改为70分：
updateGrade
setGmark='70'
whereSno='2001110';
（5）删除学号为'2001110'的学生的成绩记录：
deletefromGrade
whereSno='2001110';
fromGrade
（8）选修了操作系统的学生人数;
selectCOUNT（sno）学生人数
fromcourseinnerjoingrade
ono=o
whereCname='操作系统'
（9）找出2000级计算机软件班的成绩为空的学生姓名
selectSname学生姓名
from
（Studentinnerjoinclassonstudent.clno=class.clno）
innerjoingradeonstudent.sno=grade.sno
whereSpeciality='计算机软件'
andinyear='2000'
andgmarkisnull
13.
1）找出和李勇在同一个班级的学生信息
select*
fromStudent
whereclnoin
（selectClnofromStudent
whereSname='李勇'）
2）找出所有与学生李勇有相同选修课程的学生信息
select*fromStudent
wheresnoin
（selectsnofromgrade
wherecnoin
（selectcnofromgrade
wheresnoin（selectsnofromstudent
whereSname='李勇'）））;
3）找出年龄介于学生李勇和25岁之间的学生信息
select*fromStudent
whereSage<25andSage>
（selectSagefromStudentwhereSname='李勇'）
4）找出选修了课程是操作系统的学生学号和姓名
selectSno学号,Sname姓名fromStudent
wheresnoin
（selectsnofromGrade
whereCnoin
（selectCnofromCourse
wherecnoin（selectcnofromcourse
whereCname='操作系统'）））;
5）找出没有选修1号课程的所有学生姓名
selectSname姓名fromStudent
wherenotexists
（select*fromGrade
whereStudent.Sno=Grade.SnoandCno='1'）
6）找出选修了全部课程的学生姓名
selectSname姓名fromStudent
wherenotexists
（select*fromCourse
wherenotexists
（select*fromGrade
whereStudent.Sno=Grade.Sno
andGo=o））
14.
1）查询选修了3号课程的学生学号及成绩,并按成绩的降序排列
selectSno学号,Gmark成绩fromGrade
whereCno='3'
orderbyGmarkdesc
2）查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列
select*fromStudent
orderbyClnoasc,Sagedesc
3）求每个课程号及相应的选课人数
selectGrade.Cno课程号,COUNT（Grade.Cno）选课人数
fromGradejoinCourseonGrade.Cno=Course.Cno
groupbyGrade.Cno
4）查询选修了3门以上课程的学生学号
selectSno学号fromGrade
groupbySno
havingCOUNT（Sno）>3
15.
1）将01311班的全体学生的成绩置零
updateGradesetGmark=0
whereSnoin
（selectSnofromStudent
whereClno='01311'）
2）删除2001级计算机软件的全体学生的选课记录
deletefromGrade
whereSnoin
（selectSnofromStudent
whereClnoin
（selectClnofromClass
whereSpeciality='计算机软件'andInyear='2001'））
3）学生李勇已退学,从数据库中删除有关他的记录
deletefromGrade
whereSnoin
（selectSnofromStudent
whereSname='李勇'）
updateClasssetNumber=Number-1
whereClnoin
（selectClnofromStudent
whereSname='李勇'）
updateClasssetMonitor=case
whenMonitor=（selectSnofromStudentwhereSname='李勇'）
then''
end
fromClass
whereClnoin（selectClnofromStudentwhereSname='李勇'）
deletefromStudent
whereSname='李勇'
4）对每个班,求学生的平均年龄,并把结果存入数据库
altertableClassaddCagesmallintnull
updateClasssetCage=case
whenClno='00311'then（selectAVG（Sage）fromStudentwhereClno='00311'）
whenClno='00312'then（selectAVG（Sage）fromStudentwhereClno='00312'）
whenClno='01311'then（selectAVG（Sage）fromStudentwhereClno='01311'）
end
fromClass
16.
1、
createviewstu_01311_1
asselectstudentsno,sname,gmark
fromstudent,grade
wherecno=1
andclno=01311
andstudent.sno=grade.sno
withcheckoption
2、
createviewstu_01311_2
anselect*
fromstu_01311_1
wheregmark<60
3.
createviewstu_year（sno,sname,years）
asselectsno,sname,year（getdate（））-sage
fromstudent
4.
selectsname
fromstu_year
whereyears>1990
5.
select*
fromstu_year
wheresnoin
（selectsno
fromstu_01311_2）
第四章
10.
创建course表
createtablecourse
（
cnochar
（1）primarykey,
cnamevarchar（20）notnull,
creditsmallintcheck（creditin（'1','2','3','4','5','6','7'））
）
创建class表
createtableclass
（
clnochar（5）primarykey,
specialityvarchar（20）notnull,
inyearchar（4）notnull,
numberintegercheck（number>1andnumber<300）,
monitorchar（7）
）
创建student表
createtablestudent
（
snochar（7）primarykey,
snamevarchar（20）notnull,
ssexchar
（2）notnulldefault'男'check（ssexin（'男','女'））,
sagesmallintcheck（sage>14andsage<65）,
clnochar（5）notnullforeignkey（clno）referencesclass（clno）onupdatecascade
）
为class添加参照完整性
altertableclass
addconstraintmonitorforeignkey（monitor）referencesstudent（sno）
创建grade表
createtablegrade
（
sno
char（7）notnullforeignkey（sno）referencesstudent（sno）onupdatecascadeon
deletecascade,
cnochar
（1）notnullforeignkey（cno）referencescourse（cno）onupdatecascadeon
deletecascade,
gmarkdecimal（4,1）check（gmark>0andgmark<100）,
primarykey（sno,cno）
）
11.
插入
createtriggerstu_insert
onstudent
afterinsert
as
updateclass
setnumber=number+1
fromclass,inserted
whereclass.clno=inserted.clno;
删除
createtriggerstu_delete
onstudent
afterdelete
as
updateclass
setnumber=number-1
fromclass,deleted
whereclass.clno=deleted.clno;
12.
createtriggerstu_update
onclass
afterupdate
as
ifupdate（monitor）
if
（selectmonitorfrominserted）notin
（selectsnofromstudent
whereclno=（selectclnofromdeleted））
begin
print'thereisnotthenewmonitorintheclass'
rollbacktransaction
end
13.
新建product表
createtableproduct
（pnochar（6）primarykey,
pnamevarchar（20）notnull,
pricedecimal（7,2））
创建仓库表
createtablewarehouse
（whnochar（3）primarykey,
whnamevarchar（20）notnull,
whaddressvarchar（20））
创建库存商品表
createtablewhproduct
（whnochar（3）referenceswarehouse（whno）ondeletenoactiononupdatecascade,
pnochar（6）referencesproduct（pno）ondeletecascadeonupdatecascade,
numberint）
当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0
createtriggertri_product
onproduct
afterinsert
as
begin
declare@pnochar（3）
select@pno=pnofrominserted
insertintowhproduct
selectwhno,@pno,0
fromwarehouse
end
当新增商品是，自动生成改仓库所有商品的库存记录，库存数量为0
createtriggertri_warehouse
onwarehouse
afterinsert
as
begin
declare@whnochar（6）
select@whno=whnofrominserted
insertintowhproduct
select@whno,pno,0
fromproduct
end
14.
1）用户张勇对Student表和Course表有Select权力。
Grantselectonstudentto张勇
Grantselectoncourseto张勇
2）把对表Student的INSERT和Delete
权限授予用户张三，并允许他再把此权限授予其他用户。
Grantinsert,deleteonstudentto张三
withgrantoption
3）把查询Course表和修改属性Credit的权限授给用户李四。
Grantselect,update（credit）oncourseto李四
4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。
Grantallprivilegeonstudentto李勇敏withgrantoption
5）删除张勇对student表的select权
revokeselect
onstudent
from张勇;
删除张勇对course表的select权
revokeselect
oncourse
from张勇;
6）删除张三对student表的insert、delete权
revokeinsert,delete
onstudent
from张三
cascade;
10.创建一存储过程，根据学生学号查询该学生所以选修课的成绩，学号作为参数输入。
createprocedureproc_StuGmark
@stu_nochar（7）
as
selectGmark,Cno
fromGrade
whereSno=@Stu_no
执行过程：
executeproc_StuGmark@stu_no='2000101'
11.创建一存储过程，用来输入的参数：
班级名，判断该班级中是否已有学生存在，
若有，存储过程返回1；若没有，存储过程返回0
createprocedureproc_IsThereStudent
@Class_clnochar（5）
as
declare@countint
select@count=count（*）
fromStudent
whereClno=@Class_clno
if@count=0
return0
else
return1
执行过程：
declare@retvalveint
execute@retvalve=proc_IsThereStudent'00311'
if@retvalve=0
Print'该班级没有学生!
'
else
Print'该班级有学生!
'
14.
10）
createfunctionSno_Gmark（@stunochar（7））
returnstable
as
return（selectCno,Gmark
fromGrade
whereSno=@stuno）
执行过程：
selectCno,Gmark
fromSno_Gmark（'2000101'）;
11）
createfunctionclass_Stu（@classnochar（5））
returnsint
as
begin
declare@countint
select@count=count（*）
fromStudent
whereClno=@classno
if@count<>0
set@count=1
return（@count）
end
执行过程：
declare@retvalveint
execute@retvalve=class_Stu'00311'
if@retvalve=0
Print'该班级没有学生!
'
else
Print'该班级有学生!
'
升级会员 