SQL语句的一个面试题目

昨天去面试了家公司，笔试题别的都挺好，因为近几年项目中用ORM框架比较多sql语句答题部分一塌糊涂，而这家公司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”号课的成绩

解题：

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

select sname as 学生姓名 from s where s.sno not exists 
(select sc.sno from sc ,c where sc.cno=c.cno and c.cteacher =’李明’)

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

select s.sname as 学生姓名, AVG（sc.scgrade）as 平均成绩 from s,sc 
where sc.son in(select sc.sno from sc where sc.scgrade <60 group by sc.sno  having count(*)>1) 
group by s.sno , s.sname 

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

select s.sname as 学生姓名 from s where s.son in 
( select sc.sno where sc.cno in(‘01’,‘02’) group by sc.sno having count(distinct sc.cno)==2)

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

select s.sno as 学号 from
 (select * from sc where sc.cno=1) as sc1,
 select * from sc where sc.con=2) as sc2 
join s on s.sno=s1.sno 
where sc1.sno=sc2.sno and sc1.scgrade>sc2.scgrade

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

select s.sno as 学号, sc1.scgrade as no1g,sc2.scgrade as no2g from
 (select * from sc where sc.cno=1) as sc1, 
select * from sc where sc.con=2) as sc2 
join s on s.sno=s1.sno
 where sc1.sno=sc2.sno and sc1.scgrade>sc2.scgrade

第四个和第五个的考察点，个人感觉应该是效率，不应该这么处理，但暂时也没答案，出题人应该不至于这么弱智，以后想明白再更新吧