软件工程师招聘笔试题之多表查询

数据库有以下表

S(SNO,SNAME)即学号，姓名

C(CNO,CNAME,CTEACHER)课程号，课程名，授课老师

SC(SNO,CNO,SCGRADE)学号，课程号，成绩

1.没有选李明老师的课的所有学生。
select SNAME from s where SNO not in (

select SNO from sc LEFT JOIN c on sc.CNO=c.CNO where CTEACHER ="李明")
2.至少两门课（包括两门）及格的学生的姓名及平均分。
select s.SNAME, AVG(sc.SCGRADE) from sc left join s on sc.SNO=s.SNO where sc.SCGRADE>=60 group by sc.SNO HAVING count(*)>=2
3.选了1号课程又选了2号课程的学生姓名。
select SNAME from s where SNO in (

select sc1.SNO from sc as sc1 join sc as sc2 on sc1.SNO=sc2.SNO

where sc1.SNO=sc2.SNO and sc1.CNO='1' and sc2.CNO='2')

4.1号课程的分数比2号课程高的学生的姓名。

select SNAME from s where SNO in (

select sc1.SNO from sc as sc1 join sc as sc2 on sc1.SNO=sc2.SNO

where sc1.SNO=sc2.SNO and sc1.CNO='1' and sc2.CNO='2' and sc1.SCGRADE>sc2.SCGRADE)