11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
insert into SC values('1001','001',80); //可不写,验证用
insert into student values('1001','张三丰',18,'男'); //可不写,验证用
i
select sid,sname from student
where sid in
(
select distinct sid from sc
where cid in
( select cid from sc where sid = '1001')
);
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
select sid,sname from student
where sid in
(
select distinct sid from sc
where cid in
( select cid from sc where sid = '001')
);
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update sc
set score = (select avg(sc2.score) from sc sc2 where sc2.cid = sc.cid)
where cid in
( select cid from course
where tid =
(
select tid from teacher
where tname ='叶平'
)
)
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;(我的理解是上课数也一模一样)
insert into SC values('1002','001',80);
insert into SC values('1003','001',80);
insert into student values('1002','王五',18,'女');
insert into student values('1003','李六',20,'男');
select sid,sname from student
where sid in
(
select sid from sc
where sid in
(
select sid from sc
group by sid
having count(*) =
(
select count(*) from sc
where sid ='1002'
)
)
and cid in (select cid from sc where sid ='1002')
group by sid
having count(*) = (select count(*) from sc where sid ='1002')
);
15、删除学习“叶平”老师课的SC表记录;
delete from sc
where cid =
(
select cid from course
where tid =
(
select tid from teacher
where tname = '叶平'
)
);