《数据库原理张红娟答案》由会员分享，可在线阅读，更多相关《数据库原理张红娟答案（11页珍藏版）》请在金锄头文库上搜索。
1、11.（1）给学生表增加一个属性Nation,数据类型为Varchar（20）：ALTER TABLE StudentADD Nation VARCHAR(20) NULL;（2）删除Nation：ALTER TABLE StudentDrop Column Nation;（3）向成绩表中插入记录(2001110,3,80)：insert into Gradevalues(2001110,3,80);（4）将学号为2001110的学生的成绩改为70分：update Gradeset Gmark=70 where Sno=2001110;（5）删除学号为2001110的学生的成绩记录：delet
2、e from Grade where Sno=2001110;（6）在学生表的clno属性上创建一个名为IX_Class的索引，以班级号的升序排序：create index IX_Class on Student (clno Asc);（7）删除IX_Class索引：drop index Student.IX_Class;12.（1）找出所有被学生选修了的课程号：select distinct Cnofrom Grade;（2）找出01311班女学生的个人信息：select * from Studentwhere Clno=01311 and Ssex=女;（3）找出01311班和01312班
3、的学生姓名、姓名、出生年份select Sname,Ssex,2014-Sage as year of birthfrom Studentwhere Clno=01311 or Clno=01312;（4）找出所有姓李的学生的个人信息select * from Student where Sname like 李%;（5）找出学生李勇所在班级的学生人数select number from student inner join class on student.clno=class.clno where sname=李勇（6）找出课程名为操作系统的平均成绩、最高分、最低分select AVG(G
4、mark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分 from Gradewhere Cno in(select Cno from Coursewhere Cname=操作系统)（7）选修了课程的学生人数;select COUNT(distinct sno) 学生人数from Grade（8）选修了操作系统的学生人数;select COUNT(sno) 学生人数from course inner join gradeon o=owhere Cname=操作系统（9）找出2000级计算机软件班的成绩为空的学生姓名select Sname 学生姓名 from (Stud
5、ent inner join class on student.clno=class.clno)inner join grade on student.sno=grade.snowhere Speciality=计算机软件 and inyear=2000 and gmark is null13.1)找出和李勇在同一个班级的学生信息select * from Studentwhere clno in(select Clno from Studentwhere Sname=李勇)2)找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere sno in(sel
6、ect sno from gradewhere cno in(select cno from gradewhere sno in (select sno from studentwhere Sname=李勇);3)找出年龄介于学生李勇和25岁之间的学生信息select * from Studentwhere Sage (select Sage from Student where Sname=李勇) 4)找出选修了课程是操作系统的学生学号和姓名select Sno 学号,Sname 姓名 from Studentwhere sno in(select sno from Grade where
7、Cno in(select Cno from Course where cno in (select cno from coursewhere Cname=操作系统);5)找出没有选修1号课程的所有学生姓名select Sname 姓名 from Studentwhere not exists(select * from Grade where Student.Sno=Grade.Sno and Cno=1)6)找出选修了全部课程的学生姓名select Sname 姓名 from Studentwhere not exists(select * from Coursewhere not exi
8、sts(select * from Gradewhere Student.Sno=Grade.Snoand Go=o)14.1)查询选修了3号课程的学生学号及成绩,并按成绩的降序排列select Sno 学号,Gmark 成绩 from Gradewhere Cno=3order by Gmark desc2)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列select * from Studentorder by Clno asc ,Sage desc3)求每个课程号及相应的选课人数select Grade.Cno 课程号,COUNT(Grade.Cno) 选课人数
9、 from Grade join Course on Grade.Cno=Course.Cnogroup by Grade.Cno4)查询选修了3门以上课程的学生学号select Sno 学号 from Gradegroup by Snohaving COUNT(Sno)315.1)将01311班的全体学生的成绩置零update Grade set Gmark=0where Sno in(select Sno from Studentwhere Clno=01311)2)删除2001级计算机软件的全体学生的选课记录delete from Gradewhere Sno in(select Sno
10、 from Studentwhere Clno in(select Clno from Classwhere Speciality=计算机软件 and Inyear=2001)3)学生李勇已退学,从数据库中删除有关他的记录delete from Gradewhere Sno in(select Sno from Studentwhere Sname=李勇)update Class set Number=Number-1where Clno in(select Clno from Studentwhere Sname=李勇)update Class set Monitor=case when M
11、onitor=(select Sno from Student where Sname=李勇)then endfrom Classwhere Clno in(select Clno from Student where Sname=李勇)delete from Studentwhere Sname=李勇4)对每个班,求学生的平均年龄,并把结果存入数据库alter table Class add Cage smallint nullupdate Class set Cage=casewhen Clno=00311 then (select AVG(Sage) from Student where
12、 Clno=00311)when Clno=00312 then (select AVG(Sage) from Student where Clno=00312)when Clno=01311 then (select AVG(Sage) from Student where Clno=01311)endfrom Class16.1、create view stu_01311_1as select student sno,sname,gmarkfrom student,gradewhere cno=1and clno=01311and student.sno=grade.snowith che
13、ck option2、create view stu_01311_2an select *from stu_01311_1where gmark19905.select *from stu_yearwhere sno in(select snofrom stu_01311_2)第四章10.创建course表create table course ( cno char(1) primary key, cname varchar(20) not null, credit smallint check (credit in (1,2,3,4,5,6,7) ) 创建class表create table class ( clno char(5) primary key, speciality varchar(20) not null, inyear char(4) not null, number integer check(number1 and number300), monitor char(7) ) 创建student表create table student ( sno char(7) primary key, sname varchar(20) not null, ssex char(2) not null default
