sql 面试题

表结构和数据如下：


学生表：student
id stuName
1  s1
2  s2
3  s3


课程表：course
id couName
1  chinese
2  math
3  english


成绩表：socre
id stuid couid score
1  1     1     50
2  1     2     78
3  1     3     89
4  2     1     67
5  2     2     88
6  2     3     76
7  3     1     90

8  3     3     99

问题：

1.数学比语文高

SELECT * from student where id in (
SELECT math.stuid from
(SELECT * from score where couid=2) math,
(SELECT * FROM score where couid=1) chinese
where
math.stuid = chinese.stuid and math.score>chinese.score
);


2.平均分大于60

SELECT * from student
where id in
(SELECT avgTest.stuid from
(SELECT stuid,AVG(score) as avgScore
from score GROUP BY stuid HAVING avgScore>60) avgTest
);