常见的sql面试题

本章是SQL面试题的汇总,之后还会不断更新,文章的思维导图如下:

1.SQL初级查询
单表查询

-- 查询学生表中有几名学生的信息.显示学号
select distinct 学号  from student;

查询条件

-- 查询学生表中性别为女的学生所有信息
SELECT * FROM student WHERE 性别='女';

结果排序

-- 查询成绩表中学生所有成绩，按照课程号升序，成绩降序排列
SELECT * FROM score 
ORDER BY 课程号 ASC,成绩 DESC;

限定数量

-- 查询成绩表中学生所有成绩，按照课程号升序，成绩降序排列,显示前5条数据
SELECT * FROM score 
ORDER BY 课程号 ASC,成绩 DESC
LIMIT 5;

-- 查询成绩表中学生所有成绩，返回成绩第二学生成绩信息
SELECT * FROM score
ORDER BY 成绩 DESC
LIMIT 1 OFFSET 1;

case表达式

-- 对各课程的成绩按照[100-85（含）]、[85-70（含）]、[70-60（含）]、[60以下]分段，并统计各分段数人数和课程名
/*
定义条件：成绩在100-85（含）为[100-85（含）]段，在85-70（含）为[85-70（含）]段，在70-60（含）为[70-60（含）]段，小于60就是[60以下]分段；
把成绩表score和课程表course交叉联结（右联结），显示课程号、课程名、和分数段；
按课程号、课程名进行分组，对分组结果的人数按照上上述的逻辑条件计数；
*/
SELECT a.课程号,b.课程名称,
SUM(CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END)AS '100-80(含)',
SUM(CASE WHEN 成绩 BETWEEN 70 AND 85 THEN 1 ELSE 0 END)AS '85-70(含)',
SUM(CASE WHEN 成绩 BETWEEN 60 AND 70 THEN 1 ELSE 0 END)AS '70-60(含)',
SUM(CASE WHEN 成绩 <60 THEN 1 ELSE 0 END)AS '60以下'
FROM score AS a RIGHT JOIN course AS b
ON a.课程号=b.课程号
GROUP BY a.课程号,b.课程名称;

分组汇总

-- 对每门课程进行分组，计算每门课程有几名学生
SELECT 课程号,COUNT(学号) FROM score GROUP BY 课程号

SQL高级查询
多表联结

# 查询出每门课程的及格人数和不及格人数
/*
定义条件：成绩>=60分及格，成绩<60分不及格；
按课程号进行分组，对分组结果的人数按照上一步的逻辑条件计数；
*/
SELECT 课程号,
SUM(CASE WHEN 成绩>=60 THEN 1
ELSE 0
END)AS 及格人数,
SUM(CASE WHEN 成绩<60 THEN 1
ELSE 0
END)AS 不及格人数
FROM score
GROUP BY 课程号;

集合运算

-- 把学生表student和成绩表score交叉联结，显示所有成绩和学生
/*① 使用左联结 left join取绿色部分面积；
②使用右联结right union 条件为左列名为null的部分面积；
③ 使用union all 相加*/
SELECT a.学号,a.姓名,b.课程号,b.成绩 
FROM student as a LEFT JOIN score as b 
ON a.学号=b.学号
UNION ALL
SELECT a.学号,a.姓名,b.课程号,b.成绩 
FROM student as a RIGHT JOIN score as b 
ON a.学号=b.学号 
WHERE a.学号 IS NULL;

子查询

-- 每一科成绩都大于该科平均成绩学生的学号和成绩
/*
大白话：对每一科课程分组，对分组结果计算平均成绩；对每一科对应的学生的成绩比较，大于该科平均成绩的便符合条件
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号;
SELECT 学号,课程号,成绩 FROM score WHERE 成绩> <关联子查询>;
*/
SELECT 学号,课程号,成绩 FROM score as s1 WHERE 成绩 > 
(SELECT AVG(成绩) FROM score as s2 WHERE s1.课程号=s2.课程号 GROUP BY 课程号);

-- 查询大于平均成绩学生的学号和成绩
/*大白话：得出平均成绩；成绩大于平均成绩就符合条件，对符合条件的学号和成绩汇总；
SELECT AVG(成绩) FROM score;
SELECT 学号,成绩 FROM score WHERE 成绩 > <标量子查询>;
*/
SELECT 学号,成绩 FROM score WHERE 成绩 > (SELECT AVG(成绩) FROM score);

EXISTS运算符用于指定子查询以测试行的存在.EXISTS运算符在找到行后立即终止查询处理，因此，可以利用EXISTS运算符的此功能来提高查询性能。

-- 查找所有选修了0001号课程的课程的学生，并显示他们的姓名
SELECT 姓名 FROM student WHERE EXISTS(SELECT * FROM score WHERE 学号=student.学号 AND 课程号='0001');

窗口函数
查找每个班级成绩最高的2个学生成绩数据
拆分两步：

1.先按班级分组，并按照成绩降序排列，查看各班成绩排名：

/*
先按班级分组，并按照成绩降序排列
SELECT *, row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num  FROM score_new;
*/
SELECT *, 
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num  
FROM score_new;

2.只要我们筛选row_num值<=2即可，我们可以用子查询：

SELECT * 
FROM
(SELECT *, 
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num  
FROM score_new) AS a
WHERE row_num <=2;

3.设计与开发
创建视图

-- 创建视图
CREATE VIEW 按性别汇总(性别,人数) AS SELECT 性别,COUNT(*) FROM student GROUP BY 性别;

2.牛客网SQL实战题目练习
1.查找最晚入职员工的所有信息
【知识点】关联子查询，两次查询都要在employees中获取数据，通过入职时间和最大入职时间是否一致来寻找符合条件的员工所有信息；

--思路:直接使用聚合函数max()来做判断
select *
from employees
where hire_date >=(select max(hire_date) from employees);

2.查找入职员工时间排名倒数第三的员工所有信息
【知识点】关联子查询，同样两次查询（查员工信息、查时间）都要在employees中获取数据；

注意：对时间排序取倒数第三，需要对时间使用distinct去掉重复值；倒数第三需要跳过2条数据，读取1条数据；我们使用limit y offset x;也可以使用limit x,y 跳过x条数据读取y条数据；

--这里考察的是limit函数的用法
/*
① select * from table limit 2,1;                

//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据



② select * from table limit 2 offset 1;     

//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条
*/
select *
from employees
where hire_date = (select hire_date from employees order by hire_date desc limit 2,1)

3.查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
【知识点】多表查询（内联结）

--这里有个小坑,如果把dept_manager当做主表来写,就会不通过
select s.*,d.dept_no
from salaries s 
inner join dept_manager d
on s.emp_no = d.emp_no
where d.to_date = '9999-01-01' and s.to_date='9999-01-01';

4,查找所有已经分配部门的员工的last_name和first_name以及dept_no
【知识点】多表查询（内联结）

select e.last_name,e.first_name,d.dept_no
from employees e
inner join dept_emp d
on e.emp_no = d.emp_no
where d.dept_no is not null;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

【知识点】多表查询（左联结）

select e.last_name,e.first_name,d.dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no