子查询（经典题型）

柔光的暖阳◎ 2021-09-16 04:36 351阅读 0赞

【一】相关介绍

**学生表  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='叶平'));