数据库实验五——分组查询

一、统计学生选修表，统计每门课程的选修人数。

SELECT Cno,COUNT(*) AS number
FROM sclj
GROUP BY Cno

二、统计学生选修表，统计每个同学的总成绩。

SELECT Sno,SUM(Grade) AS total
FROM sclj
GROUP BY Sno

 

三、统计学生表，统计每个系中每种性别的学生人数，并按系升序排序。

SELECT Sdept AS xi,Ssex as gender,COUNT(*) AS number
FROM studentlj
GROUP BY Sdept,Ssex
ORDER BY Sdept

 四、统计学生选修表，统计每门课程的平均成绩，并按成绩降序排序。

SELECT Cno,AVG(Grade) as avge
FROM sclj
GROUP BY Cno
ORDER BY AVG(Grade) DESC

 五、统计学生选修表，显示有两门及以上课程不及格的学生的学号。

SELECT Sno fail
FROM sclj
WHERE Grade<60
GROUP BY Sno
HAVING COUNT(*)>1

 

六、统计学生表，统计每个系的最大年龄是多少。

SELECT Sdept AS xi,MAX(Sage) AS old
FROM studentlj
GROUP BY Sdept