Sql经典50题
建表
-
学生表
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) );
-
成绩表
CREATE TABLE score ( s_id VARCHAR(30), c_id VARCHAR(30), s_score VARCHAR(3), PRIMARY KEY(s_id, c_id) );
-
课程表
CREATE TABLE course ( c_id VARCHAR(30), c_name VARCHAR(30) NOT NULL, t_id VARCHAR(30) NOT NULL, PRIMARY KEY(c_id) );
-
教师表
CREATE TABLE teacher ( t_id VARCHAR(30), t_name VARCHAR(30) NOT NULL, PRIMARY KEY(t_id) );
插入数据
-
学生表
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‘ , ‘男‘);
-
成绩表
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);
-
课程表
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‘);
-
教师表
-- 教师表:添加数据 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. 汇总分析
-
汇总分析
-- 查询课程编号为“0002”的总成绩 SELECT SUM(s_score) FROM score WHERE c_id = ‘0002‘; -- 查询选了课程的学生数 SELECT COUNT(DISTINCT(s_id)) AS 学生人数 FROM score;
-
分组
-- 查询各科成绩最高和最低的分 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;
-
分组结果的条件
-- 查询平均成绩大于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;
-
汇总分析
-- 查询学生的总成绩并进行排名 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. 复杂查询
TODO
参考: