文章目录
问题15:面试题
第一题:找出没有选过’‘黎明’'老师的所有学生姓名
第二题:列出2门以上(含2门)不及格学生姓名及平均成绩
第三题:既学过1号课程又学过2号课程的所有的学生姓名
问题15:面试题
有三个表,表S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,老师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1.找出没有选过’‘黎明’'老师的所有学生姓名
2.列出2门以上(含2门)不及格学生姓名及平均成绩
3.既学过1号课程又学过2号课程的所有的学生姓名
drop table if exists s;
drop table if exists c;
drop table if exists sc;
create table s(
sno int(10) primary key,-- 学号
sname varchar(14)-- 姓名
);
create table c(
cno int(10) primary key,-- 课号
cname varchar(14),-- 课名
cteacher varchar(14)-- 成绩
);
create table sc(
sno int(10),-- 外键,学号
cno int(10),-- 外键,课号
scgrade int(10),-- 成绩
primary key(sno,cno),-- 联合主键
CONSTRAINT sc_s_fk FOREIGN KEY(sno) REFERENCES s(sno),-- 外键约束
CONSTRAINT sc_c_fk FOREIGN KEY(cno) REFERENCES c(cno)-- 外键约束
);
insert into s(sno,sname) values(1,'a');
insert into s(sno,sname) values(2,'b');
insert into s(sno,sname) values(3,'c');
insert into s(sno,sname) values(4,'d');
insert into c(cno,cname,cteacher) values(1,'java','王老师');
insert into c(cno,cname,cteacher) values(2,'C++','张老师');
insert into c(cno,cname,cteacher) values(3,'C#','李老师');
insert into c(cno,cname,cteacher) values(4,'mysql','周老师');
insert into c(cno,cname,cteacher) values(5,'oracle','黎明');
insert into sc(sno,cno,scgrade) values(1,1,50);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,50);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,70);
insert into sc(sno,cno,scgrade) values(2,4,59);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,61);
insert into sc(sno,cno,scgrade) values(3,3,99);
insert into sc(sno,cno,scgrade) values(3,4,100);
insert into sc(sno,cno,scgrade) values(3,5,52);
insert into sc(sno,cno,scgrade) values(4,3,82);
insert into sc(sno,cno,scgrade) values(4,4,99);
insert into sc(sno,cno,scgrade) values(4,5,40);
select * from s;
select * from c;
select * from sc;
第一题:找出没有选过’‘黎明’'老师的所有学生姓名
-- 1.找出黎明老师授课编号
select cno from c where cteacher ="黎明";
-- 2.找出选了黎明老师课程的学生
select sno from sc where cno = (select cno from c where cteacher ="黎明");
-- 3.找出没有选黎明来老师课程的学生
select
sname
from
s
where
sno not in (select sno from sc where cno = (select cno from c where cteacher ="黎明"));