SQL Diving

CROSS JOIN HR manager to data analyst: Bring me the every possible combination of department ids and job ids along with the details for both. Data analyst runs below query to fetch the data.  SELECT * FROM DEPARTMENTS, JOBS; 

FULL OUTER JOIN:  (i) HR manager to data analyst:   Bring me the details of employees and their corresponding department details and the details of department who doesn’t have any employee. SELECT * FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID= D.DEPARTMENT_ID; (ii) HR manager to data analyst: Bring me the details of job history with its corresponding job details and the details of job which doesn’t have job history. SELECT * FROM JOB_HISTORY JH FULL OUTER JOIN JOBS J ON JH.JOB_ID= J.JOB_ID; 

RIGHT OUTER JOIN (i)  HR Manager wants all the employees’ details regardless of whether they have past Job-history or not. If employee has a job-history then manager also wants job-history details. Manager to data analyst: Bring me the details of employees along with the details of their job history if employee has one. Data analyst runs below query to fetch the required data. SELECT * FROM JOB_HISTORY JH RIGHT OUTER JOIN EMPLOYEES E ON E.EMPLOYEE_ID= JH.EMPLOYEE_ID;   (ii) HR manager wants details of departments regardless of department having manager or not. If department has manager then HR manager also wants the details of manager.  Manager to data analyst:  Bring me the details of departments along with the details of manager of the department. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D ON D.MANAGER_ID= E.EMPLOYEE_ID;

LEFT OUTER JOIN (i) HR Manager wants all the employees’ details regardless of whether they have past Job-history or not. If employee has a job-history then manager also wants job-history details. Manager to data analyst: Bring me the details of employees along with the details of their job history if employee has one. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES E LEFT OUTER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID= JH.EMPLOYEE_ID;  (ii) HR manager wants details of departments regardless of department having manager or not. If department has manager then HR manager also wants the details of manager.  Manager to data analyst:  Bring me the details of departments along with the details of manager of the department. Data analyst runs below query to fetch the required data. SELECT * FROM DEPARTMENTS D LEFT OUTER JOIN EMPLOYEES E ON D.MANAGER_ID= E.EMPLOYEE_ID;

SELF JOIN Usage: HR manager to data analyst : Bring me the employee ids along with the name of manager managing that employee. Data analyst runs below query to fetch the required data. SELECT E.EMPLOYEE_ID, M.EMPLOYEE_ID AS MANAGER_ID, M.FIRST_NAME, M.LAST_NAME FROM EMPLOYEES E, EMPLOYEES M WHERE E.MANAGER_ID= M.EMPLOYEE_ID;

NATURAL JOIN Usage:  HR Manager to data analyst: Bring me the details of employees and the details of corresponding departments. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;