一道SQL题

最新推荐文章于 2022-04-16 00:43:39 发布

原创最新推荐文章于 2022-04-16 00:43:39 发布 · 803 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #java #c

上午笔试JAVA，其中三分之一是考SQL相关的，不过只有一道大题，感觉特经典

问题描述:
已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE为成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

本人的解法：（MySQL下均通过）

select SNAME from S where not exists(select 1 from C,SC

                 where C.CNO = SC.CNO

                 and S.SNO = SC.SNO

                 and C.CTEACHER = '李明');

select SNAME,AVG(SCGRADE) from S,SC as out_SC

where S.SNO = out_SC.SNO

and S.SNO = (select SNO from SC

             where SC.SCGRADE < 60

             group by SNO

             having count(CNO) >= 2)

group by SNAME;

3.（无奈MySQL不支持intersect）

(select SNAME from S,SC where S.SNO = SC.SNO and SC.CNO = 1)

intersect

(select SNAME from S,SC where S.SNO = SC.SNO and SC.CNO = 2)

select T1.SNO from 

(select S1.SNO,CNO,SCGRADE

 from S as S1,SC as SC1 

 where S1.SNO = SC1.SNO 

 and SC1.CNO = 1) as T1,

(select S2.SNO,CNO,SCGRADE

 from S as S2,SC as SC2 

 where S2.SNO = SC2.SNO 

 and SC2.CNO = 2) as T2 

where T1.SNO = T2.SNO 

and T1.SCGRADE > T2.SCGRADE;

select * from 

(select S1.SNO,SC1.SCGRADE as G1,SC2.SCGRADE as G2

 from S as S1,SC as SC1,S as S2,SC as SC2

 where S1.SNO = SC1.SNO 

 and S2.SNO = SC2.SNO 

 and S1.SNO = S2.SNO 

 and SC1.CNO = 1 

 and SC2.CNO = 2) as T 

where T.G1 > T.G2;

以上