SQL经典50题练习

文章出处：https://zhuanlan.zhihu.com/p/38354000

一、表结构创建

表结构：

 

 sql脚本：

/*
 Navicat Premium Data Transfer
 Date: 22/03/2020 17:27:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `课程号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `课程名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `教师号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('0001', '语文', '0002');
INSERT INTO `course` VALUES ('0002', '数学', '0001');
INSERT INTO `course` VALUES ('0003', '英语', '0003');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `学号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `课程号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `成绩` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('0001', '0001', '80');
INSERT INTO `score` VALUES ('0001', '0002', '90');
INSERT INTO `score` VALUES ('0001', '0003', '99');
INSERT INTO `score` VALUES ('0002', '0002', '60');
INSERT INTO `score` VALUES ('0002', '0003', '80');
INSERT INTO `score` VALUES ('0003', '0001', '80');
INSERT INTO `score` VALUES ('0003', '0002', '20');
INSERT INTO `score` VALUES ('0003', '0003', '40');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `学号` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `姓名` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `出生日期` datetime(0) NOT NULL,
  `性别` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('0001', '猴子', '1989-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('0002', '猴子', '1990-12-21 00:00:00', '女');
INSERT INTO `student` VALUES ('0003', '马云', '1991-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('0004', '王思聪', '1990-05-20 00:00:00', '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `教师号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `教师姓名` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('0001', '孟扎扎');
INSERT INTO `teacher` VALUES ('0002', '马化腾');

SET FOREIGN_KEY_CHECKS = 1;

二、习题

-- 查询姓'猴'的学生
SELECT * FROM student WHERE `姓名` LIKE '猴%'

-- 查询姓名中最后一个字是'猴'的学生
SELECT * FROM student WHERE `姓名` LIKE '%猴'

-- 查询姓名中带'猴'字的学生
SELECT * FROM student WHERE `姓名` LIKE '%猴%'

-- 查询课程编号为“0002”的总成绩
SELECT sum(`成绩`) FROM score WHERE `学号` = '0002'

-- 查询选了课程的学生人数************************************
SELECT COUNT(DISTINCT `学号`)  FROM score

-- 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分 ************************************
SELECT `课程号`,MAX(`成绩`) as '最高分',MIN(`成绩`) as '最低分' FROM score GROUP BY `课程号`

-- 查询每门课程被选修的学生数
SELECT `课程号`,COUNT(`学号`) FROM score GROUP BY `课程号`

-- 查询男生、女生人数
SELECT 性别,count(`性别`) FROM student GROUP BY `性别`

-- 查询平均成绩大于60分学生的学号和平均成绩************************************
SELECT `学号`,AVG(`成绩`) as '平均成绩' FROM score GROUP BY `学号` HAVING AVG(`成绩`) > 60

-- 查询至少选修两门课程的学生学号
SELECT `学号`,COUNT(`课程号`) as '选修课程数目' FROM score GROUP BY `学号` HAVING COUNT(`课程号`) >= 2

-- 查询同名同性学生名单并统计同名人数
SELECT `姓名`,COUNT(`姓名`) as '人数' FROM student GROUP BY `姓名`

-- 查询不及格的课程并按课程号从大到小排列
SELECT `课程号` FROM score WHERE `成绩` < 60 ORDER BY `课程号` DESC

-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT `课程号`,AVG(`成绩`) as '平均成绩' FROM score GROUP BY `课程号` ORDER BY `平均成绩` ASC , `课程号` DESC

-- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT * FROM score WHERE `课程号` = '0004' AND `成绩` < 60 ORDER BY `成绩` DESC

-- 统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT `课程号`,COUNT(`学号`) AS '选修人数' FROM score GROUP BY `课程号` HAVING `选修人数` > 2 ORDER BY `选修人数` DESC , `课程号` ASC

-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT `学号`,AVG(`成绩`) FROM score WHERE `成绩` < 60 GROUP BY `学号` HAVING COUNT(`课程号`) >= 2

-- 查询所有课程成绩小于60分学生的学号、姓名
SELECT stu.`学号`,stu.`姓名`,sco.`成绩` from score sco,student stu WHERE sco.`学号` = stu.`学号` AND sco.`成绩` < 60

-- 查询没有学全所有课的学生的学号、姓名
SELECT * FROM student stu , (SELECT `学号` FROM score GROUP BY `学号` HAVING COUNT(`课程号`) < (SELECT COUNT(1) FROM course)) t2 
WHERE stu.`学号` = t2.`学号`

-- 查找1990年出生的学生名单
SELECT * FROM student WHERE YEAR(`出生日期`) = '1990'

-- 查询各科成绩前两名的记录  *****************重点看；有点蒙**********************

SELECT
    * 
FROM
    score s1 
WHERE
    ( SELECT COUNT( 1 ) FROM score s2 WHERE s1.`课程号` = s2.`课程号` AND s1.`成绩` < s2.`成绩` ) < 2
ORDER BY
    s1.`课程号`,
    s1.`成绩` DESC
    
-- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称 ************重点看***********
SELECT `学号`,max(CASE `课程号` WHEN '0001' THEN `成绩` ELSE 0 END) as '课程号001',max(CASE `课程号` WHEN '0002' THEN `成绩` ELSE 0 END) AS '课程号002' ,

max(CASE `课程号` WHEN '0003' THEN `成绩` ELSE 0 END)  as '课程号003' FROM score GROUP BY `学号`