SQL50题面试题(10-15)

数据表创建和数据传送

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 = '叶平'
		)
);