mysql中嵌套查询分数大于70分的人数_MySQL查询练习题，自我解答版本

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT c.*,a.s_score,b.s_score

FROM score AS a,score AS b,student AS c

WHERE c.s_id=a.s_id AND a.s_id=b.s_id AND a.c_id='01'AND b.c_id='02'AND a.s_score>b.s_score;

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT c.*,a.s_score,b.s_score

FROM score AS a,score AS b,student AS c

WHERE c.s_id=a.s_id AND a.s_id=b.s_id AND a.c_id='01'AND b.c_id='02'AND a.s_score

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT a.*,AVG(b.s_score)AS total

FROM student AS a,score AS b

WHERE a.s_id=b.s_id

GROUP BY a.s_id

HAVING total>=60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT a.*,AVG(IFNULL(b.s_score,0))AS total

FROM student AS a

LEFT JOIN score AS b ON a.s_id=b.s_id

GROUP BY a.s_id

HAVING total<60;

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT a.s_id,a.s_name,COUNT(b.c_id),SUM(IFNULL(b.s_score,0))

FROM student AS a

LEFT JOIN score AS b ON a.s_id=b.s_id

GROUP BY a.s_id;

#6、查询"李"姓老师的数量

SELECT COUNT(1)

FROM teacher AS a

WHERE a.t_name LIKE'李%';

#7、查询学过"张三"老师授课的同学的信息

SELECT a.*

FROM student AS a WHERE a.s_idIN(

SELECT s.s_id FROM score AS s WHERE s.c_id=(

(SELECT c.c_idFROM course AS c WHERE c.t_id=(

SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))));

#8、查询没学过"张三"老师授课的同学的信息

SELECT a.*

FROM student AS a WHERE a.s_id NOT IN(

SELECT s.s_id FROM score AS s WHERE s.c_id=(

(SELECT c.c_idFROM course AS c WHERE c.t_id=(

SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))));

#9、查询学过编号为"01"并且也学过编号为"02"