13、面試題 · Mysql深入淺出

## 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.找出沒選過“黎明”老師的所有學生姓名。即: ![](https://img.kancloud.cn/1d/16/1d16fa2704fb089ab71d058360f8d2d6_152x71.png) 問題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)