数据库的查询题目－－较难
设有学生选取修课程数据库：S（S#, SNAME, AGE, SEX, DEPARTMENT, ADDRESS, BIRTHPLACE）
SC(S#, C#, GRADE)
C(C#, CNAME, TEACHER)
(1)李老师所教的课程号、课程名称；
SELECTC#, CNAME;
FROMC;
WHERETEACHER=”李”
(2)年龄大于23岁的女学生的学号和姓名；
SELECTS#, SNAME;
FROMS;
WHEREAGE>23ANDSEX=”女”
(3)“李小波”所选修的全部课程名称；
SELECTCNAME;
FROMS,SC;
WHERES.S#=SC.S#ANDSNAME=’李小波’
(4)所有成绩都在80分以上的学生姓名及所在系；
SELECTSNAME, DEPARTMENT;
FROMS, SC;
WHERES.S#=SC.S#ANDSC.S# ;
notin ( SELECTS#;
FROMSC;
WHEREGRADE<80 )
(5)没有选修“操作系统”课的学生的姓名；
SELECTSNAME ;
FROMS ;
WHERES#NOTIN
(SELECTS# ;
FROMSC, C ;
WHERESC.C#=C.C#ANDCNAME=’操作系统’ )
(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#));
页:
[1]