SQL Tutorial : Multi Table Insert (UNCONDITIONAL & CONDITIONAL & CONDITIONAL FIRST INSERT & PAIR WISE COMPARISON )

SQL Tutorial : Multi Table Insert (UNCONDITIONAL & CONDITIONAL & CONDITIONAL FIRST INSERT & PAIR WISE COMPARISON )

Let's learn about next topic: Multi Table Insert (UNCONDITIONAL & CONDITIONAL & CONDITIONAL FIRST INSERT & PAIR WISE COMPARISON )

MULTI TABLE INSERT:

*********************

UNCONDITIONAL:

*****************

- INSERT ALL

INTO SAMPLE1

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY)

INTO SAMPLE2

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE)

INTO SAMPLE3

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID

FROM EMPLOYEES

CONDITIONAL INSERT:

***********************

- INSERT ALL

WHEN SALARY >15000 THEN

INTO SAMPLE1

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY)

WHEN LAST_NAME LIKE 'K%' THEN

INTO SAMPLE2

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE)

ELSE

INTO SAMPLE3

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID

FROM EMPLOYEES

CONDITIONAL FIRST INSERT :

*****************************

- INSERT FIRST

WHEN SALARY >15000 THEN

INTO SAMPLE1

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY)

WHEN LAST_NAME LIKE 'K%' THEN

INTO SAMPLE2

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE)

ELSE

INTO SAMPLE3

VALUES(EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,HIRE_DATE,JOB_ID

FROM EMPLOYEES

PAIR WISE COMPARISON :

**************************

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE (DEPARTMENT_ID ,JOB_ID) IN (SELECT DEPARTMENT_ID,JOB_ID

FROM EMPLOYEES

WHERE EMPLOYEE_ID=156)

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

FROM EMPLOYEES

WHERE EMPLOYEE_ID=156)

AND JOB_ID IN(SELECT JOB_ID  FROM EMPLOYEES
WHERE EMPLOYEE_ID=156)