SQL面试题

有3个表S(学生表)，C（课程表），SC（学生选课表）

        S（SNO，SNAME）代表（学号，姓名）

        C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

        SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

        1，找出没选过“黎明”老师的所有学生姓名。

        2，列出2门以上（含2门）不及格学生姓名及平均成绩。

        3，即学过1号课程又学过2号课所有学生的姓名。

        请用标准SQL语言写出答案

相应建表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.找出没选过“黎明”老师的所有学生姓名。

        第一步：查询'黎明'教师的课程编号

        SELECT CNO FROM C WHERE CTEACHER = '黎明';

                

        第二步：查询所有选过黎明教师课程的学生       

        SELECT

                S.SNAME

        FROM

                S

        LEFT JOIN

                SC

        ON

                S.SNO = SC.SNO

        WHERE

                SC.CNO = (SELECT CNO FROM C WHERE CTEACHER = '黎明');

                 

        第三步：查询没有选过'黎明'老师的所有学生

        SELECT

                SNAME

        FROM

                S

        WHERE

                SNAME NOT IN (SELECT S.SNAME FROM S LEFT JOIN SC ON S.SNO = SC.SNO WHERE SC.CNO = (SELECT CNO FROM C WHERE CTEACHER = '黎明'));

                

问题2:列出2门以上（含2门）不及格学生姓名及平均成绩。

第一步：查询2门及2门以上不及格的学生

SELECT

        S.SNO,S.SNAME,COUNT(SC.SCGRADE)

FROM

        SC

JOIN

        S 

ON

        SC.SNO = S.SNO

WHERE 

        SC.SCGRADE < 60

GROUP BY

        SC.SNO

HAVING

        COUNT(SC.SCGRADE) >= 2;

        

第二步：求成绩的平均值

SELECT

        SNO,AVG(SCGRADE) AVGSCGRADE

FROM

        SC

GROUP BY

        SNO;

         

第三步：将第一步查询结果作为临时表t1，将第二步查询结果作为临时表t2，进行连接查询

SELECT

        t1.*,t2.AVGSCGRADE

FROM

        (SELECT B.SNO,B.SNAME,COUNT(A.SCGRADE) FROM SC A JOIN S B ON A.SNO = B.SNO WHERE  A.SCGRADE < 60 GROUP BY A.SNO HAVING COUNT(A.SCGRADE) >= 2) t1

JOIN

        (SELECT SNO,AVG(SCGRADE) AVGSCGRADE FROM SC GROUP BY SNO) t2

ON

        t1.SNO = t2.SNO;

        

问题3:即学过1号课程又学过2号课所有学生的姓名。

第一步：查询选过1号课程的学生

SELECT S.SNO,S.SNAME FROM SC JOIN S ON SC.SNO = S.SNO WHERE CNO = 1;

        

第二步：查询选过2号课程的学生

SELECT S.SNO,S.SNAME FROM SC JOIN S ON SC.SNO = S.SNO WHERE CNO = 2;

         

第三步：两表连接查询得到即选过1号课程又选过2号课程的学生

SELECT

        T1.SNO,T1.SNAME

FROM

        (SELECT S.SNO,S.SNAME FROM SC JOIN S ON SC.SNO = S.SNO WHERE CNO = 1) T1

JOIN

        (SELECT S.SNO,S.SNAME FROM SC JOIN S ON SC.SNO = S.SNO WHERE CNO = 2) T2

ON

        T1.SNO = T2.SNO;

         

说明：用红色字体显示的SQL语句是将前面几步的SQL语句全部包括在内的完整SQL语句。