sql面试题 查询学过“叶平”老师所教的所有课的同学的学号

转自:https://blog.csdn.net/u010689306/article/details/51494386

Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表 

参考数据:

1、查询“1”课程比“2”课程成绩高的所有学生的学号;

select a.sid
from (select sid,score from sc where cid=1) a join (select sid,score from sc where cid=2) b on a.sid = b.sid
where a.score > b.score;

2  查询平均成绩大于60分的同学的学号和平均成绩;

select sid,avg(score)
from sc group by sid having avg(score)>60;

3  查询所有同学的学号、姓名、选课数、总成绩

SELECT stu.sid,stu.sname,count(cid),sum(score)
FROM student stu JOIN sc s ON stu.sid = s.sid
group by stu.sid

4 查询姓“李”的老师的个数;

select count(tid)from teacher where tname like '李%'

5 查询没学过“叶平”老师课的同学的学号、姓名;

SELECT sid,sname
FROM student
WHERE sid NOT IN (
		SELECT DISTINCT sid
		FROM sc
		WHERE cid IN (
				SELECT cid
				FROM course
				WHERE tid = (
						SELECT tid
						FROM teacher
						WHERE tname = "叶平"
				)
		)
);

6 查询学过编号“1”并且也学过编号“2”课程的同学的学号、姓名;

SELECT sid,sname
FROM student
WHERE sid IN (
		SELECT a.sid
		FROM (SELECT sid FROM sc WHERE cid = 1) AS a
		WHERE a.sid IN (SELECT sid FROM sc WHERE cid = 2)
)
-- 这道题如果你抽风容易写成 where sid=1 and sid=2 大错特错!!!

7 查询学过“叶平”老师所教的所有课的同学的学号、姓名;

select sid
-- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)
from (
	-- 学过该老师课程的同学学号(包含只学一门)
	select sid,count(cid) num
	from sc
	where cid in (
		-- 该老师教了哪些课程
		select cid
		from course
		where tid in (
			select tid
			from teacher
			where tname = '叶平'
		)
	)group by sid
) t
where t.num = (
	-- 统计该老师总课程数
	select count(cid)
	from course
	where tid = (
		select tid
		from teacher
		where tname = '叶平'
	)
)

8 查询所有课程成绩小于60分的同学的学号、姓名;

select sid,sname
from student
where sid in(
	select distinct sid
	from sc
	where score<60
)

9 查询没有学全所有课的同学的学号、姓名;

select sid,sname
from student
where sid in(
	select sid
	from sc
	group by sid having  count(cid) < (
		select count(cid)
		from course
    )
)

10 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

select sid,sname
from student
where sid in(
	select distinct sid
	from sc
	where cid in(
		select cid
		from sc
		where sid='1001'
	) and sid != '1001'
)

11 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

select cid,max(score) max,min(score) min
from sc group by cid

 

  • 37
    点赞
  • 153
    收藏
    觉得还不错? 一键收藏
  • 8
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值