08. DRL OR DQL (DATA QUERY/RETRIEVE LANGUAGE)

    DRL OR DQL (DATA QUERY/RETRIEVE LANGUAGE):

    

   SELECT:

SYN:

[WITH CLAUSE]

SELECT [DISTINCT|ALL] *|COLUMNS|EXP|FUNCTIONS|LITERAL|SUBQUERIES

FROM TABLENAME|VIEWS|SUBQUERIES|TABLE FUNCTIONS

[WHERE CONDITION]

[START WITH CONDITON]

[CONNECT BY CONDITION]

[GROUP BY COLUMNS|EXP]

HAVING CONDITIONS (COLUMNS|FUNCTIONS)

ORDER BY COLUMNS|EXP|VALUES [ASC|DESC] [NULLS FIRST|NULLS LAST]|ANALYTICAL FUNCTIONS)

EG: SELECT * FROM EMP;

      SELECT 1 FROM EMP;

               SELECT ALL * FROM EMP;

               SELECT DISTINCT * FROM EMP;

               SELECT DISTINCT DEPTNO FROM EMP;

               SELECT 2*2 FROM EMP;

DUAL:

ü  It is a dummy table.

ü  Dual table is having single record.

ü  To display the required result only for once we use dual table.

MULTIPLE INSERT:

TABLES:  MULTAB,MULTAB1,MULTAB2;

SYN:       INSERT ALL

               INTO TABLENAME VALUES (VALUES)

               INTO TABLENAME VALUES (VALUES)

               INTO TABLENAME VALUES (VALUES)

               SELECT * FROM TABLENAME;

EG: INSERTING MULTIPLE VALUES INTO SAME TABLE;

      INSERT ALL

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      INTO MULTAB VALUES (1,’X’,’HYD’)

      INTO MULTAB VALUES (10,’Y’,’BANG’)

      SELECT * FROM DEPT;

EG: INSERTING MULTIPLE VALUES INTO SPECIFIED COLUMNS

      INSERT   ALL

      INTO MULTAB (DEPTNO, LOC) VALUES (DEPTNO, LOC)

      INTO MULTAB VALUES (1,’X’,’HYD’)

      INTO MULTAB VALUES (10,’Y’,’BANG’)

      SELECT * FROM DEPT WHERE DEPTNO=10;

EG: INSERTING INTO MULTIPLE TABLES

       INSERT ALL

       INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

       INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

       INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

       SELECT * FROM DEPT WHERE DEPTNO=10;

EG: INSERTING BASED ON WHEN CONDITION WITH OUT ELSE

      INSERT ALL

      WHEN DEPTNO=10 THEN

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      WHEN DEPTNO=20 THEN

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      WHEN DEPTNO=30 THEN

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      SELECT * FROM DEPT;

  

EG: INSERTING WITH ELSE PART;

      INSERT ALL

      WHEN DEPTNO>50 THEN

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      WHEN DEPTNO>60 THEN

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      ELSE

      INTO MULTAB VALUES (DEPTNO, DNAME, LOC)

      SELECT * FROM DEPT;