第3章作业解答参考.docx

1、第3章作业解答参考10. 设有一数据库GradeManager（成绩管理），包括四个表：学生表（Student）、课程表（Course）、班级表（Class）以及成绩表（Grade）。四个表的结构如图2-4所示，数据如图2-5所示。用SQL语句创建四个表。Student属性名数据类型可否为空含义SnoChar(7)否学号（唯一）SnameVarChar(20)否学生姓名SsexChar(2)否性别SageSmallint可年龄ClnoChar(5)否学生所在班级Course属性名数据类型可否为空含义CnoChar(1)否课程号（唯一）CnameVarChar(20)否课程名称CreditSma

2、llint可学分Class属性名数据类型可否为空含义ClnoChar(5)否班级号（唯一）SpecialityVarChar(20)否班级所在专业InyearChar(4)否入校年份NumberInteger可班级人数MonitorChar(7)可班长学号Grade属性名数据类型可否为空含义SnoChar(7)否学号CnoChar(1)否课程号GmarkDecimal(4,1)可成绩图1 成绩管理数据库中的表结构StudentSnoSnameSsexSageClno2000101李勇男20003112000102刘诗晨女19003112000103王一鸣男20003122000104张婷婷女2

3、1003122001101李勇敏女19013112001102贾向东男22013112001103陈宝玉男20013112001104张逸凡男2101311CourseCnoCnameCredit1数据库42离散数学33管理信息系统24操作系统45数据结构46数据处理27C语言4ClassClnoSpecialityInyearNumberMonitor00311计算机软件2000120200010100312计算机应用2000140200010301311计算机软件20012202001103GradeSnoCnoGmark2000101192200010138820001015862000

4、1021782000102655200010336520001036782000103566200010415420001046832001101270200110146520011022802001102490200110268320011034762001103656 图2 成绩管理数据库中的数据设习题二中第10题成绩管理数据库的表结构说明中加入了完整性约束说明，如图3-6所示，请用SQL语句完成图中约束的说明。Student属性名数据类型可否为空含义完整性约束SnoChar(7)否学号主码SnameVarChar(20)否学生姓名SsexChar(2)否性别男或女，默认为男SageSma

5、llint可年龄大于14，小于65ClnoChar(5)否学生所在班级号外部码，级联Course属性名数据类型可否为空含义完整性约束CnoChar(1)否课程号主码CnameVarChar(20)否课程名称CreditSmallint可学分1、2、3、4、5、6之一Class属性名数据类型可否为空含义完整性约束ClnoChar(5)否班级号主码SpecialityVarChar(20)否班级所在专业InyearChar(4)否入校年份NumberInteger可班级人数大于1，小于100MonitorChar(7)可班长学号外部码Grade属性名数据类型可否为空含义完整性约束SnoChar(7

6、)否学号主属性，外部码，级联CnoChar(1)否课程号主属性，外部码，级联GmarkDecimal(4,1)可成绩大于0，小于100 图3 加了约束说明后的表结构-创建course表create table course(cno char(1) primary key,cname varchar(20) not null,credit smallint)-创建class表create table class(clno char(5) primary key,speciality varchar(20) not null,inyear char(4) not null,number integ

7、er,monitor char(7)-创建student表create table student(sno char(7) primary key,sname varchar(20) not null,ssex char(2) not null,sage smallint,clno char(5) not null foreign key(clno) references class(clno) on delete cascade)-为class表添加参照完整性alter table classadd constraint monitor foreign key (monitor) refer

8、ences student(sno) on delete no action-创建grade表create table grade (sno char(7) not null foreign key (sno) references student(sno) on delete cascade,cno char(1) not null foreign key (cno) references course (cno) on delete cascade,gmark decimal(4,1)-插入数据classinsert into class (clno,speciality,inyear,n

9、umber)values (00311,计算机软件,2000,120)-插入数据courseinsert into coursevalues (1,数据库,4)-插入数据studentinsert into studentvalues (2000101,李勇,男,20,00311)-修改数据classupdate class set monitor=2000101where clno=00311-插入数据gradeinsert into gradevalues (2000101,1,92)11. 针对以上四个表，用SQL语言完成以下各项操作：1 给学生表增加一属性Nation（民族），数据类型

10、为Varchar（20）；alter table studentadd nation varchar(20) 删除学生表中新增的属性Nation；alter table studentdrop column nation 向成绩表中插入记录（”2001110”，”3”，80）；insert into gradevalues(2001101,3,80) 修改学号为”2001110”的学生的成绩为70分；update gradeset gmark=70where sno=2001101 删除学号为”2001110”的学生的成绩记录；delete gradefrom gradewhere sno=2

11、001101 为学生表创建一个名为IX_Class的索引，以班级号的升序排序；create index IX_Classon student (clno asc) 删除IX_Class索引。drop index student.IX_Class12. 针对以上四个表，用SQL语言完成以下各项查询： 找出所有被学生选修了的课程号；select distinct cnofrom grade 找出01311班女学生的个人信息；select *from studentwhere clno=01311 and ssex=女3 出01311班、01312班的学生姓名、性别、出生年份；select snam

12、e,ssex,2010-sage as birthyearfrom studentwhere clno=01311 or clno=013124 找出所有姓李的学生的个人信息；select *from studentwhere sname like 李% 找出学生李勇所在班级的学生人数；-使用连接查询select numberfrom class,studentwhere class.clno=student.clno and sname=李勇-或使用嵌套子查询select numberfrom classwhere clno = (select clno from student where

13、 sname=李勇) 找出课程名为操作系统的平均成绩、最高分、最低分；select avg=avg(gmark),max=max(gmark),min=min(gmark)from grade,coursewhere o=o and cname=操作系统 找出选修了课程的学生人数；select count(distinct sno) as 选课人数from grade 找出选修了课程操作系统的学生人数；-使用连接查询select count(sno) as 选操作系统人数from grade,coursewhere o=o and cname=操作系统-或使用嵌套子查询select count

14、(*) as 选操作系统人数from gradewhere cno= (select cno from course where cname=操作系统) 找出2000级计算机软件班的成绩为空的学生姓名。-使用连接查询Select snameFrom student,grade,classWhere student.sno=grade.sno and student.clno=class.clno and speciality=计算机软件 and inyear=2000 and gmark is null-或使用嵌套子查询Select snameFrom studentWhere sno lik

15、e 2000% and clno in (select clno from class where speciality=计算机软件 and sno=any (select sno from grade where gmark is null)4. 针对以上四个表，用SELECT的嵌套查询完成以下各项查询：1 找出与李勇在同一个班级的学生信息；select * from studentwhere clno=( select clno from student where sname=李勇)2 找出所有与学生李勇有相同选修课程的学生信息；select * from studentwhere sn

16、o in (select distinct sno from grade where cno in (select cno from grade where sno= (select sno from student where sname=李勇)3 找出年龄介于学生李勇和25岁之间的学生信息；select *from studentwhere sage between (select sage from student where sname=李勇) and 25-使用自身连接查询select s1.*from student s1,student s2where s1.sage betwe

17、en s2.sage and 25 and s2.sname=李勇 and s2.snos1.sno4 找出选修了课程操作系统的学生学号和姓名；select sno,snamefrom studentwhere sno in (select sno from grade where cno= (select cno from course where cname=操作系统)-或使用相关子查询select sname,sno from studentwhere exists (select* from grade where sno=student.sno and cno= (select cn

18、o from course where cname=操作系统)-或使用连接查询select student.sno,snamefrom student,grade,coursewhere student.sno=grade.sno and o=o and ame=操作系统5 找出所有没有选修1号课程的学生姓名；select snamefrom studentwhere sno not in (select sno from grade where cno=1) -或使用all关键字select snamefrom studentwhere sno all (select sno from gr

19、ade where cno=1)-或使用相关子查询select snamefrom studentwhere not exists (select * from grade where sno=student.sno and cno=1) 找出选修了全部课程的学生姓名。（提示：可找出这样的学生，没有一门课程是他不选修的。）select snamefrom studentwhere not exists (select * from course where not exists (select * from grade where grade.sno=student.sno and o=o)1

20、4. 针对以上四个表，用SQL语言完成以下各项查询：1 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；select sno,gmarkfrom gradewhere cno=3order by gmark desc2 查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；select *from studentorder by clno,sage desc3 求每个课程号及相应的选课人数；select cno,count(distinct sno) as 选课人数from gradegroup by cno-包括没被选修课程及其相应的选课人数，方法一，使用并集

21、：select cno,count(distinct sno) as 选课人数from gradegroup by cnounion select cno,0from coursewhere cno all (select cno from grade)-包括没被选修课程及其相应的选课人数，方法二，使用外连接查询：select o,count(distinct sno) as 选课人数from course,gradewhere o*=ogroup by o4 查询选修了3门以上课程的学生学号。Select sno From gradeGroup by snoHaving count(cno)

22、=315. 针对以上四个表，用SQL语言完成以下各项操作：1 对每个班，求学生的平均年龄，并把结果存入数据库；-方法一，使用into关键字select clno,avg(student.sage) as avg_ageinto t_avgagefrom studentgroup by clno-方法二，先新建表，后带子查询的插入数据create table s_avg (clno char(6) primary key, avg_age int)goinsert into s_avgselect clno,avg(sage)from studentgroup by clno方法三，使用视图的方

23、式，但注意视图只保存查询定义并不存放实际数据：create view s_avg_ageas select clno,avg(sage) as avg_age from student group by clno2 将01311班的全体学生的成绩置零；update gradeset gmark=0where sno in (select sno from student where clno=01311)3 删除2001级计算机软件的全体学生的选课记录；错解：delete from grade,student,classwhere grade.sno=student.sno and stude

24、nt.clno=class.clno and speciality=计算机软件 and inyear=2001正解：delete from grade where sno in (select sno from student where clno in (select clno from class where inyear=2001 and speciality=计算机软件)4 学生李勇已退学，从数据库中删除有关他的记录。-删除grade表中李勇同学选课记录delete from gradewhere sno= (select sno from student where sname=李勇

25、)go-修改李勇所在班级人数update classset number=number-1where clno= (select clno from student where sname=李勇)go-若李勇是班长，则还应修改该班级班长的数据update classset monitor=nullwhere clno= (select clno from student where sname=李勇)go-删除student表中李勇学生记录delete from studentwhere sname=李勇16. 视图操作：1 建立01312班选修了1号课程的学生视图Stu_01312_1；cr

26、eate view Stu_013121_1as select student.sno,sname,ssex,sage,clno from student,grade where student.sno=grade.sno and clno=01312 and cno=12 建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；create view Stu_013121_2as select Stu_013121_1.* from Stu_013121_1,grade where Stu_013121_1.sno=grade.sno and gmark603 建立视图Stu_year，由学生学号、姓名、出生年份组成。Create view Stu_yearAs Select sno,sname,2002-sage as birth_year From student4 查询1983年以后出生的学生姓名。Select snameFrom Stu_