第三章SQL练习题参考答案.doc

11、针对以上四个表，用SQL语言完成以下各项操作： 给学生表增加一属性Nation（民族），数据类型为Varchar（20）；Alter table student add Nation Varchar(20); 删除学生表中新增的属性Nation；Alter table student drop column Nation; 向成绩表中插入记录（”2001110”，”3”，80）；insert into Grade values(2001103,3,80); 修改学号为”2001103”的学生的成绩为70分；update Grade set Gmark=70 where Sno=2001103; 删除学号为”2001110”的学生的成绩记录；delete Grade where Sno=2001110; 为学生表创建一个名为，以班级号的升序排序；create index IX_Class on student(Clno ASC);删除IX_Class索引Drop index IX_Class12、针对以上四个表，用SQL语言完成以下各项查询： 找出所有被学生选修了的课程号；select distinct cno from grade; 找出01311班女学生的个人信息；select * from student where clno=01311 and ssex=女; 找出01311班、01312班的学生姓名、性别、出生年份；Select sname,ssex, year(getdata()-sage as 出生年份from student where clno in(01311,01312); 找出所有姓李的学生的个人信息；Select * from student where sname like 李%； 找出学生李勇所在班级的学生人数；Select count(*)from student where clno in (select clno from student where sname= 李勇); 找出课程名为操作系统的平均成绩、最高分、最低分；Select avg(gmark),max(gmark),min(gmark) from grade where cno =(select cno from course where cname=操作系统); 找出选修了课程的学生人数；Select count(distinct sno) from grade; 找出选修了课程操作系统的学生人数；Select count(sno) from grade where cno =(select cno from course where cname=操作系统); 找出2000级计算机软件班的成绩为空的学生姓名。Select sname from student where clno in (select clno from class where inyear=2000 and speciality=计算机软件) and sno in (select sno from grade where gmark is null); 13、针对以上四个表，用SELECT的嵌套查询完成以下各项查询： 找出与李勇在同一个班级的学生信息；Select * from student where clno = (select clno from student where sname=李勇)； 找出所有与学生李勇有相同选修课程的学生信息；Select * from student where sno in (select distinct sno from gradewhere cno in ( select cno from grade where sno=(select sno from student where sname=李勇) 找出年龄介于学生李勇和25岁之间的学生信息；Select * from student where sage between (select sage from student where sname=李勇) and 25 找出选修了课程操作系统的学生学号和姓名；Select sno,sname from student where sno in (select sno from grade where cno = (select cno from course where cname=操作系统 ) 找出所有没有选修1号课程的学生姓名；Select sname from student where not exists (select * from grade where sno=student.sno and cno=1)OR: Select sname from studentWhere sno not in (Select sno from grade Where cno=1); 找出选修了全部课程的学生姓名。（提示：可找出这样的学生，没有一门课程是他不选修的。）Select sname from student where not exists (select * from course where not exists (select * from grade where sno=student.sno and cno=o)解二:Select sname from student Where sno in(Select sno from grade Group by sno Having count(*)=Select count(*) from course);14、针对以上四个表，用SQL语言完成以下各项查询： 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列； Select sno,gmark from grade where cno=3 order by gmark desc 查询全体学生信息，查询结果按班级号升序排列，同一班级学生按年龄降序排列；select * from student order by clno,sage desc 求每个课程号及相应的选课人数；Select cno ,count(sno) from grade group by cno 查询选修了3门以上课程的学生学号。Select sno from grade group by sno having count(*)315、针对以上四个表，用SQL语言完成以下各项操作： 将01311班的全体学生的成绩置零；update gradeset gmark=0where sno in (select sno from student where clno=01311) 删除2001级计算机软件的全体学生的选课记录；Delete from gradewhere sno in (select sno from student where clno=(select clno from classwhere speciality=计算机软件 and inyear=2001) 学生李勇已退学，从数据库中删除有关他的记录。update class set monitor=null where clno in (select clno from student where sname=李勇)delete from student where sname=李勇注：grade表中外键sno设置了级联修改，因此该命令执行以后，grade表中李勇的选课记录自动删除）对每个班，求学生的平均年龄，并把结果存入数据库； 方案一：建立视图create view v_sag as select clno,AVG(sage) as sag from student group by clno)方案二：class表中增加一列，用于存放平均年龄alter table class add avg_sage smallintupdate classset avg_sage=(select avg(sage) from student where clno=class.clno)方案三：建立一存放平均年龄的表单，插入平均年龄create table 平均年龄表（clno char(5) primary key,avg_sage smallint） insert into 平均年龄表select clno,avg(sage)from studentgroupe by clno16、视图操作： 建立01312班选修了1号课程的学生视图Stu_01312_1； create view Stu_00312_1 as select *from Studentwhere Student.Sno=any(select Sno from Gradewhere Grade.Cno=1) and Student.Clno=00312建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；create view Stu_00312_2 as select *from Studentwhere Sno in (select Sno from Gradewhere Grade.Cno=1and Grade.Gmark1990查询01312班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。select *from Stu_yearwhere 学号 in (select sno From Stu_00312_2 ) 1、 建立一个学生信息表student，表中有5个字段：学号Sno（字符型），姓名Sname（字符型），性别Ssex（字符型），年龄Sage（整型），所在系Sdept（字符型），其中Sno是关系的码；用SQL语言完成题目1-6：（1）、建立学生信息表student。（要求：包含主码的定义；定义学生的年龄在15到40之间）（1）、CREATEtablestudent(snochar(8)primarykey,snamevarchar(20)notnull,ssexvarchar(2)check(ssexin(男,女),sage int check(sage between 15and 40),sdept varchar(20)定义视图，完成如下功能：输出学生平均年龄大于19的系及该系学生的平均年龄。CREATEviewv_studentAsSELECTsdept,avg(sage)平均年龄FromstudentGroupbysdeptHavingavg(sage)19