数据库面试经典题

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

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

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

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

问题：

1，查询学号为1的学生所学课程的课程名与任课教师名 

2，查询至少选修LI老师所授课程中一门课程的学生姓名。

3，查询S3同学不选的课程的课程号与课程名。

4，统计选修每一门课程的学生的人数与平均成绩。

5，查询至少选修三门课程的学生姓名与学号。

6，找出没选过“LiMing”老师的所有学生姓名。

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

8，即学过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', 'Chinese', 'Zhang'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', 'Policy', 'Wang'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', 'English', 'Li'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', 'Math', 'Zhao'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', 'Physical', 'LiMing'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'S1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'S2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'S3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'S4'); 
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  CNAE,CTEACHER from C inner join SC on C.CNO=SC.CNO where SC.SNO=1;

问题2：select SNAME from S where SNO in(select SNO from SC inner join C on SC.CNO=C.CNO where C.CTEACHER='Li');

问题3：select CNO,CNAME from C where CNO not in(select CNO from SC inner join S on SC.SNO=S.SNO where S.SNAME='S3');

问题4：select CNO 课程号，count(*) 总人数, avg(SCGRADE) 平均成绩 from SC inner join S on SC.SNO=S.SNO group by CNO;

问题5：select  SNAME,SNO from S where SNO in(select SNO from SC group by SNO having count(SNO)>=3);