数据库原理答案 西安电子科技大学5页word文档.doc
如有侵权，请联系网站删除，仅供学习与交流数据库原理答案 西安电子科技大学【精品文档】第 5 页数据库原理-西安电子科技大学出版社姓名：陈俊昌班级：10923337 杭州电子科技大学计算机学院111)给学生表增加一属性Nation(民族),数据类型为varchar(20)alter table Studentadd nation varchar(20)2)删除学生表中新增的属性nationalter table Studentdrop column nation3)向成绩表中插入记录（“2001110”，“3”，80）insert into Grade(Sno,Cno,Gmark)values ('2001110','3',80)4)将学号为“2001110”的学生成绩修改为70分update Gradeset Gmark=70where Sno='2001110'5)删除学号为“2001110”的学生成绩记录delete from Gradewhere Sno='2001110'6)在学生表的clno属性上创建一个名为ix_class的索引,以班级号的升序排序create index ix_classon student(clno)7)删除ix_class索引drop index student.ix_class12.1)找出所有被学生选修了的课程号select distinct Cno 课程号 from Grade2)找出01312班女生的个人信息select * from Studentwhere Clno='01311' and Ssex='女'3)找出01311班和01312班的学生姓名 性别 出生年份select sname 学生姓名,ssex 性别,2011-sage 出生年份from Studentwhere Clno='01311' or Clno='01312'4)找出所有姓李的学生的个人信息select * from Studentwhere Sname like '李%'5)找出李勇所在班级的学生人数select COUNT(*) from Studentwhere Clno in(select Clno from Studentwhere Sname='李勇')6)找出课程名为操作系统的平均成绩 最高分 最低分select AVG(Gmark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分 from Gradewhere Cno in(select Cno from Coursewhere Cname='操作系统')7)选修了课程的学生人数;select COUNT(distinct sno) 学生人数from Grade8)选修了操作系统的学生人数;select COUNT(distinct sno) 学生人数from Gradewhere Cno in(select Cno from Coursewhere Cname='操作系统')9)找出2000级计算机软件班的成绩为空的学生姓名select Sname 学生姓名 from(select * from Studentwhere Clno in(select Clno from Classwhere Speciality='计算机软件' and Inyear='2000') ) ruanjianwhere ruanjian.Sno not in(select distinct Sno from Grade)with ruanjian as(select Sno,Snamefrom Student join Classon Student.Clno=Class.Clnowhere Class.Speciality='计算机软件' and Class.Inyear='2000')select Sname 学生姓名 from ruanjianwhere not exists(select * from Gradewhere ruanjian.Sno=Grade.Sno)13.1)找出和李勇在同一个班级的学生信息select * from Studentwhere Sname<>'李勇' and Clno in(select Clno from Studentwhere Sname='李勇')2)找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere Sname<>'李勇' and exists(select * from Gradewhere Student.Sno=Grade.Sno and Grade.Cno in(select Cno from Gradewhere Sno in(select Sno from Student where Sname='李勇')3)找出年龄介于学生李勇和25岁之间的学生信息select * from Studentwhere Sage < 25 and Sage > (select Sage from Student where Sname='李勇')4)找出选修了课程是操作系统的学生学号和姓名select Sno 学号,Sname 姓名 from Studentwhere exists(select * from Grade where Cno in(select Cno from Course where Cname='操作系统')and Student.Sno=Grade.Sno)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 Cno from Courseexceptselect Cno from Gradewhere Student.Sno=Grade.Sno)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) 选课人数 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 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 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 Clno='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 Class
