50道SQL练习题及答案与分析（11-20）多种解法
站长
· 阅读数 11
题目以及建表语句sql等在本系列的第一篇文章，请自取 链接地址
11 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT * from student RIGHT JOIN (SELECT Sid,count(score) as count,avg(score) as avgScore from sc where score < 60
GROUP BY Sid HAVING count >= 2) a
ON student.Sid = a.Sid
-- SELECT Sid,count(score) as count,avg(score) as avgScore from sc where score < 60
-- GROUP BY Sid
--
-- SELECT Sid,score from sc where score < 60
-- SELECT Sid,count(score) from sc where score < 60 GROUP BY Sid
SELECT s.SId, s.Sname, AVG(sc.score) as avg_score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
WHERE sc.score < 60
GROUP BY s.SId, s.Sname
HAVING COUNT(sc.CId) >= 2;
12 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 先筛选后连接表
SELECT *
FROM Student
RIGHT JOIN (
SELECT *
FROM SC
WHERE CId = '01' AND score < 60
) AS subquery
ON Student.SId = subquery.SId;
ORDER BY score DESC
-- 先连接表后筛选
SELECT Student.*
FROM Student
INNER JOIN SC ON Student.SId = SC.SId
WHERE SC.CId = '01' AND SC.score < 60
ORDER BY SC.score DESC;
13 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc
left join (
select sid,avg(score) as avscore from sc
group by sid
)r
on sc.sid = r.sid
order by avscore desc;
14 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.Cid,
course.Cname,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(DISTINCT SC.SId)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
LEFT JOIN course ON sc.Cid = course.Cid
GROUP BY course.Cid
ORDER BY count(*)DESC, sc.CId ASC
SELECT
Course.CId AS '课程 ID',
Course.Cname AS '课程 name',
MAX(score) AS '最高分',
MIN(score) AS '最低分',
AVG(score) AS '平均分',
ROUND(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '及格率',
ROUND(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '中等率',
ROUND(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '优良率',
ROUND(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '优秀率',
COUNT(DISTINCT SC.SId) AS '选修人数'
FROM
Course
JOIN SC ON Course.CId = SC.CId
GROUP BY
Course.CId, Cname
ORDER BY
COUNT(DISTINCT SC.SId) DESC,
Course.CId ASC;
SELECT
SC.CId AS '课程 ID',
Course.Cname AS '课程 name',
MAX(SC.score) AS '最高分',
MIN(SC.score) AS '最低分',
AVG(SC.score) AS '平均分',
CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 60 THEN 1 END)/COUNT(*),3)*100,'%') AS '及格率',
CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 70 AND SC.score < 80 THEN 1 END)/COUNT(*),3)*100,'%') AS '中等率',
CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 80 AND SC.score < 90 THEN 1 END)/COUNT(*),3)*100,'%') AS '优良率',
CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 90 THEN 1 END)/COUNT(*),3)*100,'%') AS '优秀率'
FROM
SC
JOIN Course ON SC.CId = Course.CId
GROUP BY
SC.CId,
Course.Cname
15 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT
CId,
score,
(
SELECT
COUNT(DISTINCT score)
FROM
SC as B
WHERE
B.CId = A.CId and B.score > A.score
) + 1 AS rank
FROM
SC AS A
ORDER BY
CId, score DESC;
16 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT S.SId, S.Sname, SUM(SC.score) AS student_toal_score,
(
SELECT
COUNT(*) + 1
FROM
(
SELECT
SUM(score) AS total_score
FROM
SC
GROUP BY
SId
) T
WHERE
T.total_score > SUM(SC.score)
) AS rank
FROM Student S JOIN SC ON S.SId = SC.SId
GROUP BY S.SId, S.Sname
ORDER BY student_toal_score DESC;
17 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT t.cid,level,levelCount,totalCount,(levelCount/totalCount) from (
(SELECT cid,(CASE
WHEN 85<score and score<=100 THEN '[100-86]'
WHEN 70<=score and score<=85 THEN '[85-70]'
WHEN 60<score and score<70 THEN '[69-61]'
WHEN score<=60 THEN '[60-0]'
END) as level,COUNT(*) as levelCount from sc
GROUP BY cid,level) as t
LEFT JOIN
(SELECT cid,COUNT(*) as totalCount from sc GROUP BY cid) as y
ON t.cid = y.cid
)
ORDER BY cid
SELECT
SC.CId AS '课程编号',
Course.Cname AS '课程名称',
COUNT(CASE WHEN SC.score BETWEEN 85 AND 100 THEN 1 END) AS '[100-85]',
COUNT(CASE WHEN SC.score BETWEEN 70 AND 84.9 THEN 1 END) AS '[85-70]',
COUNT(CASE WHEN SC.score BETWEEN 60 AND 69.9 THEN 1 END) AS '[70-60]',
COUNT(CASE WHEN SC.score BETWEEN 0 AND 59.9 THEN 1 END) AS '[60-0]',
CONCAT(ROUND(100 * COUNT(CASE WHEN SC.score BETWEEN 85 AND 100 THEN 1 END) / COUNT(*), 2), '%') AS '[100-85]百分比',
CONCAT(ROUND(100 * COUNT(CASE WHEN SC.score BETWEEN 70 AND 84.9 THEN 1 END) / COUNT(*), 2), '%') AS '[85-70]百分比',
CONCAT(ROUND(100 * COUNT(CASE WHEN SC.score BETWEEN 60 AND 69.9 THEN 1 END) / COUNT(*), 2), '%') AS '[70-60]百分比',
CONCAT(ROUND(100 * COUNT(CASE WHEN SC.score BETWEEN 0 AND 59.9 THEN 1 END) / COUNT(*), 2), '%') AS '[60-0]百分比'
FROM
SC
JOIN Course ON SC.CId = Course.CId
GROUP BY
SC.CId,
Course.Cname;
18 查询各科成绩前三名的记录
SELECT c.Cname AS 课程名称, s.Sname AS 学生姓名, sc.score AS 分数
FROM Course c
JOIN (
SELECT CId, SId, score,
(SELECT COUNT(DISTINCT score)
FROM SC
WHERE CId = sc1.CId AND score > sc1.score) + 1 AS rank
FROM SC sc1
WHERE (SELECT COUNT(*) FROM SC sc2 WHERE sc1.CId = sc2.CId AND sc1.score < sc2.score) < 3
) sc ON c.CId = sc.CId
JOIN Student s ON sc.SId = s.SId
WHERE sc.rank <= 3
ORDER BY c.Cname ASC, sc.score DESC;
select a.sid,a.cid,a.score from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<3
order by a.cid;
select * from SC a where (select COUNT(*)from SC where Cid=a.Cid and score>a.score)<3
order by a.Cid,a.score desc
19 查询每门课程被选修的学生数
select cid, count(sid) from sc
group by cid;
20 查询出只选修两门课程的学生学号和姓名
select student.sid, student.sname from student
where student.sid in
(select sc.sid,COUNT(*) from sc
group by sc.sid
having count(sc.cid)=2
);