[数据库笔记] SQL50题11 - 30

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

（1）方法1

-- 查询学号为01的学生
SELECT c_id FROM score
WHERE s_id="01";

-- 查询选了相同课程的学生学号
SELECT DISTINCT s_id FROM score
WHERE c_id IN (
	SELECT c_id FROM score
	WHERE s_id="01"
) AND s_id != "01";

-- 查询学号和姓名
SELECT s_id, s_name FROM student
WHERE s_id IN (
	SELECT DISTINCT s_id FROM score
	WHERE c_id IN (
		SELECT c_id FROM score
		WHERE s_id="01"
	) AND s_id != "01"
);

（2）方法2

SELECT a.s_id, a.s_name FROM student AS a
INNER JOIN (
	SELECT DISTINCT s_id FROM score
	WHERE c_id IN (
		SELECT c_id FROM score
		WHERE s_id="01"
	) AND s_id != "01"
) AS b ON a.s_id=b.s_id;

12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

-- 01号同学学了01,02,03
-- 选出所学的课不在(01,02,03)的同学 - 排除
-- 剩下的同学肯定选了01,02,03中的某几门，判断所学的课程数是否等于3

SELECT * FROM student
WHERE s_id IN (
	SELECT s_id FROM score
	WHERE s_id != "01"
	GROUP BY s_id HAVING COUNT(DISTINCT c_id) = 
	(SELECT COUNT(DISTINCT c_id) FROM score WHERE s_id="01")
)
AND s_id NOT IN (
	SELECT DISTINCT s_id FROM score
	WHERE c_id NOT IN (
	SELECT c_id FROM score
	WHERE s_id="01"
	)
);

13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

SELECT a.s_id, a.s_name, AVG(s_score) FROM student AS a
INNER JOIN score AS b
ON a.s_id=b.s_id
WHERE a.s_id IN (
	SELECT s_id FROM score
	WHERE s_score < 60
	GROUP BY s_id HAVING COUNT(DISTINCT c_id) >= 2
)
GROUP BY s_id, s_name;

16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）

SELECT t.*, s.c_id, s.s_score FROM student AS t
INNER JOIN score AS s ON t.s_id=s.s_id
WHERE s.c_id="01" AND s.s_score < 60
ORDER BY s.s_score DESC;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

SELECT s.s_id, s.c_id, s.s_score, b.avg_s_score 
FROM score AS s
INNER JOIN
(
	SELECT s_id, AVG(s_score) AS avg_s_score FROM score
	GROUP BY s_id
) AS b ON s.s_id=b.s_id
ORDER BY b.avg_s_score DESC;

另一种展示方式

SELECT
s_id "学号",
MAX(CASE WHEN c_id="01" THEN s_score ELSE NULL END) "语文",
MAX(CASE WHEN c_id="02" THEN s_score ELSE NULL END) "数学",
MAX(CASE WHEN c_id="03" THEN s_score ELSE NULL END) "英语",
AVG(s_score) "平均成绩"
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC;

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

SELECT s.c_id,
c.c_name,
MAX(s.s_score),
MIN(s.s_score),
AVG(s.s_score),
SUM(CASE WHEN s.s_score>=60 THEN 1 ELSE 0 END) / COUNT(s_id) "及格",
SUM(CASE WHEN s.s_score>=70 AND s.s_score<80 THEN 1 ELSE 0 END) / COUNT(s_id) "中等",
SUM(CASE WHEN s.s_score>=80 AND s.s_score<90 THEN 1 ELSE 0 END) / COUNT(s_id) "优良",
SUM(CASE WHEN s.s_score>=90 THEN 1 ELSE 0 END) / COUNT(s_id) "优秀"
FROM score AS s
INNER JOIN course AS c ON s.c_id=c.c_id
GROUP BY c_id;

19、按各科成绩进行排序，并显示排名(重点row_number)， row_number(）over （order by 列）

20、查询学生的总成绩并进行排名（不重点）

SELECT s_id "学号", SUM(s_score) "总成绩"
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC;

21、查询不同老师所教不同课程平均分从高到低显示(不重点)

（1）以课程为主体，求平均分

SELECT c.c_id, c.c_name, AVG(sc.s_score) AS  avg_score
FROM score AS sc
INNER JOIN course AS c ON sc.c_id = c.c_id
GROUP BY sc.c_id
ORDER BY avg_score DESC;

（2）以老师为主体，求平均分

SELECT t.t_id, t.t_name, AVG(sc.s_score) AS avg_score
FROM score AS sc
INNER JOIN course AS c ON sc.c_id=c.c_id
INNER JOIN teacher AS t ON c.t_id=t.t_id
GROUP BY t.t_id, t.t_name
ORDER BY avg_score DESC;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）

SELECT * 
FROM (SELECT st.s_id, st.s_name, st.s_birth, st.s_sex, c_id, s_score,
row_number() over (PARTITION BY c_id ORDER BY s_score DESC) m
FROM score sc INNER JOIN student st ON sc.s_id=st.s_id) a
WHERE m IN (2,3);

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

SELECT c.c_id, c.c_name,
SUM(CASE WHEN sc.s_score<=100 AND sc.s_score>=85 THEN 1 ELSE 0 END) AS "[100,85]",
SUM(CASE WHEN sc.s_score<85 AND sc.s_score>=70 THEN 1 ELSE 0 END) AS "(85,70]",
SUM(CASE WHEN sc.s_score<70 AND sc.s_score>=60 THEN 1 ELSE 0 END) AS "(70,60]",
SUM(CASE WHEN sc.s_score<60 THEN 1 ELSE 0 END) AS "[<60)"
FROM score AS sc
INNER JOIN course AS c ON sc.c_id=c.c_id
GROUP BY c.c_id, c.c_name;

24、查询学生平均成绩及其名次（同19题，重点）

 SELECT s_id, AVG(s_score), row_number() over (ORDER BY AVG(s_score) DESC)
 FROM score
 GROUP BY s_id;

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）

26、查询每门课程被选修的学生数(不重点)

SELECT c.c_id, c.c_name, COUNT(DISTINCT sc.s_id) FROM score AS sc
INNER JOIN course AS c ON sc.c_id=c.c_id
GROUP BY c.c_id, c.c_name;

27、查询出只有两门课程的全部学生的学号和姓名(不重点)

（1）方法1

SELECT s_id, s_name FROM student
WHERE s_id IN (
SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id) = 2
);

（2）方法2

SELECT sc.s_id, st.s_name FROM score AS sc
INNER JOIN student AS st ON sc.s_id=st.s_id
GROUP BY sc.s_id HAVING COUNT(DISTINCT sc.c_id)=2;

28、查询男生、女生人数(不重点)

（1）方法1

SELECT s_sex, COUNT(s_id)
FROM student
GROUP BY s_sex;

（2）方法2

SELECT 
SUM(CASE WHEN s_sex="男" THEN 1 ELSE 0 END) "男生人数",
SUM(CASE WHEN s_sex="女" THEN 1 ELSE 0 END) "女生人数"
FROM student;

（3）方法3

SELECT 
COUNT(CASE WHEN s_sex="男" THEN 1 ELSE NULL END) "男生人数",
COUNT(CASE WHEN s_sex="女" THEN 1 ELSE NULL END) "女生人数"
FROM student;

29、查询名字中含有"风"字的学生信息（不重点）

SELECT s_name
FROM student
WHERE s_name LIKE "%风%";

参考：

https://zhuanlan.zhihu.com/p/43289968
https://www.bilibili.com/video/BV1q4411G7Lw