实验5 分组查询

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

SELECT Cno,COUNT(*) AS yyx
FROM `sc-yyx` 
GROUP BY Cno

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

SELECT Sno,SUM(Grade) AS yyx
FROM `sc-yyx` 
GROUP BY Sno

 

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

SELECT Sdept AS yyx,Ssex AS yyh,COUNT(*) AS yjh
FROM `student-yyx` 
GROUP BY Sdept,Ssex
ORDER BY Sdept

 

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

SELECT Cno,AVG(Grade) as yyx
FROM `sc-yyx` 
GROUP BY Cno
ORDER BY AVG(Grade)DESC

 

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

SELECT Sno
FROM `sc-yyx` 
WHERE Grade<60
GROUP BY Sno
HAVING COUNT(*)>1

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

SELECT Sdept as yyx,MAX(Sage) AS yyh
FROM `student-yyx` 
GROUP BY Sdept