动力节点 mysql 郭鑫_动力节点 mysql 郭鑫 34道经典的面试题二

13.有3个表S(学生表)，C(课程表)，SC(学生选课表)

S(SNO，SNAME)代表(学号，姓名)

C(CNO，CNAME，CTEACHER)代表(课号，课名，教师)

SC(SNO，CNO，SCGRADE)代表(学号，课号，成绩)

问题：1、找出没选过“黎明”老师的所有学生姓名。2、列出2门以上(含2门)不及格学生姓名及平均成绩。3、即学过1号课程又学过2号课所有学生的姓名。create tables(

snoint(4) primary keyauto_increment,

snamevarchar(32)

);insert into s(sname) values('zhangsan');insert into s(sname) values('lisi');insert into s(sname) values('wangwu');insert into s(sname) values('zhaoliu');create tablec(

cnoint(4) primary keyauto_increment,

cnamevarchar(32),

cteachervarchar(32)

);insert into c(cname,cteacher) values('Java','吴老师');insert into c(cname,cteacher) values('C++','王老师');insert into c(cname,cteacher) values('C##','张老师');insert into c(cname,cteacher) values('MySQL','郭老师');insert into c(cname,cteacher) values('Oracle','黎明');create tablesc(

snoint(4),

cnoint(4),

scgradedouble(3,1),constraint sc_sno_cno_pk primary key(sno,cno),constraint sc_sno_fk foreign key(sno) referencess(sno),constraint sc_cno_fk foreign key(cno) referencesc(cno)

);insert into sc(sno,cno,scgrade) values(1,1,30);insert into sc(sno,cno,scgrade) values(1,2,50);insert into sc(sno,cno,scgrade) values(1,3,80);insert into sc(sno,cno,scgrade) values(1,4,90);insert into sc(sno,cno,scgrade) values(1,5,70);insert into sc(sno,cno,scgrade) values(2,2,80);insert into sc(sno,cno,scgrade) values(2,3,50);insert into sc(sno,cno,scgrade) values(2,4,70);insert into sc(sno,cno,scgrade) values(2,5,80);insert into sc(sno,cno,scgrade) values(3,1,60);insert into sc(sno,cno,scgrade) values(3,2,70);insert into sc(sno,cno,scgrade) values(3,3,80);insert into sc(sno,cno,scgrade) values(4,3,50);insert into sc(sno,cno,scgrade) values(4,4,80);

首先分析下上面表的设计

一个学生可以选修多门课程

同一门课程可以被多个学生选择

学生和课程之间是多对多的关系

所以就要引入第三张中间表来解决学生和课程之间的关系

create tablesc(

snoint(4),

cnoint(4),

scgradedouble(3,1),constraint sc_sno_cno_pk primary key(sno,cno),constraint sc_sno_fk foreign key(sno) referencess(sno),constraint sc_cno_fk foreign key(cno) referencesc(cno)

);

第三张表的sno的值必须来自学生表，使用外键约束

cno必须来自课程表，使用外键约束

然后建立一个sno 和cno的一个复合主键

接下来我们就可以做题了

/*

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

2、列出2门以上(含2门)不及格学生姓名及平均成绩。

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

*/

第一题的第一种做法：

--先找出选过黎明老师的学生编号 -> 黎明老师的授课的编号

select cno from c where cteacher = '黎明';select sno from sc where cno = (select cno from c where cteacher = '黎明');select * from s where sno not in(select sno from sc where cno = (select cno from c where cteacher = '黎明'));

第一题的第二种做法：

第一步：找到黎明老师所上课对应的课程对应的课程编号select cno from c where cteacher = '黎明';

第二步：求出那些学生选修了黎明老师的课程selectsnofromscjoin(select cno from c where cteacher = '黎明') tonsc.cno=t.cno;

第三步：求出那些学生没有选择黎明老师的课selectsno,snamefromswheresnonot in(selectsnofromscjoin(select cno from c where cteacher = '黎明') tonsc.cno=t.cno

);

+-----+---------+

| sno | sname |

+-----+---------+

| 3 | wangwu |

| 4 | zhaoliu |

+-----+---------+

第二题：

2、列出2门以上(含2门)不及格学生姓名及平均成绩。

思路一 ：在sc表中首先按照学生编号进行分组，得到哪些学生的有两门以上的成绩低于60分

selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60

group bysc.snohavingstudentNum>= 2;

第二步：查询出该学生对应的编号selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60

group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno;

+-----+----------+

| sno | sname |

+-----+----------+

| 1 | zhangsan |

+-----+----------+

1 row in set

接下来需要获得该学生的平均成绩，我们得到该学生的sno的值是1，我们需要在sc表中求出该学生的平均成绩，首先需要将上面的表和sc表关联起来形成一个临时表，然后对这个临时表按照sno进行group by

我们来看下下面的代码

selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60

group bysc.snohavingstudentNum>= 2;

第二步：查询出该学生对应的编号selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60

group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno;

第三步得到该学生的平均成绩，把上面的表当成临时表mselectm.sno,m.sname,avg(d.scgrade)fromscasdjoin(selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60

group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno

)asmonm.sno=d.snogroup byd.sno ;

+-----+----------+----------------+

| sno | sname | avg(d.scgrade) |

+-----+----------+----------------+

| 1 | zhangsan | 64.00000 |

+-----+----------+----------------+

1 row in set

第三题：

selects.snamefromscjoinsonsc.sno=s.snowherecno= 1 and sc.sno in(select sno from sc where cno = 2);

+----------+

| sname |

+----------+

| zhangsan |

| wangwu |

+----------+

不能写成下面的形式会存在错误