一些MySQL练习题答案

题目在这

MySQL基础（三）聚合函数、子查询_mysql 聚合函数子查询_独憩的博客-CSDN博客

1、

SELECT last_name, salary
FROM employees e1
WHERE department_id = (
	SELECT department_id
	FROM employees e2
	WHERE e2.`last_name` = 'Zlotkey'
)
AND e1.`last_name` <> 'Zlotkey'

2、

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

3、

SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (
	SELECT salary
	FROM employees
	WHERE job_id = 'SA_MAN'
)

4、

这个有点难

先做左连接，删去在e1中，名字里有 u的人

然后在剩下的人里面选  跟名字有u同部门的人

SELECT e1.employee_id,e1.last_name
FROM employees e1
LEFT JOIN employees e3
ON e1.last_name = e3.last_name AND e1.first_name = e3.first_name 
AND (e3.last_name LIKE '%u%' OR e3.first_name LIKE '%u%' )
WHERE e1.department_id IN (
	SELECT DISTINCT e2.department_id
	FROM employees e2
	WHERE (e2.last_name LIKE '%u%' OR e2.first_name LIKE '%u%' )

)
AND e3.last_name IS NULL

5、

SELECT e.employee_id,l.location_id
FROM employees e,locations l, departments d
WHERE e.department_id = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`location_id` =1700

6、

SELECT last_name
FROM employees 
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'King'
)

7、

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

8、

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id
)

9、

10、

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= ALL(
	SELECT AVG(salary)
	FROM employees
	GROUP BY job_id
)

11、

select AVG(salary),department_id
from employees
GROUP BY department_id
having AVG(salary) > (
	SELECT avg(salary)
	FROM employees
)
and department_id IS NOT NULL

12、

SELECT last_name, employee_id
FROM employees
WHERE employee_id IN (
	SELECT manager_id
	FROM employees
)

13、不知道题目什么意思

14、有待改进

SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id = (
	SELECT manager_id
	FROM departments
	WHERE department_id = (
	SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary) >= ALL(
				SELECT AVG(salary)
				FROM employees
				GROUP BY department_id
			) 
	)
)
	

15、

SELECT DISTINCT department_id 
FROM employees
WHERE job_id <> 'ST_CLERK' 
AND department_id  IS NOT NULL

16、

SELECT last_name
FROM employees 
WHERE manager_id = employee_id
OR  manager_id IS NULL

17、好像没有这个人

SELECT employee_id , last_name,hire_Data,salary
FROM employees 
WHERE manager_id = (
	SELECT employee_id
	FROM employees 
	WHERE last_name = 'Haan' AND first_name = 'De'
)

18、

SELECT employee_id , last_name,salary
FROM employees e1
WHERE salary>(
	SELECT AVG(salary)
	FROM employees e2
	GROUP BY department_id
	HAVING e1.`department_id` = e2.`department_id`
)

19、

SELECT DISTINCT department_id
FROM employees e1
WHERE 5<(
	SELECT COUNT(*)
	FROM employees e2
	GROUP BY department_id
	HAVING e1.`department_id` = e2.`department_id`
)

20、