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

1、第五章作业1、查询学生选课表中的全部数据select * from sc2、查询计算机系学生的姓名、年龄select sname,sage from studentwhere sdept=计算机系 3、查询成绩在 70-80的学生的学号、课程号和成绩select * from sc where grade between 70 to 804、查询计算机系年龄在 18-20的男生的姓名、年龄select sname,sage from studentWhere sdept=计算机系 and sage between 18 to 20 and ssex=男 5、查询 “C01”课程的最高成绩Sel

2、ect max(grade) from sc where cno=c016、查询计算机系学生的最大年龄和最小年龄select max(sage),min(sage) from studentwhere sdept=计算机系 7、统计每个系的学生人数select sdept,count(*) from student group by sdept8、统计每门课程的选课人数和考试最高分select cno,count(*),max(grade) from sc group by cno9、统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果select sno,count(*),sum(g

3、rade) from sc group by sno order by count(*) asc10、查询总成绩超过 200分的学生，要求列出学号、总成绩select sno,sum(grade) from scgroup by sno having sum(grade)20011、查询选修了 ”C02“课程的学生的姓名和所在系select sname,sdept from student s join sc on s.sno=sc.sno where sc.con=c0212、查询成绩 80分以上的学生的姓名、选的课程号和成绩，并按成绩降序排列结果select sname,cno,grade

4、 from student s join scon s.sno=sc.sno where grade 80 order by grade desc13、查询哪些学生没有选课，要求列出学号、姓名和所在系select s.sno,sname,sdept from student s left join sc on s.sno=sc.sno where o is null14、查询与 VB在同一学期开设的课程的课程名和开课学期select ame,c1.semester from course c1 join course c2 on c1.semester=c2.semester where am

5、e=VBand ame!=VB15、查询与李勇年龄相同的学生的姓名、所在系和年龄select s2.sname,s2.sdept,s2.sage from student s1 join student s2 on s1.sage=s2.sage where s1.sname=李勇 and s2.sname!=李勇 16、查询计算机系年龄最小的 2名学生的姓名和年龄select top 2 with ties sname,sage from student where sdept=计算机系 order by sage asc17、查询 VB考试成绩最高的前 2名学生的姓名、所在系和VB成绩，包

6、括并列的情况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 desc18、查询选课门数最多的前 2名学生的学号和选课门数，包括并列的情况select top 2 with ties sno,count(*) from scgroup by sno order by count(*) desc19、查询学生人数最多的系，列出系名和人数。select top 1 sdept,count(

7、*) from studentgroup by sdept order by count(*) desc20、用子查询实现如下查询：1）查询选修了 ”C01“课程的学生姓名和所在系select sname,sdept from studentwhere sno in(select sno from sc where con=c01)2）查询数学系成绩 80分以上的学生的学号、姓名、选的课程号和成绩select sno,sname,cno,grade from student join scon student.sno=sc.sno where sdept=数学系 and sno in (sel

8、ect sno from sc where grade80)20、 3）查询计算机系考试成绩最高的学生的姓名select sname from student s join sc on s.sno=sc.sno where sdept=计算机系 andgrade=(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=

9、sc.sno join course on o=o where cname=数据结构 and grade=(select max(grade) from sc join count on o=o where cname=数据结构)21、查询没有选修 VB课程的学生的姓名和所在系select sname sdept from student s join sc on s.sno=sc.sno join course c on o=o where cname!=VB22、查询计算机系没有选课的学生的姓名和所在系select sname from student s left join sc on

10、s.sno = sc.sno Where sdept = 计算机系 and o is null23、查询计算机系考试平均最低的学生的姓名以及所选的课程名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)24、查询 1-5学期中选课人数最少的课程的课程名、开课学期和学分SELECT Cname, Semester, Credit FROM Course WHERE (Cno IN (SELECT TOP 1 WITH ties cno FROM sc GROUP BY cno ORDER BY COUNT(*) ASC) AND (Semester BETWEEN 1 AND 5)