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

someyuan

设有学生选取修课程数据库：
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#));