mysql复杂查询练习题_mysql复杂查询,优质题目

表结构

1.自行添加测试数据

2.查询平均成绩大于60分的同学的学号和平均成绩；

SELECT t1.sid,AVG(t2.number)

FROM student t1 LEFT JOIN score t2 ON t1.sid=t2.student_id

GROUP BY t1.sid HAVING AVG(t2.number)>60;

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

SELECT t1.sid,t1.sname,t2.temp1,t3.temp2

FROM student t1,(SELECT student_id,COUNT(sid) temp1 FROM score GROUP BY student_id) t2,

(SELECT student_id,SUM(number) temp2 FROM score GROUP BY student_id) t3

WHERE t1.sid=t2.student_id AND t1.sid=t3.student_id;

#较复杂，有难度

4.查询姓“李”的老师的个数；

SELECT COUNT(tid)

FROM teacher

WHERE tname LIKE '李%';

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

SELECT t1.`sid`,t1.`sname`</