子查询（经典题型）

【一】相关介绍 

学生表  student（sid,sname,birthday,sex）

老师表   teacher(tid,tname)

课程表   course（cid,cname,tid）

成绩表   sc(sid,cid,score)

---

【二】题目

1、查询2号课程成绩比1号课程成绩低的学生的学号、姓名

方式一：

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

方式二：

select c.sid,d.sname from (select a.sid from 
(select sid,cid,score from sc where cid=1)a inner join
(select sid,cid,score from sc where cid=2)b on a.sid=b.sid 
and a.score>b.score)c inner join student d where c.sid=d.sid;

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

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

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

方式一：

select a.sid 学号,a.sname 姓名,b.c 选课数,b.d 总成绩 from student a inner join
(select sid,count(*) c,sum(score) d from sc group by sid) b on a.sid=b.sid;

方式二：

 select Student.sid,Student.Sname,count(SC.sid),sum(score)
  from Student left Outer join SC on Student.sid=SC.sid
  group by Student.sid;

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

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

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

方式一：

select sid,sname from student 
where sid not in(select a.sid from sc a
inner join (select cid from course where tid=(select tid from teacher where tname='叶平')) b
on a.cid=b.cid);

 方式二：

select sid,sname from student where sid not in (select sid from 
teacher a inner join course b on a.tid=b.tid inner join sc c on b.cid=c.cid where a.tname='叶平');

 方式三：

select Student.sid,Student.Sname from Student 
where sid not in (select distinct( SC.sid) from SC,Course,Teacher 
where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平');

方式四：

select sid,sname from student where sid not in(
select sid from sc where cid in(
select cid from course where tid in(
select tid from teacher where tname='叶平')));

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

方式一：

select a.sid,a.sname from student a
inner join (select sid from sc where cid=1) b on a.sid=b.sid
inner join (select sid from sc where cid=2) c on b.sid=c.sid;

方式二：

select d.sid,d.sname from 
(select a.sid from 
(select sid from sc where cid=1)a inner join (select sid from sc where cid=2)b 
on a.sid=b.sid)c 
inner join student d where c.sid=d.sid;

方式三：

select student.sid,student.sname from student,sc 
where student.sid=sc.sid and sc.cid='001'and 
exists( select * from sc as sc_2 where sc_2.sid=sc.sid and sc_2.cid='002');

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

方式一：

select sid,sname from student 
where sid in(select a.sid from sc a
inner join (select cid from course where tid=(select tid from teacher where tname='叶平')) b
on a.cid=b.cid);

方式二：

select sid,sname from student where sid in (select sid from 
teacher a inner join course b on a.tid=b.tid inner join sc c on b.cid=c.cid where a.tname='叶平');

方式三：

select Student.sid,Student.Sname from Student 
where sid  in (select distinct( SC.sid) from SC,Course,Teacher 
where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平');

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

方式一：

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

方式二：

select d.sid,d.sname from 
(select a.sid from (select sid,cid,score from sc where cid=1)a 
inner join (select sid,cid,score from sc where cid=2)b 
on a.sid=b.sid and a.score>b.score)c 
inner join student d where c.sid=d.sid;

方式三：

  Select Sid,Sname from (select Student.Sid,Student.Sname,score ,
(select score from SC SC_2 where SC_2.Sid=Student.Sid and SC_2.Cid='002') score2
  from Student,SC where Student.Sid=SC.Sid and Cid='001') S_2 where score2 <score;

9、查询所有课程成绩小于等于60分的同学的学号、姓名；

方式一：

select student.sid,student.sname from student 
inner join (select sid,max(score) from sc group by sid having max(score)<=60) a
 on student.sid=a.sid;

方式二： 

select sid,sname from student
 where sid in (select sid from sc group by sid having max(score)<=60);

方式三： 

select sid,sname from student where sid not in(select sid from sc where score > 60);

10、查询没有学全所有课的同学的学号、姓名； 

方式一：

select a.sid,a.sname from student a 
inner join (select sid,count(*) from sc group by sid 
having count(cid)<(select count(cid) from course)) b on a.sid=b.sid;

方式二：

select Student.sid,Student.Sname from Student,SC 
where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid)
<(select count(cid) from Course);

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

select distinct a.sid,a.sname from student a 
inner join sc where a.sid=sc.sid and sc.sid!=1001 and sc.cid 
in (select cid from sc where sid='1001');

12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名；

select distinct SC.sid,Sname 
    from Student,SC 
    where Student.Sid=SC.Sid and Cid in (select Cid from SC where Sid='001'); 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

update SC set score=(select avg(SC_2.score) 
    from SC SC_2 
    where SC_2.Cid=SC.Cid) from Course,Teacher where Course.Cid=SC.Cid and Course.Tid=Teacher.Tid and Teacher.Tname='叶平');

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

select a.sid,a.sname from student a 
inner join (select sid from sc where cid in (select cid from sc where sid=1002) 
group by sid having count(*)=(select count(*) from sc where sid=1002))b 
on a.sid=b.sid order by sid;

15、删除学习“叶平”老师课的SC表记录； 

delete from sc where cid in (select cid from course where tid in(select tid from teacher where tname='叶平'));