前言

在使用mysql数据库使用中,最常用,也最考验mysql功底的是sql查询,,sql查询中相对复杂的莫过于子查询。以下这20题是难度依次递增的子查询相关题目。可以用来检验你是否拥有可以使用mysql初级水平的考验!面对疾风吧!!!!(以下题目是sql专家康师傅提供)

表信息

接下来我们大致介绍一下表信息

sql面试题50题标准答案(面试官你都工作2年了)(1)

主要分为员工表EMPLOYEES,部门表DEPARTMENTS,地点表LOCATIONS

开始

1、查询和小明相同部门的员工姓名和工资。

很明显,easy!要先查询小明的部门,然后用这个部门id去查等于此id的员工姓名和工资

SELECT last_name, salary FROM employees WHERE department_id = ( SELECT department_id FROM employees WHERE last_name = '小明' )

2、查询工资比全公司平均工资高的员工的员工号,姓名和工资

这个跟上面的题类似,也很简单,先查询公司平均工资

SELECT employee_id, last_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees )

3、查询和姓名中包含字母u的员工在相同部门的员工号和姓名

SELECT employee_id, last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' )

4、查询工资最低的员工信息:last_name, salary

SELECT last_name, salary FROM employees WHERE salary = ( SELECT DISTINCT MIN(salary) FROM employees )

5、查询工资最低的员工信息:last_name, salary

SELECT last_name, salary FROM employees WHERE salary = ( SELECT DISTINCT MIN(salary) FROM employees )

6、查询平均工资最低的部门

SELECT d.* FROM departments AS d, (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC LIMIT 0, 1) AS s WHERE d.department_id = s.department_id

下面的题要用到相关子查询,数据初级mysql学习者,查询的最高难度类型。答不出来很正常,学习一下就好。

7、查询各部门中工资比本部门平均工资高的员工的 last_name, salary, department_id

SELECT last_name, salary, department_id FROM employees AS e1 WHERE salary > ( SELECT AVG(salary) FROM employee e2 WHERE depatrtment_id = e1.`department_id` );

8、查询每个部门下的部门人数大于 5 的部门名称

SELECT department_name FROM departments AS d WHERE 5 < ( SELECT COUNT(*) FROM employee e WHERE d.depatrtment_id = e.`department_id` );

完毕,接下来学习完高级mysql教程,就可以继续搞nestjs的prisma ORM了,今年一定要把nestjs作为储备技术。

sql面试题50题标准答案(面试官你都工作2年了)(2)

原文出处:https://juejin.cn/post/7083685848637505567

,