第3章作业解答参考.docx
- 文档编号:7321861
- 上传时间:2023-01-22
- 格式:DOCX
- 页数:18
- 大小:19.83KB
第3章作业解答参考.docx
《第3章作业解答参考.docx》由会员分享,可在线阅读,更多相关《第3章作业解答参考.docx(18页珍藏版)》请在冰豆网上搜索。
第3章作业解答参考
10.设有一数据库GradeManager(成绩管理),包括四个表:
学生表(Student)、课程表(Course)、班级表(Class)以及成绩表(Grade)。
四个表的结构如图2-4所示,数据如图2-5所示。
用SQL语句创建四个表。
Student
属性名
数据类型
可否为空
含义
Sno
Char(7)
否
学号(唯一)
Sname
VarChar(20)
否
学生姓名
Ssex
Char
(2)
否
性别
Sage
Smallint
可
年龄
Clno
Char(5)
否
学生所在班级
Course
属性名
数据类型
可否为空
含义
Cno
Char
(1)
否
课程号(唯一)
Cname
VarChar(20)
否
课程名称
Credit
Smallint
可
学分
Class
属性名
数据类型
可否为空
含义
Clno
Char(5)
否
班级号(唯一)
Speciality
VarChar(20)
否
班级所在专业
Inyear
Char(4)
否
入校年份
Number
Integer
可
班级人数
Monitor
Char(7)
可
班长学号
Grade
属性名
数据类型
可否为空
含义
Sno
Char(7)
否
学号
Cno
Char
(1)
否
课程号
Gmark
Decimal(4,1)
可
成绩
图1成绩管理数据库中的表结构
Student
Sno
Sname
Ssex
Sage
Clno
2000101
李勇
男
20
00311
2000102
刘诗晨
女
19
00311
2000103
王一鸣
男
20
00312
2000104
张婷婷
女
21
00312
2001101
李勇敏
女
19
01311
2001102
贾向东
男
22
01311
2001103
陈宝玉
男
20
01311
2001104
张逸凡
男
21
01311
Course
Cno
Cname
Credit
1
数据库
4
2
离散数学
3
3
管理信息系统
2
4
操作系统
4
5
数据结构
4
6
数据处理
2
7
C语言
4
Class
Clno
Speciality
Inyear
Number
Monitor
00311
计算机软件
2000
120
2000101
00312
计算机应用
2000
140
2000103
01311
计算机软件
2001
220
2001103
Grade
Sno
Cno
Gmark
2000101
1
92
2000101
3
88
2000101
5
86
2000102
1
78
2000102
6
55
2000103
3
65
2000103
6
78
2000103
5
66
2000104
1
54
2000104
6
83
2001101
2
70
2001101
4
65
2001102
2
80
2001102
4
90
2001102
6
83
2001103
4
76
2001103
6
56
图2成绩管理数据库中的数据
设习题二中第10题成绩管理数据库的表结构说明中加入了完整性约束说明,如图3-6所示,请用SQL语句完成图中约束的说明。
Student
属性名
数据类型
可否为空
含义
完整性约束
Sno
Char(7)
否
学号
主码
Sname
VarChar(20)
否
学生姓名
Ssex
Char
(2)
否
性别
男或女,默认为男
Sage
Smallint
可
年龄
大于14,小于65
Clno
Char(5)
否
学生所在班级号
外部码,级联
Course
属性名
数据类型
可否为空
含义
完整性约束
Cno
Char
(1)
否
课程号
主码
Cname
VarChar(20)
否
课程名称
Credit
Smallint
可
学分
1、2、3、4、5、6之一
Class
属性名
数据类型
可否为空
含义
完整性约束
Clno
Char(5)
否
班级号
主码
Speciality
VarChar(20)
否
班级所在专业
Inyear
Char(4)
否
入校年份
Number
Integer
可
班级人数
大于1,小于100
Monitor
Char(7)
可
班长学号
外部码
Grade
属性名
数据类型
可否为空
含义
完整性约束
Sno
Char(7)
否
学号
主属性,外部码,级联
Cno
Char
(1)
否
课程号
主属性,外部码,级联
Gmark
Decimal(4,1)
可
成绩
大于0,小于100`
图3加了约束说明后的表结构
--创建course表
createtablecourse
(
cnochar
(1)primarykey,
cnamevarchar(20)notnull,
creditsmallint
)
--创建class表
createtableclass
(
clnochar(5)primarykey,
specialityvarchar(20)notnull,
inyearchar(4)notnull,
numberinteger,
monitorchar(7)
)
--创建student表
createtablestudent
(
snochar(7)primarykey,
snamevarchar(20)notnull,
ssexchar
(2)notnull,
sagesmallint,
clnochar(5)notnullforeignkey(clno)referencesclass(clno)ondeletecascade
)
--为class表添加参照完整性
altertableclass
addconstraintmonitorforeignkey(monitor)referencesstudent(sno)ondeletenoaction
--创建grade表
createtablegrade
(
snochar(7)notnullforeignkey(sno)referencesstudent(sno)ondeletecascade,
cnochar
(1)notnullforeignkey(cno)referencescourse(cno)ondeletecascade,
gmarkdecimal(4,1)
)
--插入数据class
insertintoclass(clno,speciality,inyear,number)
values('00311','计算机软件','2000',120)
--插入数据course
insertintocourse
values('1','数据库',4)
--插入数据student
insertintostudent
values('2000101','李勇','男',20,'00311')
--修改数据class
updateclass
setmonitor='2000101'
whereclno='00311'
--插入数据grade
insertintograde
values('2000101','1',92)
11.针对以上四个表,用SQL语言完成以下各项操作:
1给学生表增加一属性Nation(民族),数据类型为Varchar(20);
altertablestudent
addnationvarchar(20)
②删除学生表中新增的属性Nation;
altertablestudent
dropcolumnnation
③向成绩表中插入记录(”2001110”,”3”,80);
insertintograde
values('2001101','3',80)
④修改学号为”2001110”的学生的成绩为70分;
updategrade
setgmark=70
wheresno='2001101'
⑤删除学号为”2001110”的学生的成绩记录;
deletegrade
fromgrade
wheresno='2001101'
⑥为学生表创建一个名为IX_Class的索引,以班级号的升序排序;
createindexIX_Class
onstudent(clnoasc)
⑦删除IX_Class索引。
dropindexstudent.IX_Class
12.针对以上四个表,用SQL语言完成以下各项查询:
①找出所有被学生选修了的课程号;
selectdistinctcno
fromgrade
②找出01311班女学生的个人信息;
select*
fromstudent
whereclno='01311'andssex='女'
3出01311班、01312班的学生姓名、性别、出生年份;
selectsname,ssex,2010-sageasbirthyear
fromstudent
whereclno='01311'orclno='01312'
4找出所有姓李的学生的个人信息;
select*
fromstudent
wheresnamelike'李%'
⑤找出学生李勇所在班级的学生人数;
--使用连接查询
selectnumber
fromclass,student
whereclass.clno=student.clnoandsname='李勇'
--或使用嵌套子查询
selectnumber
fromclass
whereclno=
(selectclno
fromstudent
wheresname='李勇')
②找出课程名为操作系统的平均成绩、最高分、最低分;
selectavg=avg(gmark),max=max(gmark),min=min(gmark)
fromgrade,course
whereo=oandcname='操作系统'
⑦找出选修了课程的学生人数;
selectcount(distinctsno)as选课人数
fromgrade
⑧找出选修了课程操作系统的学生人数;
--使用连接查询
selectcount(sno)as选操作系统人数
fromgrade,course
whereo=oandcname='操作系统'
--或使用嵌套子查询
selectcount(*)as选操作系统人数
fromgrade
wherecno=
(selectcno
fromcourse
wherecname='操作系统')
⑨找出2000级计算机软件班的成绩为空的学生姓名。
--使用连接查询
Selectsname
Fromstudent,grade,class
Wherestudent.sno=grade.snoandstudent.clno=class.clno
andspeciality='计算机软件'andinyear='2000'andgmarkisnull
--或使用嵌套子查询
Selectsname
Fromstudent
Wheresnolike'2000%'
andclnoin
(selectclno
fromclass
wherespeciality='计算机软件'
andsno=any
(selectsnofromgrade
wheregmarkisnull))
4.针对以上四个表,用SELECT的嵌套查询完成以下各项查询:
1找出与李勇在同一个班级的学生信息;
select*
fromstudent
whereclno=(
selectclno
fromstudent
wheresname=’李勇’)
2找出所有与学生李勇有相同选修课程的学生信息;
select*
fromstudent
wheresnoin
(selectdistinctsno
fromgrade
wherecnoin
(selectcno
fromgrade
wheresno=
(selectsno
fromstudent
wheresname=’李勇’)))
3找出年龄介于学生李勇和25岁之间的学生信息;
select*
fromstudent
wheresagebetween
(selectsage
fromstudent
wheresname=’李勇’)and25
--使用自身连接查询
selects1.*
fromstudents1,students2
wheres1.sagebetweens2.sageand25ands2.sname='李勇'ands2.sno 4找出选修了课程操作系统的学生学号和姓名; selectsno,sname fromstudent wheresnoin (selectsno fromgrade wherecno= (selectcno fromcourse wherecname=’操作系统’)) --或使用相关子查询 selectsname,sno fromstudent whereexists (select* fromgrade wheresno=student.snoandcno= (selectcno fromcourse wherecname=’操作系统’)) --或使用连接查询 selectstudent.sno,sname fromstudent,grade,course wherestudent.sno=grade.snoando=o andame=’操作系统’ 5找出所有没有选修1号课程的学生姓名; selectsname fromstudent wheresnonotin (selectsno fromgrade wherecno=1) ` --或使用all关键字 selectsname fromstudent wheresno<>all (selectsno fromgrade wherecno=1) --或使用相关子查询 selectsname fromstudent wherenotexists (select* fromgrade wheresno=student.snoandcno=1) ⑥找出选修了全部课程的学生姓名。 (提示: 可找出这样的学生,没有一门课程是他不选修的。 ) selectsname fromstudent wherenotexists (select* fromcourse wherenotexists (select* fromgrade wheregrade.sno=student.snoando=o)) 14.针对以上四个表,用SQL语言完成以下各项查询: 1查询选修了3号课程的学生学号及其成绩,并按成绩的降序排列; selectsno,gmark fromgrade wherecno=’3’ orderbygmarkdesc 2查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列; select* fromstudent orderbyclno,sagedesc 3求每个课程号及相应的选课人数; selectcno,count(distinctsno)as选课人数 fromgrade groupbycno --包括没被选修课程及其相应的选课人数,方法一,使用并集: selectcno,count(distinctsno)as选课人数 fromgrade groupbycno union selectcno,0 fromcourse wherecno<>all (selectcno fromgrade) --包括没被选修课程及其相应的选课人数,方法二,使用外连接查询: selecto,count(distinctsno)as选课人数 fromcourse,grade whereo*=o groupbyo 4查询选修了3门以上课程的学生学号。 Selectsno Fromgrade Groupbysno Havingcount(cno)>=3 15.针对以上四个表,用SQL语言完成以下各项操作: 1对每个班,求学生的平均年龄,并把结果存入数据库; --方法一,使用into关键字 selectclno,avg(student.sage)asavg_age intot_avgage fromstudent groupbyclno --方法二,先新建表,后带子查询的插入数据 createtables_avg (clnochar(6)primarykey, avg_ageint) go insertintos_avg selectclno,avg(sage) fromstudent groupbyclno 方法三,使用视图的方式,但注意视图只保存查询定义并不存放实际数据: createviews_avg_age as selectclno,avg(sage)asavg_age fromstudent groupbyclno 2将01311班的全体学生的成绩置零; updategrade setgmark=0 wheresnoin(selectsno fromstudent whereclno=’01311’) 3删除2001级计算机软件的全体学生的选课记录; 错解: delete fromgrade,student,class wheregrade.sno=student.snoandstudent.clno=class.clno andspeciality=’计算机软件’andinyear=’2001’ 正解: deletefromgrade wheresnoin (selectsno fromstudent whereclnoin (selectclno fromclass whereinyear=’2001’ andspeciality=’计算机软件’)) 4学生李勇已退学,从数据库中删除有关他的记录。 ] --删除grade表中李勇同学选课记录 delete fromgrade wheresno= (selectsno fromstudent wheresname=’李勇’) go --修改李勇所在班级人数 updateclass setnumber=number-1 whereclno= (selectclno fromstudent wheresname=’李勇’) go --若李勇是班长,则还应修改该班级班长的数据 updateclass setmonitor=null whereclno= (selectclno fromstudent wheresname=’李勇’) go --删除student表中李勇学生记录 delete fromstudent wheresname=’李勇’ 16.视图操作: 1建立01312班选修了1号课程的学生视图Stu_01312_1; createviewStu_013121_1 as selectstudent.sno,sname,ssex,sage,clno fromstudent,grade wherestudent.sno=grade.snoandclno=01312andcno=1 2建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2; createviewStu_013121_2 as selectStu_013121_1.* fromStu_013121_1,grade whereStu_013121_1.sno=grade.snoandgmark<60 3建立视图Stu_year,由学生学号、姓名、出生年份组成。 CreateviewStu_year As Selectsno,sname,2002-sageasbirth_year Fromstudent 4查询1983年以后出生的学生姓名。 Selectsname FromStu_
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 作业 解答 参考