有学生头选课系统,数据库中有四个表,分别为: Student(S#, Sname, Sage, Ssex)

1

select a.S#, avg(b.Score) Student a,SC b where a.S#=b.S# group by a.S#

having avg(b.Score) >60

2

select a.S#, avg(b.Score) Student a,SC b where a.S#=b.S# and S# IN

(

select S# from SC where Score <60 group by S# having count(*) >2

)

group by a.S#

3

select S#,Sname from where S# not in

(select a.S# from Student a,Course b,SC c,Teacher d

where

a.S#=c.S# and

b.C#=c.C# and

b.T#=d.T# and

d.Tname='叶平')

1. 查询成绩介于80到90之间学生的学号、姓名、性别和班级信息

SELECT 学号,姓名,性别,班级 from 学生表 WHERE 学号=(SELECT 学号 from 成绩表 where 成绩 between 80 and 90)

2. 查询每个人的选课门数

COUNT ( select distinct 课程号 from 成绩表 where 学号 = 要查询学生的学号)

3. 查询每门课的选课人数

COUNT ( select distinct 学号 from 成绩表 where 课程号 = 要查询课程的课程号)

---