MySQL经典多表查询试题_mysql 多表查询练习题（二）答案

这篇文章是sql语句的答案篇，数据库表信息请查看上一篇 “mysql 多表查询练习题(一)数据准备”

1．查询平均成绩大于70分的同学的学号和平均成绩

select sid,avg(score) avg_sc from sc group by sid having avg_sc>70

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

select sc.sid,st.sname,count(*) c_num,sum(sc.score) c_sum from sc,student st

where sc.sid=st.sid group by sc.sid,st.sname

3．查询姓“李”的老师的个数

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

4.查询学过“李纯”老师课的同学的学号、姓名

select st.sid,st.sname from sc,student st where sc.sid=st.sid and sc.cid in (

select cid from course c,teacher t where c.tid=t.tid and tname = '李纯'

)

5.查询没学过“李雷”老师课的同学的学号、姓名

select * from student where sid not in (

select distinct sc.sid from sc where sc.cid in (

select cid from course c,teacher t where c.tid=t.tid and tname = '李雷'

)

)

6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select sid,sname from student where sid in(

select t1.sid from

(select * from sc where cid='001') t1,

(select * from sc where cid='002') t2

where t1.sid = t2.sid

)

7.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

select sid,sname from student where sid in(

select t1.sid from

(select * from sc where cid='001') t1,

(select * from sc where cid='002') t2

where t1.sid = t2.sid and t2.score

8.查询学过“李雷”老师所教的所有课的同学的学号、姓名

select s.sid,s.sname from student s,(

select sc.sid,count(sc.sid) from sc where sc.cid in(

select c.cid from course c,teacher t where c.tid=t.tid and tname='李雷'

)

group by sc.sid having count(sc.sid)=(

select count(*) from course c,teacher t where c.tid=t.tid and tname='李雷'

)

) t

where s.sid = t.sid

9.查询各科成绩最高和最低的分：以如下形式显示：课程ID，课程名最高分，最低分

select cid,max(score),min(score) from sc group by cid

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

select s.sid,s.sname from student s,

(

select sid,count(*) from sc group by sid

having count(*)=(select count(*) from course)

) t

where s.sid=t.sid

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

select sid,sname from student where sid in(

select sid from sc where cid in(

select cid from sc where sid = '1001'

) and sid!='1001'

)

12.按各科平均成绩从低到高和及格率的百分数从高到低顺序

补充：concat() 方法用于连接两个或多个数组

百分数表示方法 CONCAT(值1/值2*100,'%')

select s1.cid,avg(s1.score) avg_sc,concat(t.c2/count(s1.score)*100,'%') rate

from sc s1 left join

(select cid cid2,count(*) c2 from sc where score>=60 group by cid) t

on s1.cid = t.cid2

group by s1.cid order by avg_sc,rate desc

13.删除学习“李纯”老师课的SC表记录

delete from sc where cid in(

select cid from teacher t,course c where t.tid=c.tid and t.tname = '李纯'

)