Home

Write a query that returns the first name, last name, and hire date of every female manager. For this problem, an employee is defined as a manager if the employee existing in the dept_manager table. Write this query using common table expressions (CTEs). (Hint: Use dept_manager and employees tables.)

WITH female_employees AS (
	SELECT 
		emp_no,
		first_name,
		last_name,
		hire_date
	FROM employees 
	WHERE gender = 'F'
)

, managers AS (
	SELECT emp_no
	FROM dept_manager 
)

SELECT female_employees.*
FROM female_employees
INNER JOIN managers 
	ON female_employees.emp_no = managers.emp_no 
;

Repeat the question above and get the same output, but write the query using no subqueries, just JOINs.

SELECT 
	employees.first_name,
	employees.last_name,
	employees.hire_date
FROM employees 
INNER JOIN dept_manager
	ON employees.emp_no = dept_manager.emp_no 
WHERE employees.gender = 'F'
;

Write a query that returns the first name, last name, and salary of every level of engineer (e.g. Engineer, Senior Engineer, etc) who was hired after 1970. If an employee has multiple salary records, return the highest salary. Write this query using common table expressions (CTEs). (Hint: Use titles, salaries, employees tables.)

WITH engineers AS (
	SELECT emp_no 
	FROM titles 
	WHERE UPPER(title) LIKE '%ENGINEER%'
	GROUP BY 1
)

, salaries AS (
	SELECT 
		emp_no,
		MAX(salary) AS salary 
	FROM salaries 
	GROUP BY 1
)

, hired_after_1970 AS (
	SELECT
		emp_no,
		first_name,
		last_name
	FROM employees 
	WHERE EXTRACT(year FROM hire_date) > 1970
	GROUP BY 1, 2, 3
)

SELECT 
	hired_after_1970.first_name,
	hired_after_1970.last_name,
	salaries.salary 
FROM hired_after_1970
INNER JOIN salaries
	ON hired_after_1970.emp_no = salaries.emp_no 
INNER JOIN engineers
	ON hired_after_1970.emp_no = engineers.emp_no 
;

Repeat the question above and get the same output, but write the query using no subqueries, just JOINs.

SELECT 
	employees.first_name,
	employees.last_name,
	MAX(salaries.salary) AS salary 
FROM employees 
INNER JOIN salaries 
	ON employees.emp_no = salaries.emp_no 
INNER JOIN titles 
	ON employees.emp_no = titles.emp_no 
WHERE EXTRACT(year FROM employees.hire_date) > 1970
  AND UPPER(titles.title) LIKE '%ENGINEER%'
GROUP BY 1, 2
;

Write a query that returns the first name, last name, and hire date of every employee who has worked in the Marketing department who makes more than $30k in salary. Write this query using common table expressions (CTEs). (Hint: Use dept_emp and salaries tables.)

WITH mktg AS (
	SELECT emp_no 
	FROM dept_emp 
	WHERE dept_no = 'd001'
)

, salaries_gt_30k AS (
	SELECT emp_no 
	FROM salaries 
	WHERE salary > 30000
	GROUP BY 1
)

SELECT 
	employees.first_name,
	employees.last_name,
	employees.hire_date
FROM employees 
INNER JOIN mktg 
	ON employees.emp_no = mktg.emp_no 
INNER JOIN salaries_gt_30k
	ON employees.emp_no = salaries_gt_30k.emp_no 
GROUP BY 1, 2, 3
;

Repeat the question above and get the same output, but write the query using no subqueries, just JOINs.

SELECT
	employees.first_name,
	employees.last_name,
	employees.hire_date
FROM employees 
INNER JOIN salaries 
	ON employees.emp_no = salaries.emp_no 
INNER JOIN dept_emp 
	ON employees.emp_no = dept_emp.emp_no 
WHERE salaries.salary > 30000
  AND dept_no = 'd001'
GROUP BY 1, 2, 3  
;