Mysql--子查询练习

MySQL子查询练习题目

（这一块很差的）

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

(select sid from sc group by sid having max(score)<=60) a 

用表连接
select * from student b 
    inner join 
    (select sid from sc group by sid having max(score)<=60) a 
    on b.sid=a.sid;

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

用取反的做法
select sid from sc where score > 60;
select * from student 
    where sid 
    not in(select sid from sc where score > 60);

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

select count(*) from course; /*求得总课程数*/

/*求得没有学全总课程数的sid*/
(select sid from sc group by sid having count(*)<(select count(*) from course)) a 

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

3、查询每门课程选修人数，格式为课程名称，人数；

先统计个数，再连接课程表
(select cid, count(*) from sc group by cid) a

select totalcount,cname from 
    (select cid, count(*) totalcount from sc group by cid) a
    inner join course b on a.cid=b.cid;

先连接课程表，再统计个数
select a.cid,cname,count(*) 
    from sc a 
    inner join course b 
    on a.cid=b.cid group by a.cid, cname;

4、查询全部学生都选修的课程的课程号和课程名 ；

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

5、查询两门以上不及格课程的同学的学号及其平均成绩 ；

select sid from sc 
    where score<60 group by sid having count(*)>=2;

select sid,avg(score) from sc where sid in 
    (select sid from sc where score<60 group by sid having count(*)>=2) 
    group by sid;

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

(select sid,cid,score from sc where cid=1) a
(select sid,cid,score from sc where cid=2) b

(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

select * 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;

7. 查询学过1号课程并且也学过编号2号课程的同学的学号、姓名

select * 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)c 
    inner join student d where c.sid=d.sid; 

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

-- 找到的是叶平教过的学生的sid
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='叶平';

-- 再利用not in 排除这些sid
select * 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='叶平');

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

select stu.sid,stu.sname,
    (select count(cid) from scores sc where sc.sid=stu.sid) '选课数',
    (select sum(score) from scores sc where sc.sid=stu.sid) '总成绩'
    from student stu;

select stu.sname,a.* from (select sid,count(cid),sum(score) from scores group by sid) a
    inner join student stu on a.sid=stu.sid;

10. 查询所有学生平均成绩大于80的所有学生学号、姓名和平均成绩

select stu.sid,stu.sname,
    (select avg(score) from scores sc where stu.sid=sc.sid group by stu.sid) v
    from student stu
    having v > 80;

select a.*,stu.sname from 
    (select sid,avg(score) '平均成绩' from scores group by sid having (avg(score) > 80)) a 
    inner join student stu on a.sid=stu.sid;

11. 查询课程相同且成绩相同的学生学号、课程号与学生成绩

select b.sid,b.cid,b.score
    from (select cid,score from scores sc group by cid,score having (count(*) > 1)) a
    inner join scores b on (a.cid=b.cid and a.score=b.score);

12. 查询每个部门最高工资员工的所有信息

select b.* from (select deptno,max(sal) msal from emp group by deptno) a 
    inner join emp b on a.deptno=b.deptno and a.msal=b.sal;

select * from emp e 
    where sal = (select max(sal) from emp where e.deptno=deptno group by deptno);