MySQL练习题部分答案(未完待续)

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select
A.student_id
from
(select score.sid,score.student_id,course.cname,score.number from score left join course on score.corse_id=course.cid where course.cname="生物") as A
INNER JOIN
(select score.sid,score.student_id,course.cname,score.number from score left join course on score.corse_id=course.cid where course.cname="生物") as B
on A.student_id = B.student_id
where A.number>B.number
-- 3、查询平均成绩大于60分的同学的学号和平均成绩；
select B.student_id,student.sname,B.ccc from (select student_id,avg(number) as ccc from score group by student_id having avg(number) >60) as B
left join student on B.student_id=student.sid;
-- 4、查询所有同学的学号、姓名、选课数、总成绩；
select score.student_id,student.sname,count(1),sum(number) from score
left join student on score.student_id=student.sid GROUP BY score.student_id;

5、查询姓“李”的老师的个数；
select * from teacher where sname like '李%'
6、查询没学过“苍空”老师课的同学的学号、姓名；
方法1：
select * from student where
sid not in (
select student_id from score where corse_id in (
select
course.cid
from
course
left join teacher on course.teacher_id=teacher.tid
where
teacher.tname="苍空")
group by student.id)
方法2：
select
student.sid,
student.sname
from
(select
score.student_id as bid
from
score
where
corse_id not in (
select
course.cid
from
course
left join teacher on course.teacher_id=teacher.tid
where
teacher.tname = "苍空"
)
) as B
left join student on student.sid=B.bid
group by student.sid
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

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

10、查询有课程成绩小于60分的同学的学号、姓名；