面试题sql还是mysql_MySQL常考的面试题「复杂SQL编写」

最新推荐文章于 2024-04-07 04:00:00 发布

欧明新

最新推荐文章于 2024-04-07 04:00:00 发布

阅读量262

点赞数 1

文章标签：面试题sql还是mysql

本文链接：https://blog.csdn.net/weixin_32998893/article/details/113333153

版权

技术面试中，问到MySQL时，学生表查询SQL语句，肯定是被问得最多的。

如果你天天玩表，对你来说应该会是小菜一碟。

但是还是有很多职场老将在这里折戟沉沙，甚是可惜！

今天，臻叔就带大家一起盘点一下，学生表到底都会问什么！？

「高能预警」：以下列举的问题，都有答案，亲手验证过正确性

文章很长，建议收藏，面试前可以拿出来看一看

建议先看题目，自己练习，不会的再看答案

先来看看题目

给定四个表:student(学生表)

teacher(老师表)

course(课程表)

sc(成绩表)

根据题目要求，写出SQL语句。

「问题列表」

1、查询每个学生的学号、姓名和每门课的成绩

2、查询都学过2号同学(sid=2)学习过的课程的同学的学号

3、查询“语文(cid=1)”课程比“数学(cid=2)”课程成绩高的所有学生的学号；

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

5、查询所有同学的学号、姓名、选课数、总成绩；

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

7、查询没学过“叶平”老师课的同学的学号、姓名；

8、查询学过“语文(cid=1)”并且也学过“数学(cid=2)”课程的同学的学号、姓名；

9、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

10、查询课程编号“数学(cid=2)”的成绩比课程编号“语文(cid=1)”课程低的所有同学的学号、姓名；

11、查询所有课程成绩小于60分的同学的学号、姓名；

12、查询没有学全所有课的同学的学号、姓名；

13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩，按如下形式显示：学生

ID,语文,数学,英语,有效课程数,有效平均分

14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

15、查询男生、女生人数, 以如下形式显示：男生人数，女生人数

16、查询课程名称为“数学”，且分数低于60的学生姓名和分数

17、查询两门以上不及格课程的同学的学号及其平均成绩；

18、检索“cid=4”课程分数小于60，按分数降序排列的同学学号

「表结构以及部分数据如下图展示」

student表

teacher表

course表

sc表

面试题详解

1、查询每个学生的学号、姓名和每门课的成绩；

(1)利用隐式内联，只输出两表中都有的字段

SQL语句：

SELECT

student.sid AS 学生ID,

student.sname AS 学生姓名,

(SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称,

sc.score AS 成绩

FROM student,sc

WHERE student.sid = sc.sid

查询结果：

(2)利用左连接，假如左表有，右表没有，则按左表为准，右表缺省字段置为NULL

SQL语句：

SELECT

student.sid AS 学生ID,

student.sname AS 学生姓名,

(SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称,

sc.score AS 成绩

FROM student

LEFT JOIN sc

ON student.sid = sc.sid

因为 student 表和 sc 表没有冗余字段，所以(1)和(2)两种查询方法结果一致。

2、查询都学过2号同学(sid=2)学习过的课程的同学的学号

SQL语句：

SELECT sid

FROM sc

WHERE cid IN (SELECT cid FROM sc WHERE sid=2)

GROUP BY sid

HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=2)

查询结果：

3、查询“语文(cid=1)”课程比“数学(cid=2)”课程成绩高的所有学生的学号；

SQL语句：

SELECT a.sid

FROM

(SELECT sid, score FROM sc WHERE cid=1) a,

(SELECT sid, score FROM sc WHERE cid=2) b

WHERE a.sid=b.sid AND a.score > b.score;

查询结果：

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

SQL语句：

SELECT sid, AVG(score)

FROM `sc`

GROUP BY sid

HAVING AVG(score) > 60;

查询结果：

5、查询所有同学的学号、姓名、选课数、总成绩；

SQL：

SELECT a.sid, a.sname, COUNT(a.cid), SUM(a.score)

FROM

(SELECT student.sid, student.sname, sc.cid, sc.score FROM student JOIN sc WHERE student.sid=sc.sid) a

GROUP BY sid;

查询结果：

6、查询姓“周”的老师的个数；

SQL：

SELECT COUNT(*) FROM `teacher` WHERE tname LIKE '周%';

查询结果：

7、查询没学过“叶平”老师课的同学的学号、姓名；

SQL：

# 我自己写的

SELECT student.sid, student.sname

FROM student

WHERE student.sid NOT IN

(

SELECT sc.sid

FROM sc

WHERE cid =

(

SELECT course.cid

FROM course

JOIN teacher

WHERE course.tid=teacher.tid AND teacher.tname='叶平'

)

);

# 参考答案

SELECT student.sid,student.sname

FROM student

WHERE sid NOT IN

(

SELECT DISTINCT(sc.sid)

FROM sc, course, teacher

WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'

);

查询结果：

8、查询学过“语文(cid=1)”并且也学过“数学(cid=2)”课程的同学的学号、姓名；

SQL：

SELECT student.sid,student.sname

FROM student, sc

WHERE student.sid=sc.sid AND sc.cid=1

AND

EXISTS

( SELECT * FROM sc AS sc_2 WHERE sc_2.sid=sc.sid AND sc_2.cid=2);

查询结果：

9、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SQL：

SELECT student.sid,student.sname

FROM student

WHERE sid IN

(

SELECT sid

FROM sc ,course ,teacher

WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'

GROUP BY sid

HAVING COUNT(sc.cid)=

(

SELECT COUNT(cid)

FROM course,teacher

WHERE teacher.tid=course.tid AND teacher.tname='叶平'

)

);

查询结果：

10、查询课程编号“数学(cid=2)”的成绩比课程编号“语文(cid=1)”课程低的所有同学的学号、姓名；

SQL：

SELECT sid,sname

FROM student

WHERE sid=

(

SELECT a.sid

FROM

(SELECT sid, score FROM sc WHERE cid=1) a,

(SELECT sid, score FROM sc WHERE cid=2) b

WHERE a.sid=b.sid AND a.score > b.score

)

查询结果：

「11」、查询所有课程成绩小于60分的同学的学号、姓名；

SQL：

SELECT sid,sname

FROM student

WHERE sid NOT IN

(

SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60

);

查询结果：无

12、查询没有学全所有课的同学的学号、姓名；

SQL：

SELECT student.sid, student.sname

FROM student,sc

WHERE student.sid=sc.sid

GROUP BY sid

HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)

查询结果：

13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分

SQL：

SELECT sid AS 学生ID,

(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=1) AS 语文,

(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=2) AS 数学,

(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=3) AS 英语,

COUNT(*) AS 有效课程数,

AVG(score)

FROM sc AS sc_2

GROUP BY sid

ORDER BY AVG(sc_2.score)

查询结果：

14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SQL：

SELECT cid AS 课程ID, MAX(score) AS 最高分, MIN(score) AS 最低分

FROM sc

GROUP BY cid

查询结果：

15、查询男生、女生人数：以如下形式显示：男生人数，女生人数

SQL：

SELECT

(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='男') AS 男生人数,

(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='女') AS 女生人数;

查询结果：

16、查询课程名称为“数学”，且分数低于60的学生姓名和分数

SQL：

SELECT student.sid, student.sname

FROM student,sc

WHERE student.sid=sc.sid AND cid=(SELECT cid FROM course WHERE cname='数学') AND score > 60

查询结果：

17、查询两门及两门以上不及格课程的同学的学号及其平均成绩

SQL：

SELECT sid, AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*)>1) GROUP BY sid;

查询结果：

18、检索“cid=4”课程分数小于60，按分数降序排列的同学学号

SQL：

SELECT sid FROM sc WHERE cid=4 AND score < 60 ORDER BY score DESC

查询结果：

欧明新

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
面试题sql还是mysql_MySQL常考的面试题「复杂SQL编写」

技术面试中，问到MySQL时，学生表查询SQL语句，肯定是被问得最多的。如果你天天玩表，对你来说应该会是小菜一碟。但是还是有很多职场老将在这里折戟沉沙，甚是可惜！今天，臻叔就带大家一起盘点一下，学生表到底都会问什么！？「高能预警」：以下列举的问题，都有答案，亲手验证过正确性文章很长，建议收藏，面试前可以拿出来看一看建议先看题目，自己练习，不会的再看答案先来看看题目给定四个表:student(学生表...
复制链接

扫一扫

面试题sql还是mysql_MySQL常考的面试题 「复杂SQL编写」

“相关推荐”对你有帮助么？

面试题sql还是mysql_MySQL常考的面试题「复杂SQL编写」