数据库的查询题目－－较难

设有学生选取修课程数据库：
S（S#, SNAME, AGE, SEX, DEPARTMENT, ADDRESS, BIRTHPLACE）
SC(S#, C#, GRADE)
C(C#, CNAME, TEACHER)
(1) 李老师所教的课程号、课程名称；
SELECT C#, CNAME;
FROM C;
WHERE TEACHER=”李”
(2) 年龄大于23岁的女学生的学号和姓名；
SELECT S#, SNAME;
FROM S;
WHERE AGE>23 AND SEX=”女”
(3) “李小波”所选修的全部课程名称；
SELECT CNAME;
FROM S, SC;
WHERE S.S#=SC.S# AND SNAME=’李小波’
(4) 所有成绩都在80分以上的学生姓名及所在系；
SELECT SNAME, DEPARTMENT;
FROM S, SC;
WHERE S.S#=SC.S# AND SC.S# ;
not in ( SELECT S#;
FROM SC;
WHERE GRADE<80 )
(5) 没有选修“操作系统”课的学生的姓名；
SELECT SNAME ;
FROM S ;
WHERE S# NOT IN
(SELECT S# ;
FROM SC, C ;
WHERE SC.C#=C.C# AND CNAME=’操作系统’ )
(6) 与“李小波”同乡的男生姓名及所在系；
Select cname from c where c# in (select C# from sc where s# in
(select s# from s where sname=’李小波’))
(7) 英语成绩比数学成绩好的学生；
Select sname from S
Where s# in (select X.s# from SC X inner join SC Y on X.S#=Y.S#
Where X.grade > Y.grade and X.C#=(select c# from c where cname=’英语’)
And Y.c#=(select c# from c where cname=’数学’));
(8) 选修同一门课程时，女生比男生成绩好的学生名单；

Select sname from S
Where s# in (select X.s# from SC X inner join SC Y on X.S#=Y.S#
Where (X.C#=Y.C#) and (X.grade > Y.grade) and X.s#=(select s# from s where sex=’女’)
And Y.s#=(select s# from s where sex=’男’));

(9) 至少选修两门以上课程的学生姓名、性别；
Select sname,sex from s where s# in (select s# from sc group by s# having count(*)>=2);
(10) 选修了李老师所讲课程的学生人数；
Select count(*) from sc where c# in (select c# from c where teacher=’李老师’);
(11) 没有选修李老师所讲课程的学生；
Select sname from s where s# in (select s# from sc
where c# not in (select c# from c where teacher=’李老师’));
(12) “操作系统”课程得最高分的学生姓名、性别、所在系；
Select sname,sex,deptment from s where s# in (select s# from sc
where (c# in (select c# from c Where cname=’操作系统’))
and grade in (select max(grade) from sc group by c#));