經典SQL面試題1

經典SQL面試題1

有3個表S，C，SC

S（SNO，SNAME）代表（學號，姓名）

C（CNO，CNAME，CTEACHER）代表（課號，課名，教師）

SC（SNO，CNO，SCGRADE）代表（學號，課號，成績）

問題：

1，找出沒選過“黎明”老師的所有學生姓名。

2，列出2門以上（含2門）不及格學生姓名及平均成績。

3，既學過1號課程又學過2號課所有學生的姓名。

答：

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;

SELECT SNO,SNAME FROM S;

SELECT CNO,CNAME,CTEACHER FROM C;

SELECT SNO,CNO,SCGRADE FROM SC;

問題1.找出沒選過“黎明”老師的所有學生姓名。

第一步:求黎明老師教的所有課的課號

select distinct cno from c where cteacher='黎明';

第二步:選了黎明老師的所有學生的編號

select sno from sc where cno in (

第一步的結果

);

第三步:沒有選黎明老師的所有學生的姓名

select sname from s where sno not in (

第二步的結果

);

即:

select sname from s where sno not in

(

  select sno from sc where cno in

  (

    select distinct cno from c where cteacher='黎明'

  )

);

問題2:列出2門以上（含2門）不及格學生姓名及平均成績。

第一步:2門以上不及格的學生的學號

select sno from sc where scgrade < 60 group by sno having count(*) >= 2;

第二步:每個學生平均分

select sno, avg(scgrade) as avg_grade from sc group by sno

第三步:第一步中得到的學號對應的學生姓名以及平均分

select s.sname ,avg_grade from s

    join

         第一步的結果

         on s.sno = t1.sno

    join

        第二步的結果

        on s.sno = t2.sno

即:

select s.sname, avg_grade from s

join

(select sno from sc where scgrade < 60 group by sno having count(*) >= 2) t1

on s.sno = t1.sno

join

(select sno, avg(scgrade) as avg_grade from sc group by sno ) t2

on s.sno = t2.sno;

問題3:既學過1號課程又學過2號課所有學生的姓名。

第一步:學過1號課程的學號

select sno from sc where cno = 1; 

第二步:學過2號課程的學號

select sno from sc where cno = 2;

第三步:即學過1號課程又學過2號課的學號

select sno from sc where cno =1 and sno in (select sno from sc where cno = 2);

第四步:得到姓名

select sname from s where sno in

(

  select sno from sc where cno = 1 and sno in (select sno from sc where cno = 2)

);

或者:

select sname from s where

  sno in (select sno from sc where cno = 1)

  and

  sno in (select sno from sc where cno = 2);