– 1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
select st.*,one.s_score,two.s_score from student st
INNER JOIN (select s_id,s_score from score where c_id='01') as one
on st.s_id=one.s_id
INNER JOIN (select s_id,s_score from score where c_id='02') as two
on one.s_id=two.s_id
where one.s_score>two.s_score
– 2、查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
select AVG(s_score),s_id from score
GROUP BY s_id
HAVING AVG(s_score)>60
– 查询平均成绩小于60分的学生的学号和平均成绩,需要加上null
SELECT s_id, AVG(s_score) from score
GROUP BY s_id
HAVING AVG(s_score)<60 or AVG(s_score) is NULL
– 3、查询所有学生的学号、姓名、选课数、总成绩(不重要)
select st.s_id,s_name,co.c_name,SUM(sc.s_score)
from student st
INNER JOIN score sc
on st.s_id=sc.s_id
INNER JOIN course co
on sc.c_id=co.c_id
GROUP BY st.s_id
– 4、查询姓“张”的老师的个数(不重要)
select COUNT(*)
from teacher
where t_name like '张%'
– 5、查询没学过“张三”老师课的学生的学号、姓名(重点)
– 注意:使用in 而不是!
select * from student
where s_id not in
(select sc.s_id from score sc
INNER JOIN course co
on sc.c_id=co.c_id
INNER JOIN teacher te
on co.t_id=te.t_id
WHERE te.t_name='张三')
– 6、查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
– 注意:使用in 而不是!
SELECT * from student
WHERE s_id in
(select sc.s_id from score sc
INNER JOIN course co
on sc.c_id=co.c_id
INNER JOIN teacher te
on co.t_id=te.t_id
where te.t_name='张三')
– 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
– 先查出学过01的再查出学过02的,然后通过学生编号一个一个去比较
select * from student
where s_id in
(select one.s_id from (select s_id,c_id from score where c_id='01') as one
INNER JOIN (SELECT s_id,c_id from score where c_id='02') as two
on one.s_id=two.s_id)
– 8、查询课程编号为“02”的总成绩(不重点)
select SUM(s_score) 总成绩
from score where c_id='02'
– 9、查询所有课程成绩小于60分的学生的学号、姓名
select st.s_id,s_name,sc.c_id
from student st
INNER JOIN score sc
on st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING SUM(sc.s_score)<60
– 10.查询没有学全所有课的学生的学号、姓名(重点)
– 当课程数量小于总课程数量就是没学全
select st.s_id,