查询与维护学生信息管理数据表（分组与排序）

（1） 计算“C01”课程的平均成绩。

select count(distinct "cno") as "选课门数" from "sc";

 

（2） 统计有学生选修的课程的门数。

select avg("degree") from "sc";

 

（3）查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列。

select "sno","degree" from "sc" where "cno"='c03' order by "degree" desc;

 

（4）查询各个课程号及相应的选课人数。

select "cno",count(*) as "选课人数" from "sc" group by "cno";

 

（5）统计每门课程的选课人数和最高分。

select "cno",count(*) as "选课人数",max("degree") as "最高分" from "sc" group by "cno","degree";

 

（6）统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。

select "sno",count(*) as "选课门数",sum("degree") as "总成绩" from "sc" group by "sno" order by count(*) desc;

 

（7）查询选修了3门以上课程的学生学号。

select "sno",count(*) from "sc" group by "sno" having count(*)>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(*) >=1;

 

（10）统计输出各系学生的人数。

select "sdept",count(*) from "student_copy" group by "sdept";

 

（11）统计各系学生人数。

select "sdept",count(*) from "student_copy" group by "sdept";

 

（12）统计各系的男、女生人数。

select "sdept"，"ssex",count(*) as "人数" from "student_copy" group by "sdept","ssex";

 

（13）统计各班级的学生人数。

select "classno",count(*) "各班人数" from "student" group by "classno";

 

（14）统计各班的男、女生人数。

select "classno","ssex",count(*) from "student" group by "classno","ssex";

 

（15）统计各系的老师人数，并按人数降序排序。

select "deptno",count(*) as "老师人数" from "teacher" group by "deptno" order by count(*) desc;

 

（16）统计不及格人数超过10人的课程号。

select "cno" as "课程号",count(*) as "不及格人数" from "sc" group by "cno" having count(*) > 10;

 

（17）查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序。

select * from "student_copy" where "sdept" = '软件工程系' and "ssex" = '男' order by "sbirthday" asc,"saddress" desc;