简介:所用到的四个表格
粗略读过一遍《SQL基础教程》后,利用周末刷了一组经典的MySQL面试题,来和大家分享。主要是一些有关SQL查询的题目,也有几道涉及表的更新。有很多地方也许有更简单写法,请大神们指教~
题目中共有四个表格:
- student表
储存学生信息的表格,含有4个字段,分别是:Sid,Sname,Sage,Ssex;代表了学生的学号,姓名,年龄,性别
- sc表
储存学生成绩的表格,含有3个字段,分别是:Sid,Cid,score;代表了学生的学号,科目的代号,学生该科目的成绩
- course表
储存科目信息的表格,含有3个字段,分别是:Cid,Cname,Tid;代表了科目的代号,科目的名字,教授该科目的老师代号
**- teacher表**
储存老师信息的表格,含有2个字段,分别是:Tid,Tname;代表了老师的代号,老师的名字
50道题目及答案
有关查询的题目
(因为是练习题目,所以尽可能写了多种写法)
- 各科的报考人数
SELECT sc.Cid AS 科目代号, course.Cname AS 科目, COUNT(*) AS 报考人数 from sc
INNER JOIN course ON course.Cid = sc.Cid
GROUP BY sc.Cid;
-- 也可以使用子查询去写
SELECT DISTINCT A.Cid AS 科目代号,
(SELECT B.Cname FROM course B WHERE B.Cid = A.Cid) AS 科目,
(SELECT COUNT(*) FROM sc C WHERE C.Cid = A.Cid) AS 报考人数
from sc A;
- 每个学生的参与课程号
SELECT student.Sid, student.Sname, course.Cname FROM student
INNER JOIN sc ON sc.Sid = student.Sid
INNER JOIN course ON course.Cid = sc.Cid
ORDER BY student.Sid ASC;
- 各课程老师的名字
SELECT course.Cname AS 课程, teacher.Tname AS 授课老师 FROM course
INNER JOIN teacher ON teacher.Tid = course.Tid;
-- 也可以使用子查询去写
SELECT Cname AS 课程,
(SELECT Tname FROM teacher WHERE teacher.Tid = course.Tid) AS 授课老师
FROM course;
- 每个老师所教的课程名
-- 与第3题相同,只是改变两列的顺序
SELECT teacher.Tname AS 授课老师, course.Cname AS 课程 FROM teacher
INNER JOIN course ON course.Tid = teacher.Tid;
-- 也可以使用子查询去写
SELECT teacher.Tname AS 授课老师,
(SELECT Cname FROM course WHERE course.Tid = teacher.Tid) AS 课程
FROM teacher;
- 每个学生的最高分
SELECT A.Sid AS 学号, A.Sname AS 学生姓名,
(SELECT MAX(sc.score) FROM sc WHERE sc.Sid = A.Sid) AS 最高分数,
(SELECT course.Cname FROM course
INNER JOIN sc B ON B.Cid = course.Cid
WHERE B.Sid = A.Sid AND B.score = 最高分数) AS 最高成绩科目
FROM student A;
- 每科中的最高分是谁
SELECT Cname AS 科目,
(SELECT MAX(score) FROM sc WHERE sc.Cid = course.Cid) AS 最高分,
(SELECT Sname FROM student
INNER JOIN sc ON sc.Sid = student.Sid
WHERE sc.score = 最高分) AS 最高分同学
FROM course;
-- 不想要最高分列的话,可以将可以查询到每科`最高分`的代码写在`最高分同学`的子查询中
SELECT Cname AS 科目,
(SELECT Sname FROM student
INNER JOIN sc ON sc.Sid = student.Sid
WHERE sc.score = (SELECT MAX(score) FROM sc WHERE sc.Cid = course.Cid)) AS 最高分同学
FROM course;
- 每科中的最高分是谁
-- 将题目理解为每位同学所选修的所有科目中哪个科目成绩最高
SELECT Sid AS 学号, Sname AS 学生姓名,
(SELECT MAX(score) FROM sc WHERE Sid = student.Sid) AS 该学生最高分,
(SELECT Cname FROM course
LEFT JOIN sc on sc.Cid = course.Cid
WHERE Sid = student.Sid AND score = 该学生最高分) AS 最高分数科目
FROM student;
-- 不想要最高分列的话,可以将可以查询到每科`最高分`的代码写在`最高分同学`的子查询中
SELECT Sid AS 学号, Sname AS 学生姓名,
(SELECT Cname FROM course
LEFT JOIN sc SC1 on SC1.Cid = course.Cid
WHERE SC1.Sid = student.Sid
AND
SC1.score = (SELECT MAX(SC2.score) FROM sc SC2 WHERE SC2.Sid = student.Sid))
AS 最高分数科目
FROM student;
- 同时学了数学,语文的学生
SELECT student.Sid AS 学号, student.Sname AS 学生姓名 FROM student
WHERE student.Sid IN (SELECT sc.Sid FROM sc
LEFT JOIN course
ON course.Cid = sc.Cid
WHERE course.Cname = "数学")
AND
student.Sid IN (SELECT sc.Sid FROM sc
LEFT JOIN course
ON course.Cid = sc.Cid
WHERE course.Cname = "语文");
- 每个课程的报考人数
SELECT Cid AS 科目代号, Cname AS 科目,
(SELECT COUNT(sc.Sid)
FROM sc
WHERE sc.Cid = course.Cid) AS 报考人数
FROM course;
- 所有课程都及格的学生
-- 理解题目:所有课程都及格,也就是该学生的最低分大于等于60
SELECT Sid, Sname FROM student
WHERE (SELECT MIN(score) FROM sc WHERE sc.Sid = student.Sid) > 60;
- 总分大于260的学生
SELECT Sid, Sname FROM student
WHERE (SELECT SUM(score) FROM sc WHERE sc.Sid = student.Sid) > 260;
- 及格人数多于不及格人数的科目
SELECT course.* FROM course
WHERE
(SELECT COUNT(SC1.Sid) FROM sc SC1 WHERE SC1.Cid = course.Cid AND SC1.score >= 60)
>
(SELECT COUNT(SC2.Sid) FROM sc SC2 WHERE SC2.Cid = course.Cid AND SC2.score < 60);
- 各课的及格率
SELECT * ,
((SELECT COUNT(sc.score) FROM sc WHERE sc.Cid = course.Cid AND sc.score >= 60) /
(SELECT COUNT(sc.score) FROM sc WHERE sc.Cid = course.Cid)) AS 及格率
FROM course;
- 各老师的及格数
-- 将题目理解为 各老师所授科目中及格的人数
SELECT teacher.Tname AS 老师, course.Cname AS 科目,
(SELECT COUNT(sc.score) FROM sc WHERE sc.Cid = course.Cid AND sc.score >= 60) AS 及格人数
FROM teacher
LEFT JOIN course ON course.Tid = teacher.Tid;
- 各学生的最高分,最低分,平均分
SELECT student.Sid AS 学号, student.Sname AS 学生姓名,
(SELECT MIN(SC1.score) FROM sc SC1 WHERE SC1.Sid = student.Sid) AS 最低分,
(SELECT AVG(SC2.score) FROM sc SC2 WHERE SC2.Sid = student.Sid) AS 平均分
FROM student;
- 最高分90分的学生
-- 将题目理解为:最高分为90分的学生
SELECT student.Sid AS 学号, student.Sname AS 学生姓名 FROM student
LEFT JOIN sc SC1 ON SC1.Sid = student.Sid
WHERE (SELECT MAX(SC2.score) FROM sc SC2 WHERE SC2.Sid = student.Sid) = 90;
- 语文不及格的学生
SELECT DISTINCT student.Sid AS 学号, student.Sname AS 学生姓名 FROM student
LEFT JOIN sc SC1 ON SC1.Sid = student.Sid
WHERE (SELECT SC2.score FROM sc SC2
LEFT JOIN course ON course.Cid = SC2.Cid
WHERE SC2.Sid = student.Sid AND
course.Cname = "语文") < 60;
- 找出人数最多的科目
SELECT course.Cid AS 科目代号, course.Cname AS 科目 FROM course
LEFT JOIN sc SC1 ON SC1.Cid = course.Cid
GROUP BY course.Cid
HAVING COUNT(SC1.Sid) >= ALL
(SELECT COUNT(SC2.Sid) FROM sc SC2
GROUP BY SC2.Cid);
- 各学生按总分进行排序
SELECT Sid AS 学号, Sname AS 学生姓名,
(SELECT SUM(score) FROM sc WHERE sc.Sid = student.Sid) AS 总分
FROM student
ORDER BY 总分 DESC;
- 找到未教课的老师
SELECT teacher.Tid AS 老师代号, teacher.Tname AS 老师 FROM teacher
WHERE teacher.Tid NOT IN (SELECT course.Tid FROM course);
- 查询“3”课程比“2”课程成绩高的所有学生的学号
SELECT Sid FROM student
WHERE
(SELECT SC1.score FROM sc SC1 WHERE SC1.Sid = student.Sid AND SC1.Cid = "3")
>
(SELECT SC2.score FROM sc SC2 WHERE SC2.Sid = student.Sid AND SC2.Cid = "2");
- 查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名
SELECT Sid, Sname FROM student
WHERE
(SELECT SC1.score FROM sc SC1 WHERE SC1.Sid = student.Sid AND SC1.Cid = "2")
<
(SELECT SC2.score FROM sc SC2 WHERE SC2.Sid = student.Sid AND SC2.Cid = "1");
- 查询平均成绩大于60分的同学的学号和平均成绩
SELECT SC1.Sid, AVG(SC1.score) FROM sc SC1
WHERE (SELECT AVG(SC2.score) FROM sc SC2 WHERE SC2.Sid = SC1.Sid) > 60
GROUP BY SC1.Sid;
- 查询所有同学的学号、姓名、选课数、总成绩
SELECT student.Sid AS 学号, student.Sname AS 姓名,
(SELECT COUNT(Cid) FROM sc WHERE sc.Sid = student.Sid) AS 选课数,
(SELECT SUM(score) FROM sc WHERE sc.Sid = student.Sid) AS 总成绩
FROM student;
- 查询姓“李”的老师的个数
SELECT COUNT(Tid) FROM teacher
WHERE Tname LIKE "李%";
- 查询没学过“叶平”老师课的同学的学号、姓名
SELECT Sid AS 学号, Sname AS 姓名 FROM student
WHERE Sid NOT IN
(SELECT sc.Sid FROM sc
LEFT JOIN course ON course.Cid = sc.Cid
LEFT JOIN teacher ON teacher.Tid = course.Tid
WHERE teacher.Tname = "叶平");
- 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
SELECT student.Sid AS 学号, student.Sname AS 姓名 from student
LEFT JOIN sc SC1 on SC1.Sid = student.Sid
LEFT JOIN sc SC2 on SC2.Sid = SC1.Sid
WHERE SC1.Cid = '1' AND SC2.Cid = '2';
-- 也可以使用子查询去写
SELECT Sid AS 学号, Sname AS 姓名 from student
WHERE Sid IN (SELECT STU.Sid FROM student STU
LEFT JOIN sc SC
ON SC.Sid = STU.Sid
WHERE SC.Cid = "1")
AND
Sid IN (SELECT STU.Sid FROM student STU
LEFT JOIN sc SC ON
SC.Sid = STU.Sid
WHERE SC.Cid = "2")
- 查询学过“叶平”老师所教的所有课的同学的学号、姓名
SELECT student.Sid AS 学号, Sname AS 姓名 from student
LEFT JOIN sc ON sc.Sid = student.Sid
LEFT JOIN course ON course.Cid = sc.Cid
LEFT JOIN teacher ON teacher.Tid = course.Tid
WHERE teacher.Tname = "叶平";
- 查询有课程成绩小于60分的同学的学号、姓名;
SELECT DISTINCT student.Sid, student.Sname FROM student
LEFT JOIN sc ON sc.Sid = student.Sid
WHERE sc.score < 60;
-- 也可以使用子查询去写。只要同学的最低分小于60,则证明该同学最少有一科小于60
SELECT student.Sid AS 学号, Sname AS 姓名 FROM student
WHERE (SELECT MIN(sc.score) FROM sc WHERE sc.Sid = student.Sid) < 60;
- 查询没有学全所有课的同学的学号、姓名
-- 在这里不考虑补考,即一个同学同一科目考了几次的情况
SELECT student.Sid AS 学号, student.Sname AS 姓名 FROM student
WHERE
(SELECT COUNT(sc.Sid) FROM sc WHERE sc.Sid = student.Sid) <>
(SELECT COUNT(Cid) FROM course);
- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
-- 利用笛卡尔积
SELECT DISTINCT sc.Sid AS 学号, student.Sname AS 学生姓名
FROM sc, student,
(SELECT Cid FROM sc WHERE Sid = '1') A
WHERE
A.Cid = sc.Cid AND
sc.Sid = student.Sid AND
sc.Sid <> '1';
- 找出和学号为2的同学所选科目数一样的同学
SELECT DISTINCT SC1.Sid AS 学号, student.Sname AS 学生姓名 FROM sc SC1
LEFT JOIN student ON student.Sid = SC1.Sid
WHERE
(SELECT COUNT(SC2.Cid) FROM sc SC2 WHERE SC2.Sid = SC1.Sid)
=
((SELECT COUNT(SC3.Cid) FROM sc SC3 WHERE SC3.Sid = "2"))
AND
SC1.Sid <> "2";
- 查询和“3”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT DISTINCT A.Sid AS 学号,
A.Sname AS 学生姓名
FROM
(SELECT DISTINCT SC1.Sid, student.Sname, GROUP_CONCAT(SC1.Cid ORDER BY SC1.Cid) AS 所选课程
FROM sc SC1
LEFT JOIN student ON student.Sid = SC1.Sid
GROUP BY SC1.Sid) A
WHERE A.Sid <> "3" AND
A.所选课程 = (SELECT DISTINCT GROUP_CONCAT(SC2.Cid ORDER BY SC2.Cid)
FROM sc SC2
LEFT JOIN student ON student.Sid = SC2.Sid
WHERE SC2.Sid = "3");
- 按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示:
学生ID, 语文, 数学, 英语, 有效课程数, 有效平均分
SELECT Sid AS 学生iD,
(IF(course.Cname = "语文",sc.score, NULL)) AS 语文,
(IF(course.Cname = "数学",sc.score, NULL)) AS 数学,
(IF(course.Cname = "英语",sc.score, NULL)) AS 英语,
COUNT(sc.score) AS 有效课程数,
AVG(sc.score) AS 有效平均分
FROM sc
LEFT JOIN course ON course.Cid = sc.Cid
WHERE (course.Cname IN ("语文", "数学", "英语"))
GROUP BY Sid
ORDER BY 有效平均分 ASC;
- 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT DISTINCT SC1.Cid AS 课程ID,
(SELECT MAX(SC2.score) FROM sc SC2 WHERE SC2.Cid = SC1.Cid) AS 最高分,
(SELECT MIN(SC2.score) FROM sc SC2 WHERE SC2.Cid = SC1.Cid) AS 最低分
FROM sc SC1
ORDER BY 课程ID ASC;
- 按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT sc.Cid AS 科目代号,
course.Cname AS 科目名,
AVG(sc.score) AS 平均成绩,
(SUM(IF(sc.score >= 60,1,0)) / COUNT(sc.Sid)) AS 及格率
FROM sc
LEFT JOIN course ON course.Cid = sc.Cid
GROUP BY sc.Cid
ORDER BY 平均成绩 ASC, 及格率 DESC;
- 查询如下课程平均成绩和及格率的百分数
(备注:需要在1行内显示:
语文平均分,语文及格比,数学平均分,数学及格比,英语平均分,英语及格比)
SELECT
((CASE WHEN course.Cname = "语文" THEN sc.score ELSE 0 END)/SUM(CASE WHEN course.Cname = "语文" THEN 1 ELSE 0 END)) AS 语文平均分,
(100 * SUM(CASE WHEN course.Cname = "语文" AND sc.score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course.Cname = "语文" THEN 1 ELSE 0 END)) AS 语文及格比,
((CASE WHEN course.Cname = "数学" THEN sc.score ELSE 0 END)/SUM(CASE WHEN course.Cname = "数学" THEN 1 ELSE 0 END)) AS 数学平均分,
(100 * SUM(CASE WHEN course.Cname = "数学" AND sc.score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course.Cname = "数学" THEN 1 ELSE 0 END)) AS 数学及格比,
((CASE WHEN course.Cname = "英语" THEN sc.score ELSE 0 END)/SUM(CASE WHEN course.Cname = "英语" THEN 1 ELSE 0 END)) AS 英语平均分,
(100 * SUM(CASE WHEN course.Cname = "英语" AND sc.score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course.Cname = "英语" THEN 1 ELSE 0 END)) AS 英语及格比
FROM sc
LEFT JOIN course ON course.Cid = sc.Cid;
- 查询不同老师所教不同课程平均分从高到低显示( Cid,Cname,Tid,Tname,平均分)
SELECT course.Cid, course.Cname, course.Tid, teacher.Tname, AVG(score) AS 平均分 FROM course
INNER JOIN teacher ON teacher.Tid = course.Tid
INNER JOIN sc ON sc.Cid = course.Cid
GROUP BY teacher.Tid, course.Cid
ORDER BY 平均分 DESC;
- 排名第 3 名到第 6 名的学生成绩单 (Sid,Sname,语文,数学,英语,物理,平均成绩 )
-- 使用子查询
SELECT student.Sid, student.Sname,
(SELECT SC2.score FROM sc SC2 WHERE SC2.Sid = student.Sid AND SC2.Cid = (SELECT Cid FROM course WHERE Cname = "语文")) AS 语文,
(SELECT SC3.score FROM sc SC3 WHERE SC3.Sid = student.Sid AND SC3.Cid = (SELECT Cid FROM course WHERE Cname = "数学")) AS 数学,
(SELECT SC4.score FROM sc SC4 WHERE SC4.Sid = student.Sid AND SC4.Cid = (SELECT Cid FROM course WHERE Cname = "英语")) AS 英语,
(SELECT SC5.score FROM sc SC5 WHERE SC5.Sid = student.Sid AND SC5.Cid = (SELECT Cid FROM course WHERE Cname = "物理")) AS 物理,
AVG(sc.score) AS 平均成绩
FROM sc INNER JOIN student ON student.Sid = sc.Sid
GROUP BY student.Sid
ORDER BY 平均成绩 DESC LIMIT 2, 3;
-- 使用CASE表达式
SELECT student.Sid, student.Sname,
SUM((CASE WHEN sc.Cid = (SELECT Cid FROM course WHERE Cname = "语文") THEN sc.score ELSE NULL END)) AS 语文,
SUM((CASE WHEN sc.Cid = (SELECT Cid FROM course WHERE Cname = "数学") THEN sc.score ELSE NULL END)) AS 数学,
SUM((CASE WHEN sc.Cid = (SELECT Cid FROM course WHERE Cname = "英语") THEN sc.score ELSE NULL END)) AS 英语,
SUM((CASE WHEN sc.Cid = (SELECT Cid FROM course WHERE Cname = "物理") THEN sc.score ELSE NULL END)) AS 物理,
AVG(sc.score) AS 平均成绩
FROM sc INNER JOIN student ON student.Sid = sc.Sid
GROUP BY student.Sid
ORDER BY 平均成绩 DESC LIMIT 2, 3;
- 统计列印各科成绩,各分数段人数:Cid,Cname,[100-85],[85-70],[70-60],[ <60]
-- 使用子查询
SELECT course.Cid, course.Cname,
(SELECT COUNT(SC2.Sid) FROM sc SC2 WHERE SC2.Cid = course.Cid AND SC2.score BETWEEN 85 AND 100) AS `[100-85]`,
(SELECT COUNT(SC3.Sid) FROM sc SC3 WHERE SC3.Cid = course.Cid AND SC3.score BETWEEN 70 AND 85) AS `[85-70]`,
(SELECT COUNT(SC4.Sid) FROM sc SC4 WHERE SC4.Cid = course.Cid AND SC4.score BETWEEN 60 AND 70) AS `[70-60]`,
(SELECT COUNT(SC5.Sid) FROM sc SC5 WHERE SC5.Cid = course.Cid AND SC5.score < 60) AS `[ <60]`
FROM course
LEFT JOIN sc ON sc.Cid = course.Cid
GROUP BY course.Cid;
-- 使用CASE表达式
SELECT course.Cid, course.Cname,
SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS `[100-85]`,
SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS `[85-70]`,
SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS `[70-60]`,
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS `[ <60]`
FROM course
LEFT JOIN sc ON sc.Cid = course.Cid
GROUP BY course.Cid;
- 学生平均成绩及其名次;(Sid,sName,平均分,排名)
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
涉及表的更新的题目
- 所有课程都及格的学生
- 所有课程都及格的学生
- 所有课程都及格的学生
最后说明
表格和题目均来源自网络。一些题目表达的不是很清晰,我在做题的时候只是按照自己的理解解题。刚刚接触MySQL,很多地方写的很幼稚,欢迎大神们指正~
-
Download Data
- 网盘链接: Author
- Shanshan.Yan