BD2 final

SELECT * FROM locations;
/* 1. Write a query to display the last name, department number, and department name for all employees.  */

SELECT
  e.last_name,
  department_id,
  d.department_name
FROM
  employees e left join
  departments d USING(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 job_id, d.location_id
FROM
  employees e join
  departments d USING(department_id)
WHERE
  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,
  location_id,
  l.city
FROM
  employees e join
  departments d USING(department_id) join
  locations l USING(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 left join
  departments d USING(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,
  j.job_id,
  d.department_id,
  d.department_name
FROM
  employees e join
  jobs j ON (e.job_id = j.job_id) 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.department_id "DEPARTMENT", e.last_name "EMPLOYEE", e1.last_name "COLLEAGUE"
FROM
  employees e join
  employees e1 ON (e.department_id = e1.department_id AND e.employee_id <> e1.employee_id)
ORDER BY
  department ASC;

/* 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 inner join
  employees e2 ON e2.last_name = 'Davies' AND
  e.hire_date > e2.hire_date
ORDER BY
  e.hire_date ASC;

/* 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 "Employee",
  e.hire_date "Emp Hired",
  m.last_name "Manager",
  m.hire_date "Mgr Hired"
FROM
  employees e inner join
  employees m ON
  e.hire_date < m.hire_date AND
  e.manager_id = m.employee_id;