已知一个教学管理数据库中有四个表:
Student (sno,sname,ssex,birthday,class)
Teacher (tno,tname,sex,birthday,depart)
Course (cno,cname,tno)
Score (sno,cno,grade)
查询至少有两名男生的班级,并且按照班级的升序排列
查询2门以上(包括两门)成绩在90分以上的同学姓名和学号
SOS!!!马上考试了,拜托解答一下吧!!!!
參考答案:1:
SELECT CLASS FROM (
SELECT COUNT(SSEX) AS Expr1, CLASS
FROM Student
WHERE (SSEX = 1)
GROUP BY CLASS
HAVING (COUNT(SSEX) >= 2)
) AS A ORDER BY CLASS
2:
select sno,sname from student where sno in (
SELECT SNO
FROM (SELECT SNO, COUNT(GRADE) AS Expr1
FROM SCORE
WHERE (GRADE >= 90)
GROUP BY SNO
HAVING (COUNT(GRADE) >= 2)) AS A
)
