mysql的多表联查练习

本次练习基于教师表,学生表,课程表,学生与课程的中间表这四张表(没有进行sql语句的优化)(亲测好用)

各表测试数据如下:
学生表

教师表

课程表

学生_课程中间表
、-- 1、查询课程1的成绩 比 课程2的成绩 高 的所有学生的学号；
select c.sno from (select a.sno sno,a.score ascore,b.score bscore from (select * from sc where cno =1) a,(select * from sc where cno =2) b where a.sno=b.sno) c
where c.ascore>c.bscore

---

– 2、查询平均成绩大于60分的同学的学号和平均成绩；
select sno,avg(score) from sc group by sno having avg(score)>60

---

– 3、查询所有同学的学号、姓名、选课数、总成绩；
select s.sno 学号,s.sname 姓名,z.x 选课数,z.z 总成绩 from student s,(select sno,count(*) x,sum(score) z from sc group by sno) z where z.sno=s.sno

---

– 4、查询姓“李”的老师的个数；
select count(*) from teacher where tname like ‘%李%’

---

– 5、查询没学过“叶平”老师课的同学的学号、姓名；
select s.sno 学号,s.sname 姓名 from student s,(select distinct(sno) sno from sc where cno in (select cno from course where tno !=(select tno from teacher where tname=‘叶平’))) ss where s.sno=ss.sno

---

– 6、查询同时学过课程1和课程2的同学的学号、姓名；
select sno,sname from student where sno in(select distinct(sno) from sc where cno in(1,2))

---

– 7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名；
select s.sno,sname from student s,sc sc where s.sno=sc.sno
and sc.cno in(select cno from course where tno =(select tno from teacher where tname=‘叶平’))

---

– 8、查询所有课程成绩小于60分的同学的学号、姓名；
select sno,sname from student where sno not in (select distinct(sno) from sc where score >60)
– 9、查询所有课程成绩大于60分的同学的学号、姓名；
select sno,sname from student where sno not in (select distinct(sno) from sc where score <60)

---

– 10、查询没有学全所有课的同学的学号、姓名；
select s.sno,s.sname from student s,(select count() c,sno from sc group by sno) m where s.sno =m.sno and m.c = (select count() from course)

---

– 11、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名；
select distinct(s.sno),s.sname from student s, sc sc where s.sno=sc.sno and sc.cno in (select cno from sc where sno = 1) and s.sno!=1

---

– 12、查询和2号同学学习的课程完全相同的其他同学学号和姓名；
select s.sno,s.sname from student s,(select sno from sc group by sno having (avg(cno) ,count(cno))=(select avg(cno) ,count(cno) from sc where sno=1 group by sno)) t where s.sno=t.sno and s.sno!=2

---

– 13、查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分；
select cno 课程号,max(score) 最高分,min(score) 最低分 from sc group by cno

---

– 14、查询课程号、课程名、各科平均成绩、及格率，按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select aa.cno,aa.cname,aa.p 平均值,bb.jgl 及格率 from
(select c.cno cno,c.cname cname,cc.pj p from course c,(select avg(score) pj ,cno from sc group by cno) cc where c.cno=cc.cno) aa,
(select jg.j/zs.z jgl,jg.cno cno from (select count() j,cno from sc where score>60 group by cno) jg,(select count() z,cno from sc group by cno) zs where jg.cno=zs.cno) bb
where aa.cno=bb.cno order by aa.p,bb.jgl desc

---

– 15、查询不同老师所教不同课程平均分, 从高到低显示
select t.tname,d.pj from teacher t,(select c.tno tno,cc.p pj from course c,(select avg(score) p,cno from sc group by cno) cc where c.cno= cc.cno) d where t.tno=d.tno order by d.pj desc