mysql练习题

1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT * FROM
(SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname=“生物”) AS A
INNER JOIN
(SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname=“物理”) AS B
on A.student_id = B.student_id WHERE A.num > B.num;

3、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT B.student_id,student.sname,B.s_num FROM (SELECT student_id,avg(num) AS s_num from score GROUP BY student_id HAVING s_num>60) as B
LEFT JOIN student on B.student_id = student.sid; 取前三： %ORDER BY s_num desc LIMIT 3;

4、查询所有同学的学号、姓名、选课数、总成绩；
SELECT score.student_id,student.sname,COUNT(score.course_id) AS courses,SUM(num) as T_Score FROM score
LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id;

5、查询姓“李”的老师的个数；
SELECT * from teacher WHERE tname like “李%”;

6、查询没学过“李平”老师课的同学的学号、姓名；
SELECT student.sid,student.sname FROM student WHERE student.sid NOT IN (
SELECT score.student_id FROM score LEFT JOIN course ON score.course_id = course.cid WHERE score.course_id IN (
SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname LIKE “李平%”) GROUP BY score.student_id)

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT B.sid,B.sname FROM(
SELECTstudent.sid,score.course_id,student.sname FROM score
LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id BETWEEN 1 AND 2) AS B
GROUP BY B.sid HAVING COUNT(B.course_id)=2;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT student.sid,student.sname FROM score LEFT JOIN student ON score.student_id=student.sid WHERE score.course_id in
(SELECT course.cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname LIKE “李平%”)
GROUP BY student.sid HAVING COUNT(student_id)=2;

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT A.sid,A.sname,A.num as score_2,B.num as score_1 FROM
(SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=2) AS A
INNER JOIN
(SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=1) A