目录
一、第1关:查询统计一
1.1 任务描述
1、查询‘001’号的学生不及格的课程数,列出不及格课程数(列名为:scnt)
2、查询每个学生不及格的课程数,列出学号和不及格课程数(列名为:scnt)
3、查询每个系女同学的平均年龄,列出所在系和平均年龄(列名为:sageavg)
1.2 SQL语句
SELECT COUNT(*) AS scnt FROM SC
WHERE Sno='001' AND Grade < 60;
SELECT sno,COUNT(Sno) AS scnt FROM SC
WHERE Grade<60 GROUP BY Sno;
SELECT sdept,AVG(Sage) AS sageavg FROM Student
WHERE Ssex='f' GROUP BY Sdept;
二、第2关:查询统计二
2.1 任务描述
1、查询Niki(姓名)同学的平均分,列出平均分(列名为:savg)
2、查询CS系每个同学的平均分,列出学号和平均分(列名为:savg)
3、查询学分为2的每门课程的选课人数,列出课程号和选课人数(列名为:scnt)
2.2 SQL语句
SELECT AVG(Grade) AS savg FROM SC WHERE Sno=
(SELECT Sno FROM Student WHERE Sname='Niki');
SELECT sno,AVG(Grade) AS savg FROM SC WHERE Sno IN
(SELECT Sno FROM Student WHERE Sdept='CS')
GROUP BY Sno;
SELECT cno,COUNT(Sno) AS scnt FROM SC WHERE Cno IN
(SELECT Cno FROM Course WHERE Ccredit=2)
GROUP BY Cno;
三、第3关:查询统计三
3.1 任务描述
1、查询平均分最高的学生的姓名
2、查询不及格人数大于等于2人的课程,列出课程号,课程名,不及格人数(列名为scnt)
3.2 SQL语句
SELECT sname FROM Student
WHERE Sno IN (SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade) >=
ALL(SELECT AVG(Grade) FROM SC GROUP BY Sno));
SELECT Course.cno,cname,COUNT(Sno) AS scnt FROM SC,Course
WHERE Course.Cno=SC.Cno AND Grade<60
GROUP BY Course.cno,Cname
HAVING COUNT(Sno)>=2;
四、第4关:查询统计四
4.1 任务描述
查询E系平均成绩最高的同学的姓名,列出姓名。
4.2 SQL语句
SELECT sname FROM Student,SC
WHERE Sdept='E' AND SC.Sno=Student.Sno
GROUP BY sname
HAVING AVG(Grade)>=ALL(SELECT AVG(Grade) FROM SC
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept='E')
GROUP BY Sno);