MySQLデータベース------サブクエリ練習問題

#1、           ：last_name,salary

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


#2、             

#   
SELECT d.*
FROM employees e
INNER  JOIN departments d
ON e.`department_id`=d.department_id
GROUP BY department_id
HAVING  AVG(salary) = (
	SELECT MIN(  )
	FROM (
		SELECT AVG(salary)   ,department_id
		FROM employees
		GROUP BY department_id
	 )   
);

#   

SELECT *
FROM departments
WHERE department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);


#3、                      

SELECT d.*,AVG(salary)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY e.department_id
HAVING  AVG(salary) = (
	SELECT MIN(  )
	FROM (
		SELECT AVG(salary)   ,department_id
		FROM employees
		GROUP BY department_id
	 )   
);

#4、         job  

SELECT *
FROM jobs
WHERE job_id =(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC 
	LIMIT 1
);


#5、                    ？

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
	FROM employees
);

#6、       manager     

SELECT *
FROM employees 
WHERE employee_id IN (
	SELECT DISTINCT manager_id
	FROM employees 
);

#7、                         ？

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING department_id =(
	SELECT department_id     
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1

);


#8、            manager     ：last_name,department_id,email,salary

SELECT last_name,department_id,email,salary
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MAX(  ) 
	FROM (
		SELECT AVG(salary)   ,department_id    
		FROM employees
		GROUP BY department_id

	)   
);