Mr. Cappuccino的第8杯咖啡——一份SQL操作的笔试题

本文链接：https://blog.csdn.net/sinat_41888963/article/details/115273945

一份SQL操作的笔试题

一些闲话

最近去面试的时候，做了一份SQL操作的笔试题，发现自己有许多的SQL知识都已经慢慢淡忘了，于是今天便拿着这份题目重新去练习了一下，希望能让自己重拾起那部分丢掉的知识哈哈哈，如果也有和我一样的“患者”，希望能够对你有所帮助~~

进入正题

这份题目一共有四张表：第一张表是学生表（student），第二张表是课程表（course），第三张表是成绩表（sc），第四张表是教师表（teacher）。下面给同样需要练习的老哥提供一下表结构和插入语句~~

学生表（student）

CREATE TABLE student(
s_id INT PRIMARY KEY COMMENT '学生编号',
s_name VARCHAR(20) COMMENT '姓名',
s_birthday datetime COMMENT '生日',
s_sex VARCHAR(1) COMMENT '性别'
);

INSERT INTO student VALUE(1001, '李华', '1981-03-27 20:11:14', '男');
INSERT INTO student VALUE(1002, '孙尚香', '1982-03-27 20:11:14', '女');
INSERT INTO student VALUE(1003, '赵飞燕', '1981-03-27 20:11:14', '女');
INSERT INTO student VALUE(1004, '袁君', '1983-03-27 20:11:14', '男');

课程表（course）

CREATE TABLE course(
c_id INT PRIMARY KEY COMMENT '课程编号',
c_name VARCHAR(20) COMMENT '课程名称',
t_id INT COMMENT '教师编号'
);

INSERT INTO course VALUE(2001, '企业管理', 3003);
INSERT INTO course VALUE(2002, '马克思', 3003);
INSERT INTO course VALUE(2003, 'UML', 3002);
INSERT INTO course VALUE(2004, '数据库', 3001);
INSERT INTO course VALUE(2005, '英语', 3001);

成绩表（sc）

CREATE TABLE sc(
s_id INT COMMENT '学生编号',
c_id INT COMMENT '课程编号',
score INT COMMENT '分数'
);

INSERT INTO sc VALUE(1001, 2001, 80);
INSERT INTO sc VALUE(1001, 2002, 60);
INSERT INTO sc VALUE(1001, 2003, 75);
INSERT INTO sc VALUE(1002, 2001, 85);
INSERT INTO sc VALUE(1002, 2002, 70);
INSERT INTO sc VALUE(1003, 2004, 100);
INSERT INTO sc VALUE(1003, 2001, 90);
INSERT INTO sc VALUE(1003, 2002, 55);
INSERT INTO sc VALUE(1004, 2002, 65);
INSERT INTO sc VALUE(1004, 2003, 60);

教师表（teacher）

CREATE TABLE teacher(
t_id INT PRIMARY KEY COMMENT '教师编号',
t_name VARCHAR(20) COMMENT '教师名称'
);

INSERT INTO teacher VALUE(3001, '李思钱');
INSERT INTO teacher VALUE(3002, '何以琛');
INSERT INTO teacher VALUE(3003, '叶平');

把表结构都创建好了之后，就可以开始练习下面的题目啦~~

查询’2001’课程比’2002’课程成绩高的所有学生的学号；

SELECT a.s_id, a.score '2001', b.score '2002' FROM 
(SELECT s_id, c_id, score FROM sc WHERE c_id = '2001') AS a LEFT JOIN
(SELECT s_id, c_id, score FROM sc WHERE c_id = '2002') AS b ON a.s_id = b.s_id
WHERE a.score > b.score

查询平均成绩大于60分的同学的学号和平均成绩；

SELECT s_id, AVG(score) avg_score FROM sc GROUP BY s_id HAVING avg_score > 60

查询所有同学的学生编号、姓名、选课数、总成绩;

SELECT sc.s_id, student.s_name, COUNT(*) count, SUM(score) sum_score FROM sc 
LEFT JOIN student ON sc.s_id = student.s_id GROUP BY s_id

查询姓“李”的老师的个数；

SELECT COUNT(*) count FROM teacher WHERE t_name LIKE '李%'

把成绩表中的“叶平”老师教的课的成绩都更改为此课程的平均成绩；

UPDATE sc INNER JOIN (
SELECT c_id ,AVG(score) avg_score FROM sc WHERE c_id IN 
(SELECT course.c_id FROM course LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE teacher.t_name = '叶平') 
GROUP BY c_id
) temp ON sc.c_id = temp.c_id
SET sc.score = temp.avg_score;

删除学习“叶平”老师课程的成绩表记录；

DELETE FROM sc WHERE c_id IN 
(SELECT course.c_id FROM course LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE teacher.t_name = '叶平');

按平均成绩从高到低显示所有学生的“数据库”，“企业管理”，“英语”三门课程的成绩，按如下形式显示：学生编号，数据库，企业管理，英语，有效课程数，有效平均分；

SELECT temp.s_id '学生编号', temp.s_score '数据库', temp.q_score '企业管理', temp.y_score '英语', temp.count '有效课程数', ROUND(temp.all_score / temp.count) '有效平均分' FROM (
SELECT student.s_id s_id, a.score s_score, b.score q_score, c.score y_score, 
(CASE WHEN a.score IS NULL THEN 0 ELSE 1 END) + (CASE WHEN b.score IS NULL THEN 0 ELSE 1 END) + (CASE WHEN c.score IS NULL THEN 0 ELSE 1 END) count,
IFNULL(a.score,0) + IFNULL(b.score,0) + IFNULL(c.score,0) all_score FROM student 
LEFT JOIN (SELECT sc.s_id, sc.score FROM sc LEFT JOIN course ON sc.c_id = course.c_id WHERE course.c_name = '数据库') a ON student.s_id = a.s_id
LEFT JOIN (SELECT sc.s_id, sc.score FROM sc LEFT JOIN course ON sc.c_id = course.c_id WHERE course.c_name = '企业管理') b ON student.s_id = b.s_id
LEFT JOIN (SELECT sc.s_id, sc.score FROM sc LEFT JOIN course ON sc.c_id = course.c_id WHERE course.c_name = '英语') c ON student.s_id = c.s_id
) temp ORDER BY ROUND(temp.all_score / temp.count) DESC

查询各科成绩最高和最低的分，以如下形式显示：课程编号，最高分，最低分；

SELECT course.c_id '课程编号', max_score '最高分', min_score '最低分' FROM course 
LEFT JOIN (SELECT c_id, MAX(score) max_score, MIN(score) min_score FROM sc GROUP BY c_id) temp 
ON course.c_id = temp.c_id

查询不同老师所教不同课程平均分从高到低显示；

SELECT teacher.t_id '教师编号', course.c_id '课程编号', temp.avg_score '平均分' FROM teacher 
LEFT JOIN course ON teacher.t_id = course.t_id 
LEFT JOIN (SELECT c_id, AVG(score) avg_score FROM sc GROUP BY c_id) temp ON course.c_id = temp.c_id 
ORDER BY temp.avg_score DESC

1981年出生的学生名单（注：student表中的s_birthday列的类型是datetime）；

SELECT * FROM student WHERE YEAR(s_birthday) = '1981'

做完这些题目，相信它一定能给你带来你想要的东西嘻嘻嘻~~如果有什么写得不对的地方，希望大家能够多多指导哟！

机会不会等你，错过以后可能不会再有。