• SQL必会50题


    根据知乎进行练习, 有些还是有点难度

    链接https://zhuanlan.zhihu.com/p/43289968

    -- 1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
    USE sql_test;
    SELECT a.s_id, a.s_score AS '01', b.s_score AS '02', s.s_name
    FROM 
    (SELECT s_id, c_id, s_score FROM score WHERE c_id = 01) AS a
    JOIN
    (SELECT s_id, c_id, s_score FROM score WHERE c_id = 02) AS b
    ON a.s_id = b.s_id
    JOIN student s
    ON a.s_id = s.s_id
    WHERE a.s_score > b.s_score;
    -- 2、查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
    SELECT sc.s_id, st.s_name, AVG(sc.s_score) AS avg_score
    FROM score sc
    JOIN student st
    ON sc.s_id = st.s_id
    GROUP BY s_id
    HAVING avg_score > 60;
    
    -- 3、查询所有学生的学号、姓名、选课数、总成绩(不重要)
    SELECT 
    	st.s_id,
        st.s_name,
        COUNT(sc.c_id),
        SUM(IF(sc.s_score IS NULL, 0, sc.s_score))
    FROM student st
    LEFT JOIN score sc
    ON st.s_id = sc.s_id
    GROUP BY st.s_id;
    
    SELECT 
    	st.s_id,
        st.s_name,
        COUNT(sc.c_id),
        SUM(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END)
    FROM student st
    LEFT JOIN score sc
    ON st.s_id = sc.s_id
    GROUP BY st.s_id;
    
    -- 4、查询姓“猴”的老师的个数(不重要)
    SELECT COUNT(*)
    FROM teacher
    WHERE t_name LIKE '猴%';
    
    -- 5、查询没学过“张三”老师课的学生的学号、姓名(重点)
    SELECT *
    FROM student st
    WHERE st.s_id NOT IN
    	(SELECT sc.s_id
    	FROM course c
    		JOIN score sc USING(c_id)
    	WHERE c.t_id =
    		(SELECT t_id
    		FROM teacher
    		WHERE t_name = '张三'));
    -- 6、查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
    USE sql_test;
    SELECT *
    FROM student st
    WHERE st.s_id  IN
    	(SELECT sc.s_id
    	FROM course c
    		JOIN score sc USING(c_id)
    	WHERE c.t_id =
    		(SELECT t_id
    		FROM teacher
    		WHERE t_name = '张三'));
            
    -- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
    SELECT 
    	a.s_id,
        st.s_name
    FROM 
    	(SELECT *
    	FROM score 
    	WHERE c_id = '01') AS a
    JOIN 
    	(SELECT *
    	FROM score
    	WHERE c_id = '02') AS b
    On a.s_id = b.s_id
    JOIN student st
    ON a.s_id = st.s_id;
    
    SELECT *
    FROM student
    WHERE s_id IN (
    	(SELECT a.s_id FROM 
    	(SELECT * FROM score WHERE c_id = '01') AS a
    	JOIN 
    	(SELECT * FROM score WHERE c_id = '02') AS b
    	On a.s_id = b.s_id)
    );
    
    -- 8、查询课程编号为“02”的总成绩(不重点)
    SELECT SUM(s_score)
    FROM score
    WHERE c_id = '02';
    -- 9、查询所有课程成绩小于60分的学生的学号、姓名
    -- 也可以用子查询
    SELECT 
    	DISTINCT st.s_id, 
        st.s_name
    FROM score sc
    JOIN student st
    ON sc.s_id = st.s_id
    WHERE s_score < 60;
    -- 10.查询没有学全所有课的学生的学号、姓名(重点)
    -- 这个答案不全, 主要是从成绩表查询,但是有些没选课,因此没有成绩
    SELECT * 
    FROM student st
    JOIN 
    	(SELECT s_id FROM score
    	GROUP BY s_id 
    	HAVING count(DISTINCT c_id) < (SELECT COUNT( DISTINCT c_id) FROM course)) AS a
    ON st.s_id = a.s_id;
    -- 反过来思考, 所有学完课程的学生比较好查询
    SELECT 
    	st.s_id, 
        st.s_name,
        COUNT(DISTINCT sc.c_id) AS count_c
    FROM student st
    LEFT JOIN score sc
    ON st.s_id = sc.s_id
    GROUP BY st.s_id
    HAVING count_c < 3;
    -- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名(重点)
    SELECT *
    FROM student st
    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 sc 
    ON st.s_id = sc.s_id;
    
    -- 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
    
    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 s_id FROM score
    	WHERE c_id NOT IN (
    		SELECT c_id
    		FROM score
    		WHERE s_id = '01'
    	)
    );
    --  13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样(重点,能做出来)
    SELECT * FROM student st
    WHERE st.s_id NOT IN (
    	SELECT sc.s_id
    	FROM score sc
    	WHERE sc.c_id = (
    		SELECT c_id FROM course c
    		WHERE c.t_id = (
    			SELECT t_id
    			FROM teacher
    			WHERE t_name = '张三'))
    );
    -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(重点)
    
    SELECT sc.s_id,
    	AVG(sc.s_score),
        st.s_name
    FROM score sc
    JOIN student st
    ON sc.s_id = st.s_id
    WHERE sc.s_score < 60 
    GROUP BY sc.s_id
    HAVING COUNT(sc.c_id) >= 2;
    
    -- 16、检索"01"课程分数小于60,按分数降序排列的学生信息(和34题重复,不重点)
    
    SELECT *
    FROM student st
    JOIN score sc
    ON st.s_id = sc.s_id
    WHERE sc.s_score < 60 AND sc.c_id = '01'
    ORDER BY sc.s_score DESC;
    -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    -- 格式不清晰
    
    SELECT s_id,
    	st.s_id,
        sc.c_id,
        sc.s_score,
        avg_score
    FROM student st
    LEFT JOIN score sc
    ON st.s_id = sc.s_id
    LEFT JOIN 
    	(SELECT 
    	sc.s_id,
        AVG(sc.s_score) as avg_score
    	FROM score sc
    	GROUP BY sc.s_id) AS avg_s
    ON st.s_id = avg_s.s_id
    ORDER BY avg_score DESC;
    -- 第二种结果
    USE sql_test;
    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 sc
    GROUP BY s_id
    ORDER BY AVG(s_score) DESC;
    
    -- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    
    -- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 (超级重点)
    
    SELECT 
    	sc.c_id,
        c.c_name,
        MAX(sc.s_score),
        MIN(sc.s_score),
        AVG(sc.s_score),
        SUM(CASE WHEN sc.s_score >= 60 THEN 1.0 ELSE 0 END) / COUNT(s_id) '及格率',
        AVG(CASE WHEN sc.s_score >= 70 AND sc.s_score <80 THEN 1.0 ELSE 0 END) '中等率',
        AVG(CASE WHEN sc.s_score >= 80 AND sc.s_score <90 THEN 1.0 ELSE 0 END) '优良率',
        AVG(CASE WHEN sc.s_score >= 90 THEN 1.0 ELSE 0 END) '优秀率'
    FROM score sc
    JOIN course c
    ON sc.c_id = c.c_id
    GROUP BY c_id;
    
    -- 19、按各科成绩进行排序,并显示排名(重点row_number)
    
    SELECT 
    	s_id,
        c_id,
        RANK() OVER(PARTITION BY c_id ORDER BY s_score)
    FROM score;
    
    -- 查询学生的总成绩并进行排名(不重点), 一般都是用RANK
    -- ROW_NUMBER
    -- RANK
    -- DENSE_RANK
    USE sql_test;
    SELECT 
    	s_id,
        SUM(s_score) AS sum_score
    FROM score
    GROUP BY s_id
    ORDER BY sum_score DESC;
    -- 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
    -- 以课程为主体
    SELECT 
    	c.c_id,
        c.c_name,
        AVG(s_score)
    FROM course c
    JOIN score sc
    ON c.c_id = sc.c_id
    GROUP BY c.c_id
    ORDER BY  AVG(s_score);
    -- 以老师为主体
    SELECT 
    	te.t_id,
        te.t_name,
        AVG(s_score)
    FROM course c
    JOIN score sc
    ON c.c_id = sc.c_id
    JOIN teacher te
    ON te.t_id = c.t_id
    GROUP BY c.c_id
    ORDER BY  AVG(s_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
    	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 
    	sc.c_id,
        c.c_name,
        SUM(CASE WHEN s_score < 60 THEN 1 ELSE 0 END) '<60',
        COUNT(CASE WHEN s_score <= 70 AND s_score > 60 THEN 1 ELSE NULL END) '70-60',
        SUM(CASE WHEN s_score <= 85 AND s_score > 70 THEN 1 ELSE 0 END) '85-70',
    	SUM(CASE WHEN s_score > 85 THEN 1 ELSE 0 END) '100-85'
    FROM score sc
    JOIN course c
    ON sc.c_id = c.c_id
    GROUP BY c_id;
    
    -- 24、查询学生平均成绩及其名次(同19题,重点)
    
    SELECT 
    	s_id,
        AVG(s_score),
        ROW_NUMBER() OVER(PARTITION BY s_id ORDER BY AVG(s_score)) m
    FROM score
    GROUP BY s_id;
    
    -- 25、查询各科成绩前三名的记录(不考虑成绩并列情况)(重点 与22题类似)
    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
    	JOIN student st
    	ON sc.s_id = st.s_id) a
    WHERE m IN (1, 2, 3);
    -- 26、查询每门课程被选修的学生数(不重点)
    SELECT 
    	c.c_id,
        c.c_name,
        COUNT(sc.s_id)
    FROM course c
    JOIN score sc
    ON c.c_id = sc.c_id
    GROUP BY c.c_id;
    
    -- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
    SELECT 
    	st.s_id,
        st.s_name,
        COUNT(sc.c_id) as count_c
    FROM student st
    JOIN score sc
    ON st.s_id = sc.s_id
    GROUP BY st.s_id
    HAVING count_c = 2;
    
    -- 28、查询男生、女生人数(不重点)
    SELECT 
    	s_sex,
        COUNT(s_id)
    FROM student
    GROUP BY s_sex;
    
    -- 29 查询名字中含有"风"字的学生信息(不重点)
    SELECT *
    FROM student
    WHERE s_name LIKE '%风%';
    -- 31、查询1990年出生的学生名单(重点year)
    
    SELECT *
    FROM student 
    WHERE YEAR(s_birth) = 1990;
    
    -- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
    USE sql_test;
    SELECT 
    	st.s_id,
        st.s_name,
        AVG(sc.s_score)
    FROM student st
    JOIN score sc
    ON st.s_id = sc.s_id
    GROUP BY st.s_id
    HAVING AVG(sc.s_score) > 85;
    
    -- 33、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列(不重要)
    SELECT 
    	c_id,
        AVG(s_score)
    FROM score
    GROUP BY c_id
    ORDER BY AVG(s_score), c_id DESC;
    
    -- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数(不重点)
    SELECT 
    	st.s_name,
        sc.s_score
    FROM course co
    JOIN score sc ON co.c_id = sc.c_id
    JOIN student st ON sc.s_id = st.s_id
    WHERE c_name = '数学' AND sc.s_score < 60;
    
    -- 35查询所有学生的课程及分数情况(重点)
    SELECT 
    	sc.s_id,
        MAX(CASE WHEN co.c_name = '数学' THEN sc.s_score ELSE NULL END) '数学',
        MAX(CASE WHEN co.c_name = '语文' THEN sc.s_score ELSE NULL END) '语文',
        MAX(CASE WHEN co.c_name = '英语' THEN sc.s_score ELSE NULL END) '英语'
    FROM score sc
    JOIN course co ON sc.c_id = co.c_id
    GROUP BY sc.s_id;
    
    -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数(重点)
    
    SELECT 
    	sc.s_id,
    	st.s_name,
        sc.s_score
    FROM score sc
    JOIN student st ON sc.s_id = st.s_id
    WHERE sc.s_score > 70;
    -- 37、查询不及格的课程并按课程号从大到小排列(不重点)
    SELECT *
    FROM score
    WHERE s_score < 60
    ORDER BY c_id DESC;
    -- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
    SELECT 
    	sc.s_id,
    	st.s_name
    FROM score sc
    JOIN student st ON sc.s_id = st.s_id
    WHERE sc.c_id = '03' AND sc.s_score > 80;
    -- 39、求每门课程的学生人数(不重要)
    SELECT 
    	c_id,
    	COUNT(c_id)
    FROM score
    GROUP BY c_id;
    
    
    -- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(重要top)
    SELECT 
    	st.s_name,
        sc.s_score
    FROM teacher te
    JOIN course co ON te.t_id = co.t_id
    JOIN score sc ON co.c_id = sc.c_id
    JOIN student st ON sc.s_id = st.s_id
    WHERE te.t_name = '张三'
    ORDER BY sc.s_score DESC LIMIT 1;
    
    -- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (重点)
    USE sql_test;
    SELECT 
    	DISTINCT s1.s_id, s1.c_id, s1.s_score
    FROM score s1
    JOIN score s2
    ON s1.s_id = s2.s_id
    WHERE s1.s_score = s2.s_score AND s1.c_id != s2.c_id;
    
    -- 42查询每门功成绩最好的前两名
    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
    	JOIN student st
    	ON sc.s_id = st.s_id) a
    WHERE m IN (1, 2);
    
    -- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列(不重要)
    SELECT 
    	c_id,
        COUNT(c_id)
    FROM score
    GROUP BY c_id
    HAVING COUNT(c_id) > 5
    ORDER BY COUNT(c_id) DESC, c_id;
    
    -- 44、检索至少选修两门课程的学生学号(不重要)
    SELECT 
    	s_id,
        COUNT(DISTINCT c_id) AS count_course
    FROM score
    GROUP BY s_id
    HAVING count_course >= 2;
    
    -- 45、 查询选修了全部课程的学生信息(重点划红线地方)
    SELECT 
    	s_id,
        COUNT(c_id)
    FROM score
    GROUP BY s_id
    HAVING COUNT(c_id) = (SELECT COUNT(*) FROM course);
    
    -- 47、查询没学过“张三”老师讲授的任一门课程的学生姓名(还可以,自己写的,答案中没有)
    SELECT *
    FROM student
    WHERE s_id NOT IN (
    	SELECT sc.s_id
    	FROM score sc
    	JOIN course co ON sc.c_id = co.c_id
    	JOIN teacher te ON co.t_id = te.t_id
    	WHERE te.t_name = '张三'
    );
    -- 查询两门以上不及格课程的同学的学号及其平均成绩(还可以,自己写的,答案中没有)
    
    SELECT 
    	s_id, 
    	COUNT(s_score), 
        AVG(s_score)
    FROM score
    WHERE s_score < 60
    GROUP BY s_id
    HAVING COUNT(s_score) >= 2;
    
    -- 46、查询各学生的年龄(精确到月份)
    SELECT 
    	YEAR(CURDATE()) - YEAR(s_birth)
    FROM student;
    SELECT
    	s_id,
    	datediff(CURDATE(), s_birth) / 240
    FROM student;
    
    
    -- 47、查询本月过生日的学生(无法使用week、date(now())
    SELECT *
    FROM student
    WHERE MONTH(s_birth) = MONTH(CURDATE());
    
  • 相关阅读:
    出售几个闲置的玉米
    竞价账户时好时坏怎样分析找到原因?
    两行代码轻松搞定网页自动刷新,简单,实用。
    BCompare文件对比神器 工程师 开发必备工具之一
    关于 Apache 屏蔽 IP 地址
    一段 JavaScript 实现禁止用户打开控制台与鼠标右键查看源码
    Apache使用.htaccess防盗链禁止用户下载
    HTML5下video右键禁用-禁止右键下载视频
    python装饰器
    部署nginx支持lua
  • 原文地址:https://www.cnblogs.com/jly1/p/13049707.html
Copyright © 2020-2023  润新知