50道SQL练习题及答案与分析（11-20）多种解法

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
);