SQL50题

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

SELECT a.s_id,a.s_score FROM
(SELECT * FROM score WHERE c_id='01') as a
INNER JOIN
(SELECT * FROM score WHERE c_id='02') as b
on a.s_id=b.s_id
WHERE a.s_score>b.s_score;

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 a.s_id,s_name

16、检索"01"课程分数小于60，按分数降序排列的学生信息

# 方法一：个人
SELECT * FROM student
INNER JOIN
(SELECT s_id,s_score FROM score WHERE c_id=01 AND s_score<60) as t
on student.s_id=t.s_id
ORDER BY s_score DESC;

# 方法二：个人
SELECT * FROM student
INNER JOIN score ON student.s_id=score.s_id

WHERE s_score<60 AND c_id=01
ORDER BY s_score DESC;

# 方法三：
SELECT * 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_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，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

SELECT s.c_id "课程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 course AS c
INNER JOIN
score as s ON c.c_id=s.c_id
GROUP BY c.c_id

SELECT c_id,
       avg(CASE WHEN s_score >= 0 AND s_score < 60 THEN 1.0 ELSE 0.0 END)   "及格率",
       avg(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1.0 ELSE 0.0 END)  "中等率",
       avg(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1.0 ELSE 0.0 END)  "良好率",
       avg(CASE WHEN s_score >= 85 AND s_score < 100 THEN 1.0 ELSE 0.0 END) "优秀率"
FROM score
GROUP BY c_id;

19、按各科成绩进行排序，并显示排名(重点row_number)

SELECT *,row_number()  OVER (PARTITION BY c_id ORDER BY s_score DESC ) FROM score;

窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。
窗口函数大体可以分为以下两种：

1.能够作为窗口函数的聚合函数（sum，avg，count，max，min）
2.rank，dense_rank，row_number等专用窗口函数。
语法的基本使用方法：使用rank函数
rank函数是用来计算记录排序的函数
添加链接描述
专用函数的种类：1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
3.row_number函数：赋予唯一的连续位次。

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

SELECT c.c_id "课程ID",c_name "课程名字",
sum(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE 0 END ) AS "[100,85]",
sum(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE 0 END ) "[85,70]",
sum(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE 0 END )"[70,60]",
sum(CASE WHEN s_score<=60 THEN 1 ELSE 0 END )"<60"

FROM score as s
INNER JOIN course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;

SELECT c.c_id "课程ID",c_name "课程名字",
count(CASE WHEN s_score<=100 and s_score>85 THEN 1 ELSE NULL END ) AS "[100,85]",
count(CASE WHEN s_score<=85 and s_score>70 THEN 1 ELSE NULL END ) "[85,70]",
count(CASE WHEN s_score<=70 and s_score>60 THEN 1 ELSE NULL END )"[70,60]",
count(CASE WHEN s_score<=60 THEN 1 ELSE NULL END )"<60"

FROM score as s
INNER JOIN course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;

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

SELECT s_id,avg(s_score),
       rank() OVER (ORDER BY avg(s_score)) as ranking FROM score
group by s_id


# 不可加partition by，需使用group by

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
26、查询每门课程被选修的学生数(不重点)

26、查询每门课程被选修的学生数(不重点)
SELECT c_name,COUNT(c.c_id)
FROM score as s
INNER JOIN
course as c on s.c_id=c.c_id
GROUP BY c_name;

# 注：一般需要将select后的字段放在group by后面

SELECT c.c_id,c.c_name,count(DISTINCT s.s_id) AS "数量"
FROM score as s
INNER JOIN
course as c on s.c_id=c.c_id
GROUP BY c.c_id,c.c_name;

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

# 方法一：
SELECT s.s_id,s.s_name
FROM student AS s
INNER JOIN
  score as sc on s.s_id=sc.s_id
GROUP BY s.s_id HAVING count(DISTINCT c_id)=2;

# 错误
SELECT s.s_name,s.s_id
FROM student AS s
INNER JOIN
  score as sc on s.s_id=sc.s_id
WHERE count(DISTINCT c_id)=2;

# 方法二：
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);

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

# 方法一：
SELECT s_sex,count(s_sex) FROM student
GROUP BY s_sex;
# 方法二：
SELECT
sum(CASE WHEN s_sex='男' THEN 1 ELSE 0 END ) "男生人数",
sum(CASE WHEN s_sex='女' THEN 1 ELSE 0 END ) "女生人数"
FROM student
# 方法三：
SELECT
count(CASE WHEN s_sex='男' THEN 1 ELSE NULL END ) "男生人数",
count(CASE WHEN s_sex='女' THEN 1 ELSE NULL END ) "女生人数"
FROM student

# 注：null对count来说是不计算个数的，所以后面不能写0，只能用null

35、查询所有学生的课程及分数情况（重点）

# 不对
SELECT s.s_id,s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON c.c_id=sc.c_id
GROUP BY s.s_id,s.s_name

# 正确做法：(行转列)
SELECT s.s_id,s.s_name,
max(CASE WHEN c.c_name="语文" THEN s_score ELSE NULL END ) AS "语文",
max(CASE WHEN c.c_name="数学" THEN s_score ELSE NULL END ) AS "数学",
max(CASE WHEN c.c_name="英语" THEN s_score ELSE NULL END ) AS "英语"
FROM student as s
LEFT JOIN score as sc
ON s.s_id=sc.s_id
LEFT JOIN course as c
ON c.c_id=sc.c_id
GROUP BY s.s_id,s.s_name

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

# 错误处理方法：
SELECT s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc on s.s_id=sc.s_id
INNER JOIN course AS c ON c.c_id=sc.c_id
GROUP BY s.s_id HAVING sc.s_score>70;

# 正确处理方法：
SELECT s.s_name,c.c_name,sc.s_score
FROM student as s
INNER JOIN score as sc on s.s_id=sc.s_id
INNER JOIN course AS c ON c.c_id=sc.c_id
WHERE sc.s_score>70;
# 注：where 后面要跟的是数据表里的字段，where针对数据库文件的发挥作用，
# 而having只是根据前面查询出来的结果集再次进行查询，因此having是针对结果集发挥作用。

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

SELECT s.s_id,s.s_name,c.c_name,c.c_id,sc.s_score
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON sc.c_id=c.c_id
INNER JOIN teacher as t
ON t.t_id=c.t_id
WHERE t.t_name="张三" ORDER BY sc.s_score DESC limit 0,1; # 0表示从0开始取，若为降序排列则0是第一位，1表示取几条

# SQL SERVER 中用top
/*
SELECT top 1 s.s_id,s.s_name,c.c_name,c.c_id
FROM student as s
INNER JOIN score as sc
ON s.s_id=sc.s_id
INNER JOIN course as c
ON sc.c_id=c.c_id
INNER JOIN teacher as t
ON t.t_id=c.t_id
WHERE t.t_name="张三" ORDER BY DESC
*/