软件测试面试真题 | 经典 SQL 查询

作者：测试人

2022-11-02
北京
本文字数：2950 字
阅读完需：约 10 分钟

获取更多面试真题

经典面试题

已知有如下 3 张表：
学⽣表：student (学号, 学⽣姓名, 出⽣年⽉, 性别)
成绩表：score (学号, 课程号, 成绩)
课程表：course (课程号, 课程名称, 教师姓名)
根据以上信息按照下⾯要求写出对应的 SQL 语句

考察点

分析数据表的关联关系
SQL 的编写能力

技术点

SQL 查询语法

创建学生表

学⽣表：student
学号：studentId，主键
学⽣姓名：studentName
出⽣年⽉：birthDate
性别：sex

CREATE TABLE `shcool`.`student` (  `studentId` VARCHAR(10) NOT NULL,  `studentName` VARCHAR(45) NULL,  `birthDate` DATE NULL,  `sex` VARCHAR(10) NULL,  PRIMARY KEY (`studentId`));insert into student(studentId, studentName, birthDate, sex) values("0001", "张三", "1989-01-01", "男");insert into student(studentId, studentName, birthDate, sex) values("0002", "李四", "1990-12-21", "⼥");insert into student(studentId, studentName, birthDate, sex) values("0003", "王五", "1991-12-21", "男");insert into student(studentId, studentName, birthDate, sex) values("0004", "赵六", "1990-05-20", "男");insert into student(studentId, studentName, birthDate, sex) values("0005", "冯七", "1991-08-11", "女");

复制代码

创建成绩表

成绩表：score
学号：studentId，主键
课程号：courseId，主键
成绩：score

CREATE TABLE `shcool`.`score` (  `studentId` INT NOT NULL,  `courseId` INT NOT NULL,  `score` FLOAT NULL,  PRIMARY KEY (`studentId`, `courseId`));insert into score(studentId, courseId, score) values("0001" , "0001" , 80);insert into score(studentId, courseId, score)values("0001" , "0002" , 90);insert into score(studentId, courseId, score)values("0001" , "0003" , 99);insert into score(studentId, courseId, score)values("0002" , "0001" , 58);insert into score(studentId, courseId, score)values("0002" , "0002" , 55);insert into score(studentId, courseId, score)values("0002" , "0003" , 80);insert into score(studentId, courseId, score)values("0003" , "0001" , 90);insert into score(studentId, courseId, score)values("0003" , "0002" , 80);insert into score(studentId, courseId, score)values("0003" , "0003" , 59);insert into score(studentId, courseId, score)values("0004" , "0002" , 49);insert into score(studentId, courseId, score)values("0005" , "0003" , 70);

复制代码

创建课程表

CREATE TABLE `shcool`.`course` (  `courseId` INT NOT NULL,  `courseName` VARCHAR(255) NULL,  `teacher` VARCHAR(255) NULL,  PRIMARY KEY (`courseId`));insert into course(courseId, courseName, teacher) values("0001" , "语⽂" , "王老师");insert into course(courseId, courseName, teacher) values("0002" , "数学" , "李老师");insert into course(courseId, courseName, teacher) values("0003" , "英语" , "赵老师");

复制代码

汇总分析

查询课程编号为 “0002” 的总成绩
查询选了课程编号为 “0002” 的学生人数

-- 查询课程编号为 "0002" 的总成绩SELECT     SUM(score)FROM    scoreWHERE    courseId = "0002";-- 查询选了课程编号为 "0002" 的学生人数SELECT     COUNT(DISTINCT studentId) AS 学⽣⼈数FROM    scoreWHERE    courseId = "0002";

复制代码

分组

查询各科成绩最高和最低分
查询每门课程被选修的学生数

-- 查询各科成绩最高和最低分SELECT     courseId, MAX(score) AS 最⾼分, MIN(score) AS 最低分FROM    scoreGROUP BY courseId;-- 查询每门课程被选修的学生数SELECT     courseId, COUNT(studentId)FROM    scoreGROUP BY courseId;

复制代码

分组结果的条件

查询平均成绩大于 60 分学生的学号
查询⾄少选修两门课程的学⽣学号

-- 查询平均成绩大于 60 分学生的学号SELECT     studentId, AVG(score)FROM    scoreGROUP BY studentIdHAVING AVG(score) > 60;-- 查询⾄少选修两门课程的学⽣学号SELECT     studentId, COUNT(courseId) AS 选修课程数⽬FROM    scoreGROUP BY studentIdHAVING COUNT(courseId) >= 2;

复制代码

复杂查询

查询所有有过不及格（低于 60 分）成绩的学生姓名（不重复）
查询 2 门以上（含 2 门）不及格（低于 60 分）的学生姓名
查询没有选过王老师的课的所有学生姓名
查询选过王老师的课并且及格的所有学生姓名
查询所有学⽣的学号、姓名、选课数、总成绩

-- 查询所有有过不及格（低于 60 分）成绩的学生姓名（不重复）-- 表连接SELECT DISTINCT    studentName 不及格学生姓名FROM    student st        LEFT JOIN    score sc ON st.studentId = sc.studentIdWHERE    score < 60;-- 子查询SELECT DISTINCT    studentName 不及格学生姓名FROM    studentWHERE    studentId IN (SELECT             studentId        FROM            score        WHERE            score < 60)-- 查询 2 门以上（含 2 门）不及格（低于 60 分）的学生姓名SELECT     studentName 姓名, COUNT(studentName) 不及格课程数FROM    student st        LEFT JOIN    score sc ON st.studentId = sc.studentIdWHERE    score < 60GROUP BY studentNameHAVING COUNT(studentName) >= 2;-- 查询没有选过王老师的课的所有学生姓名SELECT     studentId, studentNameFROM    studentWHERE    studentId NOT IN (SELECT             st.studentId        FROM            student st                INNER JOIN            score sc ON st.studentId = sc.studentId                INNER JOIN            course co ON sc.courseId = co.courseId        WHERE            teacher = "王老师");-- 查询选过王老师的课并且及格的所有学生姓名SELECT     *FROM    student st        INNER JOIN    score sc ON st.studentId = sc.studentId        INNER JOIN    course co ON sc.courseId = co.courseIdWHERE    teacher = "王老师" and score >= 60;-- 查询所有学⽣的学号、姓名、选课数、总成绩SELECT     sc.studentId,    studentName,    COUNT(sc.courseId) AS 选课数,    SUM(score) AS 总成绩FROM    student AS st        LEFT JOIN    score AS sc ON st.studentId = sc.studentIdGROUP BY st.studentId;

复制代码

答案总结

SQL 查询问题分析思路
查询的内容是什么
查询的内容从哪张表中查找
如果查询的内容，或者查询的条件分别在不同的表中，需要使用表连接或者子查询
确定查询的条件是什么
如果使用了聚合函数，则要对某个字段进行分组
是否需要对分组后的结果进行条件过滤
是否需要排序
是否需要限定输出数据的数量

SELECT <列名>FROM    <表名1>  [别名1]INNER/LEFT/RIGHT JOIN   <表名2>  [别名2]ON   别名1.外键列 = 别名2.主键列WHERE <查询条件表达式>GROUP BY <分组的列名>HAVING <分组后的查询条件表达式>ORDER BY <排序的列名> [ASC或者DESC]LIMIT [开始的行数], <查询记录的条数>```: