--1. Write a query to display the last name, department number, and department name for all employees.

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


--2. Create a unique listing of all jobs that are in department 80. Include the location of department 80 in the output.

SELECT DISTINCT e.job_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id=d.department_id)
WHERE e.department_id=80;


--3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.

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


--4.Display the employee last name and department name for all employees who have an a (lowercase) in their last names.

SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.last_name LIKE '%a%';


--5. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. Use JOIN .. ON.


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 (d.location_id=l.location_id)
WHERE l.city= 'Toronto';


--6. Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Use JOIN … ON.


SELECT e.last_name AS "Employee", e.department_id AS "Department", c.last_name AS "Colleague" FROM employees e
JOIN employees c
ON (e.department_id=c.department_id)
WHERE (e.employee_id != c.employee_id)


--7. Create a query to display the name and hire date of any employee hired after employee Davies.
--Join a table to itself (self join). You can not use WHERE HIRE_DATE>’ 97/01/29’.

SELECT e.last_name, e.hire_date
FROM employees e
JOIN employees davies
ON (davies.last_name='Davies')
WHERE davies.hire_date < e.hire_date;


--8. Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively.
--Join a table to itself (self join).

SELECT e.last_name AS "Employee", e.hire_date AS "Emp Hired",
m.last_name AS "Manager", m.hire_date AS "Mgr Hired"
FROM employees e JOIN employees m
ON (e.manager_id=m.employee_id)
WHERE e.hire_date < m.hire_date;