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; 

INSTR Syntax:  INSTR ( source string, search string [, start position, nth occurance]) Parameters:  source string is the string in which search string is to be searched. Start position is the starting position for searching. It is optional. If it is omitted, its default value is 1. The first position in the source string is 1. If the start position is negative, The INSTR function counts back start position number of characters from the end of string and the searches towards the beginning of string. Nth occurrence is the occurrence of substring which we want. It is an optional parameter. If it is omitted, its default value is 1. Return type:  The INSTR function returns a numeric value. If the search string is not found in source string, INSTR function returns 0. Usage:  Manager to data analyst: Bring me the second word in the street address of each employee. Data analyst runs below query to fetch the required data. SELECT EMPLOYE...

TRUNC Syntax: TRUNC( number [,decimal precision] ) Parameter: number is a source number or column containing source number. decimal precision specifies the degree of truncation and is the optional parameter. If decimal precision parameter is absent, the default degree of rounding is zero, which means source number is truncated to nearest whole number. Return type: TRUNC function (when it applies to numeric value) returns a number. Usage: Manager wants to fill the online report where in the salary section, online system only allows manager to fill two places after decimal point. Manager to Data analyst:  Bring me the list of all employees and their salaries with only two digits after decimal point. Data analyst runs the below query to fetch the required data. SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TRUNC(SALARY, 2) FROM EMPLOYEES;