queries_section7_3_5_intermediate_results.txt

-- SECTION 7.3.5 - Queries for Intermediate Results
-- Query to create new table current_employee
-- Joining retirement_info and dept_emp tables to check the 
-- date and ensure they are still employed with company

SELECT * FROM current_emp

-- Create new table employees_by_dept to obtain count
-- of potential retirees by dept for management planning. 
SELECT COUNT(ce.emp_no), de.dept_no
--INTO employees_by_dept
FROM current_emp as ce
LEFT JOIN dept_emp as de
ON ce.emp_no = de.emp_no
GROUP BY de.dept_no
Order By de.dept_no;

SELECT * FROM employees_by_dept;

-- LIST 1: EMPLOYEE INFORMATION (Section 7.3.5)
-- Description - create new table emp_info which is a 
-- list of employees, still employed by company,
-- born between 1952 - 1955 and hired between 1985 - 1988
-- 31-34 years with the company. 

SELECT e.emp_no,
	e.first_name,
	e.last_name,
	e.gender,
	s.salary,
	de.to_date
INTO emp_info
FROM employees As e 
INNER JOIN Salaries AS s
ON (e.emp_no = s.emp_no)
INNER JOIN dept_emp as de
ON (e.emp_no = de.emp_no)
WHERE (e.birth_date BETWEEN '1952-01-01' AND '1955-12-31')
     AND (e.hire_date BETWEEN '1985-01-01' AND '1988-12-31')
	 AND (de.to_date = '9999-01-01');

SELECT * FROM emp_info;

-- LIST 2: MANAGEMENT (section 7.3.5) 
-- Description - create new table manager_info for each dept
-- from already filtered current_emp list. 
SELECT  dm1.dept_no,
        d.dept_name,
        dm1.emp_no,
        ce.last_name,
        ce.first_name,
        dm1.from_date,
        dm1.to_date
INTO manager_info
FROM dept_manager1 AS dm1
    INNER JOIN departments AS d
        ON (dm1.dept_no = d.dept_no)
    INNER JOIN current_emp AS ce
        ON (dm1.emp_no = ce.emp_no); 
SELECT * FROM manager_info;



-- LIST 3: DEPARTMENT RETIREES
-- Final list with departments added. Introduced problem
-- in that not showing date of dept change. duplicate records
-- showing employee in previous depts. 

SELECT ce.emp_no,
	ce.first_name,
	ce.last_name,
	d.dept_name	
INTO dept_info
FROM current_emp as ce
INNER JOIN dept_emp AS de
ON (ce.emp_no = de.emp_no)
INNER JOIN departments AS d
ON (de.dept_no = d.dept_no);

-- TAILORED LIST #1 
-- Create list sales_info of potential retires. Use ce list
-- and add additional condition of dept = sales

SELECT ce.emp_no,
	ce.first_name,
	ce.last_name,
	d.dept_name	
INTO sales_info
FROM current_emp as ce
INNER JOIN dept_emp AS de
ON (ce.emp_no = de.emp_no)
INNER JOIN departments AS d
ON (de.dept_no = d.dept_no)
WHERE (d.dept_name = 'Sales');
SELECT * FROM sales_info;

-- TAILORED LIST #2 
-- Create list sales_dev_info of potential retires. Use ce list
-- and add additional condition of dept = sales OR
-- dept = development
SELECT ce.emp_no,
	ce.first_name,
	ce.last_name,
	d.dept_name	
INTO sales_dev_info
FROM current_emp as ce
INNER JOIN dept_emp AS de
ON (ce.emp_no = de.emp_no)
INNER JOIN departments AS d
ON (de.dept_no = d.dept_no)
WHERE d.dept_name
IN ('Sales', 'Development');

SELECT * FROM sales_dev_info