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 = '叶平'
		)
);
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值