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