数据库第五六七章作业答案.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第五章作业,1,、查询学生选课表中的全部数据,select *from sc,2,、查询计算机系学生的姓名、年龄,select sname,sage from student,where sdept=,计算机系,3,、查询成绩在,70-80,的学生的学号、课程号和成绩,select*from sc where grade between 70 to 80,4,、查询计算机系年龄在,18-20,的男生的姓名、年龄,select sname,sage from student,Where sdept=,计算机系,and sage between 18 to 20 and ssex=,男,5,、查询“,C01”,课程的最高成绩,Select max(grade)from sc where cno=c01,1,6,、查询计算机系学生的最大年龄和最小年龄,select max(sage),min(sage)from student,where sdept=,计算机系,7,、统计每个系的学生人数,select sdept,count(*)from student group by sdept,8,、统计每门课程的选课人数和考试最高分,select cno,count(*),max(grade)from sc group by cno,9,、统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果,select sno,count(*),sum(grade)from sc,group by sno order by count(*)asc,10,、查询总成绩超过,200,分的学生，要求列出学号、总成绩,select sno,sum(grade)from sc,group by sno having sum(grade)200,2,11,、查询选修了”,C02“,课程的学生的姓名和所在系,select sname,sdept from student s join sc on s.sno=sc.sno where sc.con=c02,12,、查询成绩,80,分以上的学生的姓名、选的课程号和成绩，并按成绩降序排列结果,select sname,cno,grade from student s join sc,on s.sno=sc.sno where grade 80 order by grade desc,13,、查询哪些学生没有选课，要求列出学号、姓名和所在系,select s.sno,sname,sdept from student s left join sc on,s.sno=sc.sno where o is null,3,14,、查询与,VB,在同一学期开设的课程的课程名和开课学期,select ame,c1.semester from course c1 join course c2 on c1.semester=c2.semester where ame=VBand ame!=VB,15,、查询与李勇年龄相同的学生的姓名、所在系和年龄,select s2.sname,s2.sdept,s2.sage from student s1 join student s2 on s1.sage=s2.sage where s1.sname=,李勇,and s2.sname!=,李勇,4,16,、查询计算机系年龄最小的,2,名学生的姓名和年龄,select top 2 with ties sname,sage from student where sdept=,计算机系,order by sage asc,17,、查询,VB,考试成绩最高的前,2,名学生的姓名、所在系和,VB,成绩，包括并列的情况,select top 2 with ties sname,sdept,grade from student s join sc on s.sno=sc.sno join course c on o=o where cname=VB order by grade desc,18,、查询选课门数最多的前,2,名学生的学号和选课门数，包括并列的情况,select top 2 with ties sno,count(*)from sc,group by sno order by count(*)desc,19,、查询学生人数最多的系，列出系名和人数。,select top 1 sdept,count(*)from student,group by sdept order by count(*)desc,5,20,、用子查询实现如下查询：,1,）查询选修了”,C01“,课程的学生姓名和所在系,select sname,sdept from student,where sno in(select sno from sc where con=c01),2,）查询数学系成绩,80,分以上的学生的学号、姓名、选的课程号和成绩,select sno,sname,cno,grade from student join sc,on student.sno=sc.sno where sdept=,数学系,and sno in(select sno from sc where grade80),6,20,、,3,）查询计算机系考试成绩最高的学生的姓名,select sname from student s join sc on s.sno=sc.sno where sdept=,计算机系,and,grade=(select max(grade)from sc join student s on s.sno=sc.sno where sdept=,计算机系,),4,）查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩,select sname,sdept,ssex,grade from student s join sc on s.sno=sc.sno join course on o=o where cname=,数据结构,and grade=(select max(grade)from sc join count on o=o where cname=,数据结构,),7,21,、查询没有选修,VB,课程的学生的姓名和所在系,select sname sdept from student s join sc on s.sno=sc.sno join course c on o=o where cname!=VB,22,、查询计算机系没有选课的学生的姓名和所在系,select sname from student s left join sc on s.sno=sc.sno Where sdept=,计算机系,and o is null,8,23,、查询计算机系考试平均最低的学生的姓名以及所选的课程名,select sname,cname from student s join sc on s.sno=sc.sno join course c on o=o where sdept=,计算机系,and sno=(select top 1 sno from sc order by ave(grade)asc),9,24,、查询,1-5,学期中选课人数最少的课程的课程名、开课学期和学分,SELECTCname,Semester,Credit,FROMCourseWHERE(CnoIN,(SELECTTOP1WITHtiescnoFROMsc,GROUPBYcno,ORDERBYCOUNT(*)ASC)AND(SemesterBETWEEN1AND5),10,25,、,create table test_t(,col1 int,col2 char(10)not null,col3 char(10),insert into test_t(col2)values(b1),insert into test_t(col1,col2,col3)values(1,b2,c2),insert into test_t(col1,col2)values(2,b3),11,26,、删除考试成绩低于,50,分的学生的该门课程的选课记录,delete from sc where grade60 and sdept=,计算机系,12,29,、删除,VB,考试成绩最低的学生的,VB,选课记录,delete from sc join student s on s.sno=sc.sno join course c on o=o where cname=vb and grade=(select min(grade)from sc join student s on sc.sno=s.sno where cname=vb),13,30,、将第,2,学期开设的所有课程的学分增加,2,分,update course set credit=credit+2,where semester=2,31,、将,VB,课程的学分改为,3,分,update course set credit=credit+2,where cname=vb,32,、将计算机系学生的年龄增加,1,岁,update student set sage=sage+1,where sdept=,计算机系,14,33,、将信息系学生的“计算机文化学”课程的考试成绩加,5,分,update sc set grade=grade+5 where cno in(select cno from course where cname=,计算机系,)and sno in(select sno from student where sdept=,信息系）,34,、将选课人数最少的课程的学分降低,1,分,update course set credit=credit-1 where cno=(select top 1 cno from sc group by cno order by count(*)asc),15,第六章作业,9,、,1),在,student,表上为,sname,列建立一个聚集索引，索引名为,sldx,create clustered index sldx on student(sname),2),在,course,表上为,cname,列建立一个唯一的非聚集索引，索引名为,cnidx,create unique nonclustered index cnidx on course(cname),3),在,SC,表上为,SNO,和,CNO,建立一个组合的聚集索引，索引名为,snocnoidx,Create clustered index snocnoidx on sc(sno,cno),4),删除,SNAME,列上建立的,sldx,索引,Drop index sldx,16,12,、,1),查询学生的学号、姓名、所在系、课程名、课程号、课程学分,create view v1 as,select s.sno,sdept,cno,cname,credit from student s join sc on s.sno=sc.sno join course c on o=o,2),查询学生的学号、姓名、选修的课程名和考试成绩,create view v2 as,select s.sno,sname,cname,grade from student s join sc on s.sno=sc.sno join course c on o=o,3),统计每个学生的选课门数，要求列出学生学号和选课门数,create view v3 as select sno,count(*)as total from sc group by sno,4),统计每个学生的选课总学分，要求列出学生学号和总学分（说明：考试成绩大于等于,60,分才能获得此门课程的学分）,create view v4 as select sno,sum(credit)total_credit from sc join course c on o=o where grade=60 group by sno,17,13,、,1,）查询考试成绩大于等于,90,分得学生的姓名、课程名和成绩,select sname,cname,grade from v2 where grade=90,2),查询选课门数超过,3,门的学生的学号和选课门数,select*from v3 where total3,3),查询计算机系选课门数超过,3,门的学生的姓名和选课门数,select sname,total from v3 join student s on v3.sno=s.sno,4),查询修课总学分超过,10,分的学生的学号、姓名、所在系和修课总学分,select v4.sno,sname,sdept,total_credit from v4 join student s on s.sno=v4.sno where total_credit10,5),查询年龄大于等于,20,的学生中，修课总学分超过,10,分的学生的姓名、年龄、所在系和修课总学分,select sname,sage,sdept,total_credit from v4 join student s on v4.sno=s.sno where sage=20 and total_credit=10,18,14,、修改视图,v4,，使其查询每个学生的学号、总学分和总的选课门数,alter view v4,as,select sno,sum(credit),count(*)from sc join course c on o=o group by sno,19,第七章作业,6,、关系模式：学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）,语义：一个学生可以选多门课程，一门课程可以被多名学生选。一个学生有唯一的所在系，每门课程有唯一的课程名和学分,答：候选码为（学号，课程号），同时也是此关系模式的主键,分解：,学生表（,学号,，姓名，所在系，性别）,课程表（,课程号,，课程名，学分）,选课表（,学号，课程号,，成绩）,20,7,、关系模式：学生（学号，姓名，所在系，班号，班主任，系主任）,语义：一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。,答：候选码为学号，同时也是此关系模式的主键,分解：,学生表（,学号,，姓名，所在系，班号）,班级表（,班号,，班主任）,选课表（,所在系,，系主任）,21,8,、关系模式：教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数）,语义：一名课程（有课程号决定）有确定的课程名和学分，每名教师（有教师号决定）有确定的教师名，每门课程可以有多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数,答：候选码为（课程号，授课教师号），同时也是此关系模式的主键,分解：,课程表（,课程号,，课程名，学分）,教师表（,教师号,，教师名）,授课表（,课程号，教师号,，授课时数）,22,