数据库原理答案 西安电子科技大学.doc

精品文档数据库原理-西安电子科技大学出版社姓名：陈俊昌班级：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=20011105)删除学号为“2001110”的学生成绩记录delete from Gradewhere Sno=20011106)在学生表的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=013124)找出所有姓李的学生的个人信息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 (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 th