随笔十六 ：5大SQL练习题
# 1、查询所有课程的名称以及对应的任课老师的名字第一步，需要连表
select * from course inner join teacher on course.teacher_id=teacher.id;
第二步，需要查询什么条件
select course.cname,teacher.tname from course inner join teacher on course.teacher_id=teacher.id;
# 2、查询平均成绩大于80分的同学姓名和平均成绩
连表:
第一步
select * from student inner join score on student.sid=score.student_id
第二步，分组获取平均成绩进行过滤
select student.sid,avg(num)as avg_num from student inner join score on student.sid=score.student_id group by student.sid having avg(num)>80
第三步，拿到平均成绩在和学生表做内连接
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN （ SELECT
student.sid,
avg( num ) AS avg_num
FROM
student
INNER JOIN score ON student.sid = score.student_id
GROUP BY
student.sid
HAVING
avg( num ) > 80
) AS t1 ON student.sid = t1.student_id
子查询：
第一步先通过成绩表获取大于85分的同学id和平均成绩
select student_id,avg(num) from score group by student_id having avg(num)>80;
第二步通过内连接连表
select student.sname,avg(num) from student inner join
(select student_id,avg(num) from score group by student_id having avg(num)>85) as sco
on student.sid=sco.student_id
# 3、查询没有报李平老师课程的学生姓名
分步操作:
方法一:
1、查询出李平老师的课程id
select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname=('李平')
2、通过课程id查询学生id
select distinct student_id score where course_id in (select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平'))
3、然后连表对于学生id取反
SELECT
student.sname
FROM
student
INNER JOIN score ON student.sid = score.sutdent_id
WHERE
score.student_id in
(select distinct student_id score where course_id in (select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平')))
方法二:
1、course和teacher连表，过滤出没有没有李平老师的cid
select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平')
2、student和score连表，条件等与上表的course.cid
SELECT
student.sname
FROM
student
INNER JOIN score ON student.sid = score.sutdent_id
WHERE
course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.id WHERE tname NOT IN ( '李平' ) )
# 4、查询没有同时选修物理课程和体育课程的学生姓名，没有选的也不要
老师思路:
1、先查询出物理和体育的课程id
select cid from course where cname in（'物理'，'体育'）
2、通过成绩表进行过滤课程id当条件，在进行分组过滤，拿到count为1的学生id
select sid from score where cid in (select cid from course where cname in（'物理'，'体育'）) group by sid having count(*)=1
3、通过学生表通过上面的学生id获取学生信息
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
sid
FROM
score
WHERE
cid IN ( SELECT cid FROM course WHERE cname IN（ '物理'，'体育'） GROUP BY sid HAVING count( * ) = 1 )
)
自己思路：
1、课程与成绩连表，选出学生id
select score.sid from score inner join coures on score.course_id = course.id where course.cname in（'物理'，'体育'） group by score.sid having count(*)=1;
2、学生表直接用sid当条件
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
score.sid
FROM
score
INNER JOIN coures ON score.course_id = course.id
where
course.cname in（'物理'，'体育'）
GROUP BY
score.sid
HAVING
count( * ) = 1
)
# 5、查询挂科成绩超过2门（包括2门）的学生成绩和班级(学生姓名)
1、在成绩表中对于学生分组，通过where score<60,having>=2,拿到学生的sid，score
select sid,score from score where score<60 group by sid having count(*)>=2
2、与学生表进行连表取出score,class_id,再去和班级表连表
select student.class_id,t1.score from student inner join (select sid,score from score group by sid having count(*)>=2) t1
on student.id=t1.sid
3、在和班级连表
SELECT
t2.score,
calss.captian
FROM
class
INNER JOIN (
select student.class_id,t1.score from student inner join (select sid,score from score group by sid having count(*)>=2) t1
on student.id=t1.sid
) t2 ON class.id = t2.class_id
页:
[1]