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)

SQL Tutorial : SYNONYMS

SQL Tutorial : SYNONYMS

Let's learn about next topic: SYNONYMS

SYNONYMS :

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

-ALTERNATIVE NAMES OR SIMPLER NAMES

FOR THE OBJECTS

- CREATE SYNONYM EMPSYN FOR EMPLOYEES

Dropping Synonyms:

DROP SYNONYM EMPSYN

DD: USER_SYNONYMS

SQL Tutorial : INDEXES

SQL Tutorial : INDEXES

Let's learn about next topic: INDEXES

INDEXES:

*********

- USED FOR IMPROVING THE PERFORMANCE BY FETCHING THE

DATA FASTER

BASIC TYPES:

1.B-TREE INDEX

2.BIT MAP INDEX

- WHEN A PRIMARY KEY AND UNIQUE KEY CONSTRAINTS ARE CREATED

AUTOMATICALLY UNIQUE INDEX IS CREATED

NON -UNIQUE INDEX:

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

- CREATE INDEX EMP_INX

ON EMPLOYEES

(LAST_NAME)

FUNCTION BASED INDEX:

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

- CREATE INDEX EMP_INX

ON EMPLOYEES

INITCAP (LAST_NAME)

Dropping an Index:

DROP INDEX EMP_INX

DD: USER_INDEXES

SQL Tutorial : SEQUENCE

SQL Tutorial : SEQUENCE

Let's learn about next topic: SEQUENCE

SEQUENCE:

***********

- UNIQUE VALUE GENERATOR:

10  STARTS WITH 10

20 INCREMENT BY 10

30 MAX VALUE 50

40  MIN VALUE 10
50  

- CREATE SEQUENCE SEQ;

- CREATE SEQUENCE SEQ1

START WITH 10

INCREMENT BY 10

MAXVALUE 50

NOCYCLE

CACHE 20

FUNCTIONS:

- NEXTVAL => GIVES THE NEXT GENERATED VALUE

- CURRVAL => POINTS TO VALUES GENERATED RECENTLY

- INSERT SEQ_TABLE

VALUES(SEQ1.NEXTVAL,'A')

- ALTER SEQUENCE SEQ1

INCREMENT BY 5

MAXVALUE 100

- ALTERING A SEQUENCE CHANGE THE FUTURE GENERATED NUMBERS

ONLY.

** START WITH CANNOT BE ALTERED

- SELECT SEQ.NEXTVAL FROM DUAL

DD: USER_SEQUENCES

Dropping a Sequence:

DROP SEQUENCE SEQ1

SQL Tutorial : COMPLEX VIEW

SQL Tutorial : COMPLEX VIEW

Let's learn about next topic: COMPLEX VIEW

COMPLEX VIEWS:

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

- CREATE OR REPLACE VIEW EMP_DEPTV

AS

SELECT E.LAST_NAME,D.DEPARTMENT_NAME

FROM COPY_EMP E,COPY_DEPT D

WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID

- CREATE OR REPLACE  VIEW EMPV

AS

SELECT DISTINCT EMPLOYEE_ID EID ,LAST_NAME LNAME ,SALARY SAL ,JOB_ID

FROM COPY_EMP

- NAMED SUB-QUERY IN THE FROM CLAUSE IS CALLED AS A INLINE

VIEW.

USAGE:

1.TOP-N-ANALYSIS

2.DISPLAY DATA FROM MULTIPLE TABLES

   USING SUB-QUERY

- DROPING VIEW:

DROP VIEW EMPV

SQL Tutorial : SIMPLE VIEW

SQL Tutorial : SIMPLE VIEW

Let's learn about next topic: SIMPLE VIEW

CREATING SIMPLE VIEW:

-CREATE OR REPLACE  VIEW EMPV

AS

SELECT EMPLOYEE_ID EID ,LAST_NAME LNAME ,SALARY SAL

FROM COPY_EMP

WHERE DEPARTMENT_ID= 50

- INSERT INTO EMPV

VALUES

(210,'WIPRO',20000,'SA_REP')

- CREATE OR REPLACE  VIEW EMPV

AS

SELECT EMPLOYEE_ID EID ,LAST_NAME LNAME ,SALARY SAL

FROM COPY_EMP

WITH READ ONLY

- CREATE OR REPLACE  VIEW EMPV

AS

SELECT EMPLOYEE_ID EID ,LAST_NAME LNAME ,SALARY SAL ,JOB_ID

FROM COPY_EMP

WHERE SALARY >5000

WITH CHECK OPTION

- CREATE OR REPLACE FORCE VIEW EEVV

AS SELECT LNAME ,LSAL

FROM LTABLE

DD: USER_VIEWS

- SELECT VIEW_NAME

FROM USER_VIEWS

WHERE VIEW_NAME LIKE'EE%'

SQL Tutorial : VIEW

SQL Tutorial : VIEW

Let's learn about next topic: VIEW

VIEWS:

*******

- VIRTUAL TABLES.USED TO RESTRICT THE ACCESS

SIMPLE VIEW:

1. ONE TABLE

2. DML IS POSSIBLE

COMPLEX VIEWS:

1. MORE THAN ONE TABLE

2. PSEUDO COLUMNS (ROWNUM,ROWID)

3. GROUP FUNCTIONS AND GROUP BY

4. DISTINCT

5. COMPLEX EXPRESSIONS

6. NOT NULL COLUMNS IN THE TABLE  IS NOT A PART OF VIEW