## 13、面试题 有3个表S(学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩） 问题： 1，找出没选过“黎明”老师的所有学生姓名。 2，列出2门以上（含2门）不及格学生姓名及平均成绩。 3，即学过1号课程又学过2号课所有学生的姓名。 请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。 ``` CREATE TABLE SC ( SNO VARCHAR(200), CNO VARCHAR(200), SCGRADE VARCHAR(200) ); CREATE TABLE S ( SNO VARCHAR(200 ), SNAME VARCHAR(200) ); CREATE TABLE C ( CNO VARCHAR(200), CNAME VARCHAR(200), CTEACHER VARCHAR(200) ); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); commit; INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); commit; INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); commit; ``` 问题1.找出没选过“黎明”老师的所有学生姓名。 即:  问题2:列出2门以上（含2门）不及格学生姓名及平均成绩。 问题3:即学过1号课程又学过2号课所有学生的姓名。 问题1.找出没选过“黎明”老师的所有学生姓名。 思路 1.获取黎明老师所教的课的编号 select CNO from c where CTEACHER='黎明' 2. 根据步骤1的查询结果作为条件 获取所有选过黎明老师课程的学生编号 select sno from sc where cno=(select CNO from c where CTEACHER='黎明') 3. 根据步骤2的查询结果获取所有学生编号不在这个范围内的所有学生 select * from s where sno not in( select sno from sc where cno=(select CNO from c where CTEACHER='黎明')) 问题2:列出2门以上（含2门）不及格学生姓名及平均成绩。 思路 1.获取所有的低于60分记录 select * from sc where scgrade <60 2.在步骤1的基础之上根据学生编号进行分组然后筛选掉统计数量低于2的所有记录, 这就可以获取到所有不及格科目大于等于2的所有学生编号 select sno from sc where scgrade <60 group by sno having count(sno)>=2 3.使用学生表和科目进行多表查询然后根据学生编号分组取平均成绩 select s.sno,s.sname,avg(sc.scgrade) from s inner join sc on s.sno=sc.sno group by s.sno 4.在步骤3的基础上添加where条件 获取sno为步骤2查询结果了 select s.sno,s.sname,avg(sc.scgrade) from s inner join sc on s.sno=sc.sno where s.sno in(select sno from sc where scgrade <60 group by sno having count(sno)>=2) group by s.sno 问题3:即学过1号课程又学过2号课所有学生的姓名。 思路 1.获取所有学习过课程1 或者 是学习过课程2的所有信息 select * from sc where cno =1 or cno=2 2.在步骤1的基础上使用sno进行分组 ，并且使用having 筛选掉统计数量不是2的所有记录 （及学习过1也学习过2的所有记录） select sno from sc where cno =1 or cno=2 group by sno having count(sno)>=2 3.使用步骤2的查询结果作为条件，查询s表中所有sno在这个范围内的所有学生 select * from s where sno in ( select sno from sc where cno =1 or cno=2 group by sno having count(sno)>=2)