MySQL数据库（实验分组与排序）答案-武洪萍主编

MySQL数据库（实验分组与排序）答案-武洪萍主编

#153页：分组与排序
1、统计有学生选修的课程的门数
SELECT COUNT(DISTINCT cno)
#from course;
FROM sc;

2、计算"C01"课程的平均成绩
SELECT AVG(degree)
FROM sc
WHERE cno='C01';

3、查询选修了"C03"课程的学生的学号及其成绩，查询结果按分数降序排列
SELECT sno,degree
FROM sc
WHERE cno='C03'
ORDER BY degree DESC;
 
4、查询各个课程号及相应的选课人数
SELECT cno,COUNT(*) 选课人数
FROM sc
GROUP BY cno; 

5、统计每门课程的选课人数和最高分
SELECT cno 课程号,COUNT(*) 选课人数,MAX(degree) AS 最高分
FROM sc
GROUP BY cno;

6、统计每个学生的选课门数和考试总成绩，并按选课门数降序排列
SELECT sno,COUNT(*) 选课门数,SUM(degree) 总成绩
FROM sc
GROUP BY sno
ORDER BY COUNT(*) DESC;

7、查询选修了3门以上课程的学生学号
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*)>3;

8、查询成绩不及格的学生学号及课程号，并按成绩降序排列
SELECT sno,cno
FROM sc
WHERE degree<60
GROUP BY sno
ORDER BY degree DESC;

9、查询至少选修一门课程的学生学号
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*)>=1;

10、统计输出各系学生的人数
SELECT sdept,COUNT(*)
FROM student
GROUP BY sdept;

11、统计各系的男、女生人数
SELECT sdept,ssex,COUNT(*)
FROM student
GROUP BY sdept,ssex;

12、统计各班级的学生人数
SELECT bno,COUNT(*)
FROM student
GROUP BY bno;

13、统计各班的男、女生人数
SELECT bno,ssex,COUNT(*)
FROM student
GROUP BY bno,ssex;

14、统计各系的老师人数，并按人数降序排列
SELECT tdept,COUNT(*)
FROM teacher	
GROUP BY tdept
ORDER BY COUNT(*) DESC;

15、统计不及格人数超过10的课程号
SELECT cno 课程号,COUNT(*) 选修人数
FROM sc
GROUP BY cno
HAVING COUNT(*)>10; 

16、查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序
SELECT *
FROM student
WHERE sdept='软件系' AND ssex='男'
ORDER BY sbirthday ASC, saddress DESC;