数据分析 | MySQL45道练习题（28~36）

【题目总览】

28.查询所有学生的课程及分数情况（存在学生没成绩没选课的情况）

29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

30.查询不及格的课程 

31.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 

32.求每门课程的学生人数 

33.假设成绩不重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 

34.假设成绩有重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

36.查询每门科目成绩最好的前两名 

28.查询所有学生的课程及分数情况（存在学生没成绩没选课的情况）
-- 【分析】给学生信息加上课程情况

# 法一
select a.sid,a.sname,c.cname,b.score
from student a
left join sc b
on a.sid = b.sid 
left join course c
on b.cid = c.cid;

# 法二
-- 1.先查询课程情况 --
SELECT sc.sid,sc.cid,c.cname
FROM course c
LEFT JOIN sc
ON sc.cid = c.cid;
-- 2.再将以上结果与学生表student左关联 --
SELECT s.*,a.cid,a.cname,a.score
FROM student s
LEFT JOIN (SELECT sc.sid,sc.cid,c.cname,sc.score
FROM course c
LEFT JOIN sc
ON sc.cid = c.cid) a
ON s.sid = a.sid;

29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- 【分析】条件查询结果

# 法一
-- 1、先找出成绩大于70分的成绩记录，这里面的sid就是要找的学生 
select sid
from sc
where score > 70;
-- 2、和学生表进行关联得到学生信息记录 
select b.sname,c.cname,a.score
from sc a
left join student b
on a.sid = b.sid
left join course c
on a.cid = c.cid
where a.score > 70;

# 法二
-- 1.先查询成绩在70分以上的sid、cid、cname和score --
SELECT sc.sid,sc.cid,c.cname,sc.score
FROM sc
LEFT JOIN course c
ON sc.cid = c.cid
WHERE score > 70;

-- 2.与课程表表student关联 --
SELECT s.sname,a.cname,a.score
FROM (SELECT sc.sid,sc.cid,c.cname,sc.score
	   FROM sc
	   LEFT JOIN course c
	   ON sc.cid = c.cid
	   WHERE score > 70) a
LEFT JOIN student s
ON a.sid = s.sid;

30.查询不及格的课程 
-- 【分析】条件查询 

-- 1.先查询成绩小于60的cid*(去重) --
SELECT DISTINCT(cid)
FROM sc
WHERE score < 60;

-- 2.将以上结果与课程表course关联 --
SELECT DISTINCT(sc.cid),c.cname
FROM sc
LEFT JOIN course c
ON sc.cid = c.cid
WHERE sc.score < 60;

31.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 

select a.sid,a.sname,b.cid,b.score
from student a
inner join sc b
on a.sid = b.sid
where b.cid = '01' and b.score > 80;


SELECT s.sid,s.sname,sc.cid,sc.score
FROM sc 
LEFT JOIN student s
ON sc.sid = s.sid
WHERE sc.cid = "01" AND sc.score > 80;

32.求每门课程的学生人数 

SELECT sc.cid 课程编号,c.cname 课程名称,COUNT(sc.sid) 学生人数
FROM sc
LEFT JOIN course c 
ON sc.cid=c.cid
GROUP BY sc.cid;

33.假设成绩不重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 
-- 【分析】条件筛选查询，只需要一个人

# 法一
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
LEFT JOIN sc
ON c.cid = sc.cid
LEFT JOIN student s
ON s.sid = sc.sid
WHERE t.tname = "张三"
ORDER BY sc.score DESC
LIMIT 1;

# 法二
-- 1.先查询[张三]老师所带的课程编号cid和tid --
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
WHERE t.tname = "张三";
-- 2.将以上结果与成绩表sc关联 --
SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
FROM (SELECT t.tid,t.tname,c.cid,c.cname
	  FROM teacher t
	  LEFT JOIN course c
	  ON t.tid = c.tid
	  WHERE t.tname = "张三") a 
LEFT JOIN sc
ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1;
-- 3.将以上结果与学生表student关联 --
SELECT s.sid 学号,s.sname 学生姓名,a.cname 课程,a.tname 教师姓名,a.score 成绩
FROM (SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
	  FROM (SELECT t.tid,t.tname,c.cid,c.cname
			FROM teacher t
			LEFT JOIN course c
			ON t.tid = c.tid
			WHERE t.tname = "张三") a 
		LEFT JOIN sc
		ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1) a
LEFT JOIN student s
ON a.sid = s.sid;

34.假设成绩有重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 

SELECT *
FROM 
(SELECT a.*,
		CASE WHEN @score=score THEN @rank
			 WHEN @score:=score THEN @rank:=@rank+1 END rk
FROM 
(SELECT s.sid,s.sname,sc.score,c.cid,c.cname
 FROM teacher t
 LEFT JOIN course c
 ON t.tid = c.tid
 LEFT JOIN sc
 ON c.cid = sc.cid
 LEFT JOIN student s
 ON s.sid = sc.sid
 WHERE t.tname = "张三") a,(SELECT @score:=NULL,@rank:=0) t) s
WHERE rk=1;

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
-- 【分析】条件关联筛选 

SELECT a.sid,a.cid,a.score
FROM sc a
INNER JOIN sc b
ON a.sid = b.sid
WHERE a.cid != b.cid AND a.score = b.score
GROUP BY a.cid,a.sid;

36.查询每门科目成绩最好的前两名 
-- 【分析】用户变量排序 

SELECT sid,cid,score,rk
FROM(
	SELECT sc.*,
		   @rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) rk,
		   @sco:=score,
		   @c_cid:=cid
		FROM sc,(SELECT @sco=null,@rank:=0,@c_cid:=null) b
ORDER BY cid,score DESC) a
where a.rk<3;