寫在前面

今天繼續前面的20題之後的練習，數據都是一樣的，可以直接使用，來看看你的sql功底降了沒。

基礎20題

#1.查詢每個員⼯的姓名、郵箱、職位名稱以及所在部⻔名稱。
	SELECT
	CONCAT(last_name,first_name) as 姓名,
	email,
	job_title,
	department_name
FROM
	employees e 
	JOIN departments d ON e.department_id=d.department_id
	JOIN jobs j ON e.job_id = j.job_id ;

#2.查詢⽉薪最⾼的員⼯的姓名、職位名稱以及⽉薪。
	SELECT
	CONCAT(last_name,first_name) as 姓名,
	job_title,
	salary
FROM
	employees e 
JOIN jobs j on e.job_id = j.job_id
WHERE	salary = (SELECT MAX(salary) FROM employees);

#3.查詢每個部⻔的平均⽉薪。
#註：如果某一部門沒有員工不會顯示
	SELECT
	department_name,
	AVG(salary)
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
GROUP BY
	department_name;   

#4.查詢部⻔中員⼯⽉薪⾼於5000的所有員⼯的姓名、⽉薪以及所在部⻔名稱。
	SELECT
	CONCAT(last_name,first_name) as 姓名,
	salary,
 department_name
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
WHERE
	salary > 5000;

#5.查詢獎⾦率最⾼的員⼯的姓名、職位名稱以及獎⾦率。
	SELECT
	CONCAT(last_name,first_name) as 姓名,
	job_title,
	commission_pct
FROM
	employees e 
JOIN jobs j ON e.job_id = j.job_id
WHERE
	commission_pct = (SELECT MAX(commission_pct) FROM employees);

#6.查詢每個職位的最⾼⽉薪和最低⽉薪。
	SELECT
	job_title,
	MAX(salary),
	MIN(salary)
FROM
	employees e 
JOIN jobs j ON e.job_id = j.job_id
GROUP BY
	job_title;
	
#7.查詢⼊職時間早於2000年的所有員⼯的姓名、⼊職⽇期以及職位名稱。
	SELECT
	CONCAT(last_name,first_name) as 姓名,
	hiredate,
	job_title
FROM
	employees e 
JOIN jobs j on e.job_id = j.job_id
WHERE
	YEAR(hiredate) < 2000;

#8.查詢每個部⻔的員⼯⼈數。
	SELECT
	COUNT(*) as 人數,
	department_name
FROM
  employees e 
JOIN departments d ON e.department_id = d.department_id
GROUP BY
	department_name;

#9.查詢在每個部⻔中⽉薪最⾼的員⼯的姓名、⽉薪以及部⻔名稱。
#註：在使用in時可以使用（）將需要的列括起來查詢
	SELECT 
	CONCAT(e.last_name,e.first_name) as 姓名,
	e.salary,
	d.department_name
FROM
	employees e JOIN departments d ON e.department_id = d.department_id 
WHERE
	(e.salary,d.department_id) in (
SELECT MAX(salary),department_id FROM employees GROUP BY department_id);               

#10.查詢⽉薪超過部⻔平均⽉薪的員⼯的姓名、⽉薪以及所在部⻔名稱。
	SELECT
	CONCAT( e1.last_name, e1.first_name ) AS 姓名,
	e1.salary,
	d.department_name 
FROM
	employees e1
	JOIN departments d ON e1.department_id = d.department_id 
WHERE
e1.salary > ( SELECT AVG( e2.salary ) FROM employees e2 WHERE e1.department_id = e2.department_id);

#11.查詢每個職位的員⼯⼈數。
	SELECT 
	COUNT(*) as 人數,
	job_title
FROM
	employees e 
JOIN jobs j ON e.job_id = j.job_id
GROUP BY
	job_title;

#12.查詢每個部⻔的最⾼⽉薪和最低⽉薪。
	SELECT
	department_name,
	MAX(salary),
	MIN(salary)
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
GROUP BY
	department_name;

#13.查詢每個員⼯的姓名、郵箱、職位名稱以及他們的上級領導的姓名。
	SELECT
	CONCAT(e1.last_name,e1.first_name) as 姓名,
	e1.email,
	j.job_title,
	CONCAT(e2.last_name,e2.first_name) as 上級領導
FROM
	employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
JOIN jobs j ON e1.job_id = j.job_id;

#14.查詢每個部⻔的員⼯平均獎⾦率。
	SELECT
	department_name,
	AVG(commission_pct) as 平均獎金率
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
GROUP BY
	department_name;

#15.查詢每個城市的員⼯⼈數。
	SELECT
	COUNT(*) as 人數,
	city
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY
	city;

#16.查詢每個部⻔的職位種類數。
#註：使用distinct可以去重算種類數
	SELECT
	COUNT(DISTINCT job_id) as 種類數,
	department_name
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
GROUP BY
	department_name;  

#17.查詢⼯資⾼於其職位平均⼯資的員⼯姓名、職位名稱以及⽉薪。
	SELECT
	CONCAT(e1.last_name, e1.first_name ) AS 姓名,
	j.job_title,
	e1.salary
FROM
 employees e1
JOIN jobs j ON e1.job_id = j.job_id
WHERE
	e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.job_id = e2.job_id);

#18.查詢每個國家的員⼯⼈數。
	SELECT
	COUNT(DISTINCT employee_id) as 人數,
	country_id
FROM
	employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY
	country_id;

#19.查詢沒有領導的員⼯的姓名以及職位名稱。
	SELECT
	CONCAT( last_name, first_name ) AS 姓名,
	job_title
FROM
	employees e 
JOIN jobs j ON e.job_id = j.job_id
WHERE
	manager_id is null ;

#20.查詢job_id為"IT_PROG"的員⼯的姓名、職位名稱以及⽉薪。
	SELECT
	CONCAT( last_name, first_name ) AS 姓名,
	job_title,
	salary
FROM
	employees e 
JOIN jobs j ON e.job_id = j.job_id
WHERE
	e.job_id = 'IT_PROG';

好了，今天的分享結束了，答案僅供參考不代表最終答案，如果有更好的方法，歡迎在底下留言評論！