sql server实验8 单表查询

1、①　查询平均成绩大于60 分的同学的学号和平均成绩（思路按学号分组计算学生的平均成绩，用having子句筛选出平均成绩大于60的学生）；select sno as 学号,avg(score) AS 平均成绩fromscGROUP BY SNOHAVING AVG(SCORE)>60

2、①　查询姓“刘”的老师的个数select count(*) as 姓刘教师个数from teacherwhere tname like '刘%'

3、①　查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分select cno as 课程ID,max(score) as 最高分,min(score) as 最低分from scgroup by cno

4、①　按各科平均成绩从低到高和及格率的百分数从高到低顺序，效果如下（思路：可以通过case when..then..else..end统计人数））：select cno as 课程ID,avg(score) as 平均成绩,sum(case when score>60 then 1 else 0 end)*100/count(*) as '及格率%'from scgroup by cnoorder by avg(score) desc

5、①　查询每门课程被选修的学生数select cno,count(*) as 选课人数from scgroup by cno

6、①　查询男生、女生人数方法一：select sum(case when ssex='男&#泌驾台佐39; then 1 else 0 end) as　男生总人数,sum(case when ssex='女' then 1 else 0 end) as　女生总人数from student

7、方法二：select ssex,count(*) as 人数from studentgroup by ssex