参考答案：
(1)在表C中统计开设课程的教师人数.
Select Count(distinct TEACHER)
from C
(2)求选修C4课程的女学生的平均年龄.
Select AVG(SA)
from S
where Sex=0 and  S# in
  (select S#
   from SC
   where C#='C4')
(3)求每个学生都选修课程(已有成绩)的门数和平均成绩.
Select S#, AVG(GRADE), COUNT(C#)
from SC
Group by S#
 (4)统计每个学生选修课程的门数(超过5门的学生才统计).要求输出学生学号和选修门数,查询结果按门数降序排列,若门数相同,按学号升序排列.
Select COUNT(C#), S#
from SC
GROUP BY S#
HAVING COUNT(*)>5
ORDER BY COUNT(*) DESC, SC.S#  ASC
 (5)检索学号比WANG同学大,而年龄比他小的学生姓名.
方法一：
Select Sn
from S
where  S.S#>(select S1.S#
              from S as S1
              where S1.Sn='WANG')
and S.Sa< ( select S2.Sa
from S as S2
where S2.Sn='WANG')
 
  方法二：
Select Sn
from S as S1
where  Exists (select *
              from S as S2
              where S2.Sn='WANG') and S1.S#>S2.S# 
                         and S1.Sa

上一页3456下一页