(1)统计有学生选修的课程的门数。
(2)计算 “CO1”课程的平均成绩。
(3)查询选修了“C03”课程的学生的学号及其成绩,查询结果按分数降序排列。
(4)查询各个课程号及相应的选课人数。
(5)统计每门课程的选课人数和最高分。
(6)统计每个学生的选课门数和考试总成绩,并按选课门数降序排列。
(7)查询选修了3门以上课程的学生学号。
(8)查询成绩不及格的学生学号及课程号,并按成绩降序排列。
(9)查询至少选修-一门课程的学生学号。
(10)统计输出各系学生的人数。
(11)统计各系的男、女生人数。
(12)统计各专业的学生人数。
(13)统计各专业的男、女生人数。
(14)统计各系的老师人数,并按人数降序排序。
(15)统计不及格人数超过10的课程号。
(16)查询“自己”所在系的学生信息,查询结果按出生日期升序排序,出生日期相同的按地址降序排序。
统计cno的数量
1、SELECT COUNT(DISTINCT cno) FROM sc;
2、SELECT AVG(DISTINCT degree) FROM sc WHERE cno='c01';
以degree 的desc降序排序
3、SELECT sno,degree FROM sc WHERE cno='c03' ORDER BY degree DESC;
归类相同的cno组,计算组中的人数sno
4、SELECT cno,COUNT(DISTINCT sno) FROM sc GROUP BY cno;
归类所有的cno,计算其中的值
5、SELECT cno,COUNT(DISTINCT sno),MAX(DISTINCT degree) FROM sc GROUP BY cno;
6、SELECT sno,COUNT(DISTINCT cno),SUM(degree) FROM sc GROUP BY sno ORDER BY COUNT(cno) DESC;
7、SELECT sno FROM sc GROUP BY sno HAVING COUNT(cno)>3;
8、SELECT sno,cno,degree FROM sc WHERE degree<60 ORDER BY degree DESC;
9、SELECT sno FROM sc GROUP BY sno HAVING COUNT(cno)>1;
10、SELECT sdept,COUNT(*) FROM student GROUP BY sdept;
11、SELECT ssex,sdept,COUNT(*) FROM student GROUP BY ssex,sdept;
12、SELECT sdept,COUNT(*) FROM student GROUP BY sdept;
13、SELECT sdept,ssex,COUNT(*) FROM student GROUP BY sdept,ssex;
14、SELECT tname,COUNT(*) FROM teacher GROUP BY tdept ORDER BY COUNT(*) DESC;
15、SELECT cno,COUNT(*) FROM sc WHERE degree<60 GROUP BY cno HAVING COUNT(*)>10 ;
16、SELECT * FROM student WHERE sdept='软件工程系' ORDER BY YEAR(sbirthday) DESC,saddress DESC;