数据库原理张红娟答案.doc

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=70where Sno=2001110;（5 ）删除学号为20011

2、10的学生的成绩记录：delete from Grade where Sno=2001110;（6 ）在学生表的 clno 属性上创建一个名为IX_Class的索引，以班级号的升序排序：create index IX_Classon 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 Ss

3、ex=女;（3 ）找出 01311 班和 01312 班的学生姓名、姓名、出生年份select Sname,Ssex,2014-Sage as year of birthfrom Studentwhere Clno=01311 or Clno=01312;（4 ）找出所有姓李的学生的个人信息select * from Student where Sname like 李%;（5 ）找出学生李勇所在班级的学生人数select numberfrom student inner join class on student.clno=class.clnowhere sname=李勇（6 ）找出课程名为

4、操作系统的平均成绩、最高分、最低分select AVG(Gmark) 平均成绩 ,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 级计算机软件班的成绩

5、为空的学生姓名select Sname 学生姓名 from (Student 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)找出所有与学生李勇有相同选修课程的学生信息se

6、lect * from Studentwhere sno in(select 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 Studentwhe

7、re sno in(select sno from Grade where 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

8、exists(select * from Coursewhere not exists(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)求每个课程号及相应的选课人

9、数select Grade.Cno 课程号,COUNT(Grade.Cno) 选课人数 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 级计算机软件的

10、全体学生的选课记录delete from Gradewhere Sno in(select Sno 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 Studentwh

11、ere Sname=李勇)update Class set Monitor=case when Monitor=(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 Cln

12、o=00311 then (select AVG(Sage) from Student where 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

13、=1and clno=01311and student.sno=grade.snowith check 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 number14 and sage 0 and gmark 0set count = 1return (count)