Sql经典50题练习（未完）

Sql经典50题

建表

1. 学生表

   CREATE TABLE student (
   s_id VARCHAR(30),
   s_name VARCHAR(30) NOT NULL,
   s_birth date NOT NULL,
   s_sex VARCHAR(10) NOT NULL,
   PRIMARY KEY(s_id)
   );
   

2. 成绩表

   CREATE TABLE score (
   s_id VARCHAR(30),
   c_id VARCHAR(30),
   s_score VARCHAR(3),
   PRIMARY KEY(s_id, c_id)
   );
   

3. 课程表

   CREATE TABLE course (
   c_id VARCHAR(30),
   c_name VARCHAR(30) NOT NULL,
   t_id VARCHAR(30) NOT NULL,
   PRIMARY KEY(c_id)
   );
   

4. 教师表

   CREATE TABLE teacher (
   t_id VARCHAR(30),
   t_name VARCHAR(30) NOT NULL,
   PRIMARY KEY(t_id)
   );
   

插入数据

1. 学生表

   insert into student(s_id,s_name,s_birth,s_sex) 
   values('0001' , '猴子' , '1989-01-01' , '男');
   
   insert into student(s_id,s_name,s_birth,s_sex) 
   values('0002' , '猴子' , '1990-12-21' , '女');
   
   insert into student(s_id,s_name,s_birth,s_sex) 
   values('0003' , '马云' , '1991-12-21' , '男');
   
   insert into student(s_id,s_name,s_birth,s_sex) 
   values('0004' , '王思聪' , '1990-05-20' , '男');
   

2. 成绩表

   insert into score(s_id,c_id,s_score) 
   values('0001' , '0001' , 80);
   
   insert into score(s_id,c_id,s_score) 
   values('0001' , '0002' , 90);
   
   insert into score(s_id,c_id,s_score) 
   values('0001' , '0003' , 99);
   
   insert into score(s_id,c_id,s_score) 
   values('0002' , '0002' , 60);
   
   insert into score(s_id,c_id,s_score) 
   values('0002' , '0003' , 80);
   
   insert into score(s_id,c_id,s_score) 
   values('0003' , '0001' , 80);
   
   insert into score(s_id,c_id,s_score) 
   values('0003' , '0002' , 80);
   
   insert into score(s_id,c_id,s_score) 
   values('0003' , '0003' , 80);
   

3. 课程表

   insert into course(c_id,c_name,t_id)
   values('0001' , '语文' , '0002');
   
   insert into course(c_id,c_name,t_id)
   values('0002' , '数学' , '0001');
   
   insert into course(c_id,c_name,t_id)
   values('0003' , '英语' , '0003');
   

4. 教师表

   -- 教师表：添加数据
   insert into teacher(t_id,t_name) 
   values('0001' , '孟扎扎');
   
   insert into teacher(t_id,t_name) 
   values('0002' , '马化腾');
   
   -- 这里的t_name是空值（null）
   insert into teacher(t_id,t_name) 
   values('0003' , null);
   
   -- 这里的t_name是空字符串（''）
   insert into teacher(t_id,t_name) 
   values('0004' , '');
   

题目

1. 简单查询

-- 查询姓“猴”的学生名单
SELECT * from student where s_name LIKE '猴%';

-- 查询姓名中最后一个字是“猴”的学生名单
SELECT * FROM student where s_name LIKE '%猴';

-- 查询姓名中带“猴”字的学生名单
SELECT * FROM student where s_name LIKE '%猴%';

-- 查询姓“孟”老师的个数
SELECT COUNT(t_id) from teacher WHERE t_name LIKE '孟%';

2. 汇总分析

1. 汇总分析

   -- 查询课程编号为“0002”的总成绩
   SELECT SUM(s_score) FROM score WHERE c_id = '0002';
   
   -- 查询选了课程的学生数
   SELECT COUNT(DISTINCT(s_id)) AS 学生人数 FROM score;
   

2. 分组

   -- 查询各科成绩最高和最低的分
   SELECT c_id, MAX(s_score) AS 最高分, MIN(s_score) AS 最低分 FROM score GROUP BY c_id;
   
   -- 查询每门课程被选修的学生数
   SELECT c_id, COUNT(s_id) AS 学生数 FROM score GROUP BY c_id;
   
   -- 查询男生，女生人数
   SELECT s_sex, COUNT(*) AS 人数 FROM student GROUP BY s_sex;
   

3. 分组结果的条件

   -- 查询平均成绩大于60分学生的学号和平均成绩
   SELECT s_id, AVG(s_score) from score GROUP BY s_id HAVING AVG(s_score) > 60;
   
   -- 查询至少选修两门课程的学生学号
   SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) >= 2;
   
   -- 查询同名同姓的学生名单并统计同名人数
   SELECT s_name, COUNT(*) AS 人数 FROM student GROUP BY s_name HAVING COUNT(*) >= 2; 
   
   -- 查询不及格的课程并按课程号从大到小排列
   SELECT c_id FROM score WHERE s_score < 60 ORDER BY c_id DESC;
   
   -- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
   SELECT c_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY c_id ORDER BY AVG(s_score) ASC, c_id DESC;
   
   -- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
   SELECT s_id, s_score FROM score WHERE c_id = 0004 AND s_score < 60 ORDER BY s_score DESC;
   
   -- 统计每门课程的学生选修人数(超过2人的课程才统计)
   要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
   SELECT c_id, COUNT(s_id) AS 选修人数 FROM score GROUP BY c_id HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC, c_id ASC;
   
   -- 查询两门以上不及格课程的同学的学号及其平均成绩
   SELECT s_id, AVG(s_score) AS 平均成绩 FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(c_id) > 2;
   

4. 汇总分析

   -- 查询学生的总成绩并进行排名
   SELECT s_id, SUM(s_score) AS 总成绩 FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC;
   
   -- 查询平均成绩大于60分的学生的学号和平均成绩
   SELECT s_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY s_id HAVING AVG(s_score) > 60;
   

3. 复杂查询

-- 查询所有课程成绩小于60分学生的学号、姓名
SELECT s_id, s_name FROM student WHERE s_id IN (SELECT s_id FROM score WHERE s_score < 60);

-- 查询没有学全所有课的学生的学号、姓名
SELECT s_id, s_name FROM student WHERE s_id IN (
SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) IN (
SELECT COUNT(c_id) FROM course));

-- 查询出只选修了两门课程的全部学生的学号和姓名
SELECT s_id, s_name FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) = 2);

-- 1990年出生的学生名单
SELECT s_name FROM student WHERE YEAR(s_birth) = 1990;

-- 查询各科成绩前两名的记录
(SELECT c_id, s_score FROM score WHERE s_id = '0001' GROUP BY c_id ORDER BY s_score DESC LIMIT 2)
UNION ALL
(SELECT c_id, s_score FROM score WHERE s_id = '0002' GROUP BY c_id ORDER BY s_score DESC LIMIT 2)
UNION ALL
(SELECT c_id, s_score FROM score WHERE s_id = '0003' GROUP BY c_id ORDER BY s_score DESC LIMIT 2);

-- 查询各学生的年龄（精确到月份）
SELECT s_id, TIMESTAMPDIFF(MONTH, s_birth, now()) / 12 FROM student;

4. 多表查询

TODO

参考：

1. https://zhuanlan.zhihu.com/p/150571462