转自：/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.sidfrom (select sid,score from sc where cid=1) a join (select sid,score from sc where cid=2) b on a.sid = b.sidwhere 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.sidgroup by stu.sid
4 查询姓“李”的老师的个数；
select count(tid)from teacher where tname like '李%'
5 查询没学过“叶平”老师课的同学的学号、姓名；
SELECT sid,snameFROM studentWHERE sid NOT IN (SELECT DISTINCT sidFROM scWHERE cid IN (SELECT cidFROM courseWHERE tid = (SELECT tidFROM teacherWHERE tname = "叶平")));
6 查询学过编号“1”并且也学过编号“2”课程的同学的学号、姓名；
SELECT sid,snameFROM studentWHERE sid IN (SELECT a.sidFROM (SELECT sid FROM sc WHERE cid = 1) AS aWHERE 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) numfrom scwhere cid in (-- 该老师教了哪些课程select cidfrom coursewhere tid in (select tidfrom teacherwhere tname = '叶平'))group by sid) twhere t.num = (-- 统计该老师总课程数select count(cid)from coursewhere tid = (select tidfrom teacherwhere tname = '叶平'))
8 查询所有课程成绩小于60分的同学的学号、姓名；
select sid,snamefrom studentwhere sid in(select distinct sidfrom scwhere score<60)
9 查询没有学全所有课的同学的学号、姓名；
select sid,snamefrom studentwhere sid in(select sidfrom scgroup by sid having count(cid) < (select count(cid)from course))
10 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select sid,snamefrom studentwhere sid in(select distinct sidfrom scwhere cid in(select cidfrom scwhere sid='1001') and sid != '1001')
11查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid,max(score) max,min(score) minfrom sc group by cid
如果觉得《sql面试题 查询学过“叶平”老师所教的所有课的同学的学号》对你有帮助，请点赞、收藏，并留下你的观点哦！
