Subqueries and JOINs - Exercise

01. Employee Address
SELECT e.employee_id,e.job_title,e.address_id,a.address_text FROM employees AS e
LEFT JOIN addresses AS a ON e.address_id=a.address_id ORDER BY e.address_id ASC
LIMIT 5;
----------
02. Addresses with Towns
SELECT e.first_name,e.last_name,t.name AS `town`,a.address_text FROM employees AS e
LEFT JOIN addresses AS a ON e.address_id=a.address_id RIGHT JOIN towns AS t
ON a.town_id=t.town_id ORDER BY first_name ASC,last_name ASC LIMIT 5;
-------------
03. Sales Employee
SELECT e.employee_id,e.first_name,e.last_name,d.name AS `department_name`
FROM employees AS e LEFT JOIN departments AS d ON d.department_id=e.department_id
WHERE d.name='Sales' ORDER BY e.employee_id DESC;
--------------
04. Employee Departments
SELECT e.employee_id,e.first_name,e.salary,d.name AS `department_name`
FROM employees AS e LEFT JOIN departments AS d ON d.department_id=e.department_id
WHERE e.salary > 15000 ORDER BY d.department_id DESC LIMIT 5;
-------------
05. Employees Without Project
SELECT e.employee_id,e.first_name FROM employees AS e
LEFT JOIN employees_projects AS ep ON ep.employee_id=e.employee_id
WHERE ep.employee_id IS NULL ORDER BY e.employee_id DESC LIMIT 3;
------------
06. Employees Hired After
SELECT first_name,last_name,hire_date,name AS `dept_name`
FROM employees AS e LEFT JOIN departments AS d ON d.department_id=e.department_id
WHERE DATE(e.hire_date) > '1999-01-01' AND d.name IN ('Sales','Finance')
ORDER BY e.hire_date ASC;
---------------
07. Employees with Project
SELECT e.employee_id,e.first_name,p.name AS `project_name` FROM employees AS e
LEFT JOIN employees_projects AS ep ON ep.employee_id=e.employee_id
RIGHT JOIN projects AS p ON ep.project_id=p.project_id WHERE DATE(p.start_date)
> '2002-08-13' AND DATE(p.end_date) IS NULL ORDER BY e.first_name ASC,
p.name ASC LIMIT 5;
---------------
08. Employee 24
SELECT e.employee_id,e.first_name,
CASE
    WHEN YEAR(p.start_date) >= '2005'
    THEN p.name = NULL
    ELSE p.name
    END
AS `project_name` FROM employees AS e
LEFT JOIN employees_projects AS ep ON ep.employee_id=e.employee_id
LEFT JOIN projects AS p ON ep.project_id=p.project_id
WHERE e.employee_id=24
ORDER BY `project_name` ASC;
--------------
09. Employee Manager
SELECT e.employee_id,e.first_name,e.manager_id,e2.first_name AS `manager_name`
FROM employees AS e JOIN employees AS e2 ON e.manager_id=e2.employee_id
WHERE e.manager_id IN (3,7) ORDER BY e.first_name ASC;
-------------
10. Employee Summary
SELECT e.employee_id,CONCAT(e.first_name,' ',e.last_name) AS `employee_name`,
CONCAT(e2.first_name,' ',e2.last_name) AS `manager_name`,d.name AS `department_name`
FROM employees AS e JOIN employees AS e2 ON e.manager_id=e2.employee_id
JOIN departments AS d ON d.department_id=e.department_id ORDER BY e.employee_id ASC
LIMIT 5;
---------------
11. Min Average Salary
SELECT AVG(e.salary) AS `min_average_salary` FROM employees AS e
GROUP BY e.department_id ORDER BY AVG(e.salary) LIMIT 1;
------------
12. Highest Peaks in Bulgaria
SELECT c.country_code,m.mountain_range,peak_name,elevation FROM peaks AS p
LEFT JOIN mountains AS m ON p.mountain_id=m.id RIGHT JOIN mountains_countries
AS mc ON m.id=mc.mountain_id RIGHT JOIN countries AS c ON
c.country_code=mc.country_code WHERE c.country_name='Bulgaria' AND
p.elevation > 2835 ORDER BY p.elevation DESC;
-------------
13. Count Mountain Ranges
SELECT mc.country_code,COUNT(m.id) AS `mountain_range`
FROM mountains_countries AS mc
JOIN mountains AS m ON m.id=mc.mountain_id
WHERE mc.country_code IN ('BG','US','RU')
GROUP BY mc.country_code
HAVING mountain_range > 0
ORDER BY mountain_range DESC;
-----------
14. Countries with Rivers
SELECT country_name,river_name FROM rivers AS r
RIGHT JOIN countries_rivers AS cr ON r.id=cr.river_id
RIGHT JOIN countries AS c ON c.country_code=cr.country_code
WHERE c.continent_code='AF' ORDER BY c.country_name ASC LIMIT 5;