MySQL - 基础题练习题 - 查询篇(8)

数据在本专栏的第一篇博客里

子查询，做过第三题之后就顺了

9.1.查询和Zlotkey相同部门的员工姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	department_id IN ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' );

9.2.查询工资比公司平均工资高的员工的员工号，姓名，工资

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

9.3.查询各部门中工资比本部门平均工资高的员工号，姓名，工资

#这题有点难，我的话不分步反应不过来
#1.各部门的平均工资：
SELECT
	department_id,
	AVG( salary ) 
FROM
	employees 
GROUP BY
	department_id;
#2.加一个连接查，用department_id连接两个表：
SELECT
	employee_id,
	last_name,
	salary,
	e.department_id 
FROM
	employees e
	LEFT JOIN ( SELECT department_id, AVG( salary ) ag_sal FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id 
WHERE
	e.salary > d.ag_sal;

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

#1.上一个会做，这个就也还行，先查字母u的员工：
SELECT
	employee_id,
	last_name 
FROM
	employees 
WHERE
	last_name LIKE '%u%';
#2.和上面这个在一个部门的员工：
SELECT
	employee_id,
	last_name 
FROM
	employees 
WHERE
	department_id IN ( SELECT department_id FROM employees WHERE last_name LIKE '%u%' );

9.5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT
	employee_id 
FROM
	employees 
WHERE
	department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 );

9.6.查询管理者是k_ing的员工姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'K_ing' );

9.7.查询工资最高的员工的姓名，要求first和last_name显示为一列，列名为姓名

SELECT
	CONCAT( first_name, last_name ) '姓名',
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT MAX( salary ) FROM employees );

结果的话我都没贴截图，因为下一篇还有只看表，然后直接写代码的..