有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);