MySQL The first 6 Chapter After class exercises of multi table query

1. Show the names of all employees , Department number and department name .

SELECT e.last_name,e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2. Inquire about 90 The staff of department No job_id and 90 Department No location_id

SELECT d.department_id,e.job_id,d.location_id,e.department_id
FROM  employees e JOIN departments d  
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3. Select all employees with bonuses last_name , department_name , location_id , city

SELECT e.last_name ,d.department_name , d.location_id, city 
FROM employees e 
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4. choice city stay Toronto The employees at work last_name , job_id , department_id , department_name

SELECT  e.last_name , e.job_id , e.department_id , d.department_name 
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN locations l
ON l.`location_id`= d.`location_id`
WHERE l.`city` = 'Toronto';

5. Query the Department name of the employee 、 Department Address 、 full name 、 Work 、 Wages , The Department name of the employee's department is ’Executive’

SELECT e.last_name,e.job_id,e.salary,e.department_id,d.department_name,d.location_id,l.street_address
FROM employees e
join departments d
on e.`department_id` = d.`department_id`
join locations l
on d.`location_id`= l.`location_id`
WHERE d.`department_name` = 'Executive'

6. Select the name of the designated employee , Employee number , And his manager's name and employee number , The result is similar to the following format

employees Emp# manager Mgr#

kochhar 101 king 100

SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager ",mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;

7. Query which departments have no employees ( This question can also be a subquery )

select d.department_id
From departments d 
LEFT Join employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;

8. Find out which city has no department

SELECT l.location_id
FROM locations l
left join departments d
on l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

9. Query department name is Sales or IT Employee information

SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` = "Sales" 
OR d.`department_name` = "IT";

OR

SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` in ("Sales","IT");