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;

TRUNC Syntax: TRUNC ( source date [, date precision format ] ) Parameters: source date is the date which is to be truncated. Data precision format is the unit of measure to apply for truncating. It is an optional parameter. If date precision format is omitted, the TRUNC function will truncate the date to the day value. Date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI). Return type: The TRUNC function (as it applies to dates) returns date value. Usage:  According to company's policy, the start date of the health insurance of the employees will start from the 1st day of the month of their joining date. Manager to Data analyst: Bring me the list of all employees with the start date of their health insurance. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TRUNC(HIRE_DATE, ‘MM’) FROM EMPLOYEES;