add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, August 5

Oracle SQL Notes


SQL(Structured Query Languages)
            Standard Languages for Databases
            ANSI (American National Standard Institute) Standard
            Used to Interact / communicate with database
            Once Learned user every where

            SQL is re-categorized in sub-languages according to tasks:

            DCL(Data Control Languages)
                        Used to Control Database Security
                        Create User, Change Password, Assign Rights, snatch rights
                        CREATE USER, GRANT RIGHTS, REVOKE RIGHTS

                        CREATE USER

                        You can create new users by this command and assign password to it.
                        CREATE USER SCOTT IDENTIFIED BY TIGER;

                        ALTER USER
                                    ALTER USER SCOTT IDENTIFIED BY TIGERS;

                        GRANT RIGHTS
GRANT RESOURCE, CONNECT, CREATE TABLE, CREATE SESSION,DBA TO SCOTT;

                        REVOKE RIGHTS
                                    REVOKE CREATE SESSION FROM SCOTT;

            DDL(Data Definition Language)
                        Used to interact with database
                        Define objects, change objects, remove objects
                        CREATE, ALTER, DROP

                        CREATE TABLE                                      
                                    CREATE TABLE EMP(
                                    EMPNO NUMBER(10) primary key,
                                    ENAME VARCHAR2(30),
                                    SAL NUMBER(12,2),
                                    COMM NUMBER(12,2),
                                    DEPTNO NUMBER(10)
                                    );

                        ALTER TABLE
                                    ALTER TABLE EMP
                                    ADD MGR NUMBER(5);

                                    ALTER TABLE EMP
                                    MODIFY ENAME VARCHAR2(50);

                        DROP TABLE
                                    DROP TABLE EMP;

                        TRUNCATE

                                    TRUNCATE TABLE EMP_HISTORY;

                                   
            DML (Data Manipulation Language)
                        Used to interact with tables
                        Store new records, change records, remove records, select data from tables.
                        INSERT,UPDATE, DELETE, SELECT

                        INSERT
                                    INSERT INTO EMP
                                    VALUES (1,’ASAD’,5000,500,7787,10);

                                    INSERT INTO EMP(EMPNO,ENAME,SAL)
                                    VALUES (2,’ALEEM’,7000);

                                    INSERT INTO EMP(EMPNO,ENAME,SAL)
                                    VALUES (&no,’&name’,&salary);

 

                                    INSERT INTO EMP_HISTORY

SELECT * FROM EMP;

 


 

 

UPDATE

                                    UPDATE EMP
                                    SET MGR = 7767;

                                    UPDATE EMP
                                    SET COMM = 200
                                    WHERE COMM IS NULL;

                                    UPDATE   EMPLOYEES
SET      JOB_ID  = (SELECT  JOB_ID
FROM    EMPLOYEES
          WHERE   EMPLOYEE_ID = 205),
         SALARY  = (SELECT  SALARY
FROM    EMPLOYEES
                     WHERE   EMPLOYEE_ID = 205)
WHERE    EMPLOYEE_ID    =  114;



UPDATE  COPY_EMP
SET     DEPARTMENT_ID  =  (SELECT DEPARTMENT_ID
FROM EMPLOYEES
                           WHERE EMPLOYEE_ID = 100)
WHERE   JOB_ID         =  (SELECT JOB_ID
                           FROM EMPLOYEES
                           WHERE EMPLOYEE_ID = 200);


                        DELETE

                                    DELETE FROM EMP;

                                    DELETE FROM EMP
                                    WHERE SAL < 2000;

 

 

DELETE FROM EMPLOYEES

WHERE  DEPARTMENT_ID =

                (SELECT DEPARTMENT_ID

                 FROM   DEPARTMENTS

                 WHERE  DEPARTMENT_NAME

                        LIKE '%PUBLIC%');

 

 

                        SELECT

                                    SELECT * FROM EMP;
                       
                                    SELECT ENAME,JOB,SAL
                                    FROM EMP;

                                    SELECT ENAME EMPLOYEE,JOB DESIGNATION, SAL SALARY
                                    FROM EMP;
                       
                                    SELECT ENAME,JOB,SAL,SAL*.2 BONUS
                                    FROM EMP;

                                    SELECT ‘The name of Employee is’||ENAME
                                    FROM EMP;

                                    SELECT * FROM EMP
                                    ORDER BY ENAME;

                                    SELECT * FROM EMP
                                    ORDER BY 2;
           
                                    SELECT * FROM EMP
                                    ORDER BY JOB,DEPTNO;
                                    SELECT * FROM EMP
                                    ORDER BY JOB DESC,DEPTNO;

                                    SELECT * FROM EMP
                                    WHERE SAL < 3000;

                                    SELECT * FROM EMP
                                    WHERE SAL > 5000;
                       
                                    SELECT * FROM EMP
                                    WHERE DEPTNO = 20;

                                    SELECT * FROM EMP
                                    WHERE DEPTNO IN (10,30,40);

                                    SELECT * FROM EMP
                                    WHERE SAL BETWEEN 2000 AND 5000;

                                    SELECT * FROM EMP
                                    WHERE ENAME LIKE ‘A%;’

                                    SELECT * FROM EMP
                                    WHERE ENAME LIKE  ‘%ALI%’;

                                    SELECT * FROM EMP
                                    WHERE JOB = ‘MANAGER’
                                    AND SAL < 3000;

                                    SELECT * FROM EMP
                                    WHERE JOB = ‘CLERK’
                                    OR SAL > 2000;

                                    SELECT * FROM EMP
                                    WHERE  COMM IS NOT NULL;
                                   
            TCL (Transaction Control Language)
                        Used to control Transactions
                        COMMIT, ROLLBACK, SAVEPOINT

                        COMMIT;
                        ROLLBACK;
                        SAVEPOINT;





Operator Precedence


Operator
Meaning
1
Arithmetic operators
2
Concatenation operator
3
Comparison conditions
4
IS [NOT] NULL, LIKE, [NOT] IN
5
[NOT] BETWEEN
6
Not equal to
7
NOT logical condition
8
AND logical condition
9
OR logical condition
  1.  

 

 

 

 

 

Functions

            Single Row
                        CHARACTER FUNCTIONS
                                    UPPER
                                                SELECT UPPER(ENAME) FROM EMP;
                                    LOWER
                                                SELECT LOWER(ENAME FROM EMP;
                                    INITCAP
                                                SELECT INITCAP(ENAME) FROM EMP;
                                    LENGTH
                        SELECT LENGTH(ENAME) FROM EMP;
            SUBSTR
                                                SELECT SUBSTR(ENAME,2,3) FROM EMP;
            INSTR           
                                                SELECT INSTR(ENAME,’A’) FROM EMP;
            LPAD
                                                SELECT LPAD(ENAME,10,’~’) FROM EMP;
            RPAD
                                                SELECT RPAD(ENAME,10,’~’) FROM EMP;
            REPLACE
                                                SELECT REPLACE(ENAME,’S’,’A’) FROM EMP;
            TRIM
                                                SELECT TRIM (‘H’ FROM ‘HELLO WORLD’) FROM EMP;



                        NUMERIC FUNCTIONS
                                    ROUND
                                                SELECT ROUND(17879.879,2) FROM DUAL;
                                                SELECT ROUND(17879.241,2) FROM DUAL;
                                               
                                    TRUNC
                                                SELECT TRUNC(17879.879,2) FROM DUAL;
                                                SELECT TRUNC(17879.241,2) FROM DUAL;

                                    MOD
                                                SELECT MOD(15,4) FROM DUAL;

                        DATE FUNCTIONS

                                    ADD_MONTHS
                                                SELECT ADD_MONTHS(HIREDATE,10) FROM EMP;                                  

                                    MONTHS_BETWEEN
                                                SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;

LAST_DAY

                                                SELECT LAST_DAY(SYSDATE) FROM DUAL;

                                    NEXT_DAY
                                    ROUND
                                    TURNC



                        CONVERSION FUNCTIONS

                                    TO_CHAR
                                                SELECT TO_CHAR(SYSDATE,’DD-MM-RR HH:MI’) FROM DUAL;
                                                SELECT TO_CHAR(SAL,’999,999.00’) FROM EMP;

                                    TO_NUMBER
                                                SELECT TO_NUMBER(SUBSTR(HIREDATE,1,2)) FROM EMP;

 

                                    TO_DATE

                                                SELECT TO_DATE(’10-10-04’,’MM-DD-YY’) FROM DUAL;

                        OTHER FUNCTIONS

                                    NVL

                                                SELECT NVL(SAL,100) FROM EMP;
                                    NVL2
                                                SELECT NVL2(COMM,SAL+COMM,SAL) FROM EMP;
                                    NULLIF (if both parameters in the function are equal then return null)
                                                SELECT NULLIF(1234,1234) FROM DUAL;

                                    COALESCE (return first non null value)
                                                SELECT COALESCE(SUBSTR('ABC',4),'NOT BC','NO SUBSTRING') FROM DUAL;

                                    DECODE

SELECT DECODE(JOB,’MANAGER’,SAL*.2,’CLERK’,SAL*.1,SAL*.05) FROM EMP;

                                    CASE EXPRESSION
                                                SELECT CASE JOB WHEN ‘PRESIDENT’ THEN ‘A+’
                                                WHEN ‘MANAGER’ THEN ‘A’
                                                ELSE ‘B’ END
                                                FROM EMP;

            Group Functions                  

                        SUM

                                    SELECT SUM(SAL) FROM EMP;
                                    SELECT SUM(SAL) FROM EMP
                                    GROUP BY DEPTNO;
                                    SELECT SUN(SAL) FROM EMP
                                    GROUP BY JOB;

                        AVG

                                    SELECT AVG(SAL) FROM EMP
                                    GROUP BY DEPTNO;

 

                        MIN

                                    SELECT MIN(SAL) FROM EMP;

                        MAX

                                    SELECT MAX(SAL) FROM EMP;

                        COUNT

                                    SELECT COUNT(*) FROM EMP;
                                    SELECT COUNT(*) FROM EMP
                                    GROUP BY JOB;

            Having Clause

                        SELECT SUM(SAL)
                        FROM EMP
                        GROUP BY JOB
                        HAVING SUM(SAL) > 5000;

Joins

            INNER JOIN
                        SELECT DNAME,ENAME,JOB,SAL
                        FROM EMP,DEPT
                        WHERE EMP.DEPTNO = DEPT.DEPTNO;

            OUTER JOIN

            SELECT DNAME,ENAME,JOB,SAL
                        FROM EMP,DEPT
                        WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;
           

            SELF JOIN

                        SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
                        B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
                        FROM EMP A,EMP B
                        WHERE A.EMPNO = B.MGR;

            NATURAL JOIN
                        SELECT DEPTNO,DNAME,ENAME,JOB,SAL
                        FROM DEPT
                        NATURAL JOIN EMP

            USING CLAUSE
                        SELECT DNAME,JOB,SAL
FROM EMP
JOIN DEPT
USING (DEPTNO)

            ON CLAUSE
                        SELECT DNAME,ENAME,JOB,SAL
FROM EMP JOIN DEPT
ON (EMP.DEPTNO = DEPT.DEPTNO);
           
            SELF JOIN USING ON CLAUSE
                        SELECT A.ENAME SUBNAME,A.JOB SUBJOB,
A.EMPNO SUBNO, B.ENAME MNGNAME,
B.JOB MNGJOB,B.EMPNO MNGNO
FROM EMP A JOIN EMP B
ON (A.MGR = B.EMPNO)

            Three way join          with ON CLAUSE
                        SELECT EMPNO,CITY,DNAME
                        FROM EMP
                        JOIN DEPT
                        ON (EMP.DEPTNO = DEPT.DEPTNO)
                        JOIN LOC
                        ON (LOC.LOCATION_ID = DEPT.LOCATION_ID)

            Non equi join
                        SELECT E.LAST_NAME,E.SALARY,J.GRADE_LEVEL
                        FROM EMP E JOIN JOB_GRADES J
                        ON E.SAL BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL

            LEFT OUTER JOIN
                        SELECT DNAME,ENAME,JOB,SAL
FROM EMP E LEFT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)

            RIGHT OUTER JOIN
                        SELECT DNAME,ENAME,JOB,SAL
FROM EMP E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)

            FULL OUTER JOIN
                        SELECT DNAME,ENAME,JOB,SAL
FROM EMP E FULL OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)

            CORSS JOIN
                        SELECT DNAME,ENAME,JOB,SAL
FROM EMP
CROSS JOIN DEPT
           


Sub-Query

            SINGLE ROW SUBQUERY
            SELECT * FROM EMP
            WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME = ‘SMITH’);


            MULTIPLE ROW SUBQUERY (IN)
            SELECT * FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10)

MULTIPLE ROW SUBQUERY (ANY)
SELECT * FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10)

MULTIPLE ROW SUBQUERY (ALL)
SELECT * FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10)

Set Operators
            UNION(returns both queries after eliminating duplications)
            UNION ALL (returns results from both queries, including all duplications)
            INTERSECT(returns rows that are common to both queries)
            MINUS(returns rows in the first query that are not present in second query)

Views

            CREATE VIEW ORG_EMP_V AS
            SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
            B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
            FROM EMP A,EMP B
            WHERE A.EMPNO = B.MGR;

Sequence

            CREATE SEQUENCE SEQ_EMP1
            START WITH 1
            INCREMENT BY 1
            NOCACHE;

            CREATE SEQJUENCE SEQ_EMP2
            START WITH 100
            INCREMENT BY 1
            MINVALUE 90
            MAXVALUE 150
            CYCLE;

            SELECT SEQ_EMP1.NEXTVAL
FROM DUAL;

SELECT SEQ_EMP1.CURRVAL
FROM DUAL;

Synonym

            CREATE SYNONYM E FOR EMP;

Index

            CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);      

Constraints

            PRIMARY KEY
            UNIQUE
            NOT NULL
            FOREIGN KEY
            CHECK
           
            CREATE TABLE DEPT(
            DEPTNO NUMBER(10) PRIMARY KEY,
            DNAME VARCHAR2(100) NOT NULL);

            CREATE TABLE EMP(
            EMPNO NUMBER(10) PRIMARY KEY,
            ENAME VARCHAR2(100) NOT NULL,
            PHONE VARCHAR2(15) UNIQUE,
            SAL NUMBER(15,2),
            DEPTNO NUMBER(10),
            CHECK (SAL < 3000),
            FOREIGN KEY (DEPTNO) REFERENCES EMP);

Security

            CREATE ROLE R1 IDENTIFIED BY R1;
            GRANT SELECT ON EMP TO R1;
            GRANT UPDATE,DELETE ON EMP TO R1;
            GRANT R1 TO SCOTT;


PL/SQL NOTES

            Block
                        Declaration Section (Declare all variable, constants, cursors, exceptions)
                        Executable Section (All executable PL/SQL Commands, call other procedures,DML)
                        Exception Section  (Tran Run time errors and give your own messages)

Variable

                        PL/SQL Variable
                                    Scalar variable
                                    Composite variable

                        Declare
                                    X number(10);
                                    V_EMPNO EMP.EMPNO%TYPE;
                                    V_EMP EMP%ROWTYPE;
                        BEGIN
                                    SELECT *
                                    INTO V_EMP
                                    FROM EMP
                                    WHERE EMPNO = 7788;
                                    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
                        END;


            DML in PL/SQL

                        SELECT
                        INSERT
                        UPDATE
                        DELETE

                        Declare
                                    X number(10);
                                    V_EMPNO EMP.EMPNO%TYPE;
                                    V_EMP EMP%ROWTYPE;
                        BEGIN
                                    SELECT *
                                    INTO V_EMP
                                    FROM EMP
                                    WHERE EMPNO = 7788;
                                    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
                                   
INSERT INTO EMP1 (EMPNO,ENAME,JOB,SAL)
VALUES (V_EMP.EMPNO,V_EMP.ENAME,V_EMP.JOB,V_EMP.SAL);
                                   
                                    UPDATE EMP1
                                    SET SAL = V_EMP.SAL
                                    WHERE EMPNO = V_EMP.EMPNO;
                                   
                                    DELETE FROM EMP1
                                    WHERE DEPTNO = V_EMP.DEPTNO;

            END;



Flow Control

                        If condition

                                    DECLARE
                                                V_EMP  EMP%ROWTYPE;
                                    BEGIN
                                                SELECT *
                                                INTO V_EMP
                                                FROM EMP
                                                WHERE ENAME = ‘SCOTT’;
                                   
                                                IF V_EMP.SAL < 3000 THEN
                                                            DBMS_OUTPUT.PUT_LINE(‘A’);
                                                ELSIF V_EMP.SAL < 2000 THEN
                                                            DBMS_OUTPUT.PUT_LINE(‘B’);
                                                ELSIF V_EMP.SAL < 1000 THEN
                                                            DBMS_OUTPUT.PUT_LINE(‘C’);
                                                ELSE
                                                            DBMS_OUTPUT.PUT_LINE(‘D’);
                                                END IF;
                                    END;

            Basic loop

                                    DECLARE
                                                X NUMBER := 1;
                                    BEGIN
                                                LOOP
                                                            X := X +1 ;
                                                            DBMS_OUTPUT.PUT_LINE(X);
                                                            EXIT WHEN X = 10;
                                                END LOOP;
                                    END;
                                               

                        While loop

                                    DECLARE
                                                X NUMBER := 1;
                                    BEGIN
                                                WHILE X < 10 LOOP
                                                            X := X +1;
                                                            DBMS_OUTPUT.PUT_LINE(X);
                                                END LOOP;
                                    END;

                        For loop

                                    BEGIN
                                                FOR I IN 1..10 LOOP
                                                            DBMS_OUTPUT.PUT_LINE(I):
                                                END LOOP;
                                    END;


Exceptions
            DECLARE
                        V_EMP EMP%ROWTYPE;
            BEGIN
                        SELECT *
                        INTO V_EMP
                        FROM EMP
WHERE DEPTNO = 1;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
            EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                                    DBMS_OUTPUT.PUT_LINE(‘SORRY, NO DATA FOUND’);
                        WHEN TOO_MANY_ROWS THEN
                                    DBMS_OUTPUT.PUT_LINE(‘SORRY, MANY RECORDS FOUND’);
            END;
                                   


Cursor
            DECLARE
                        V_EMP EMP%ROWTYPE;
                        CURSOR C_EMP IS           
                        SELECT *
                        INTO V_EMP
                        FROM EMP
            WHERE DEPTNO = 10;
            BEGIN
                        OPEN C_EMP;
                        LOOP
                                    FETCH C_EMP INTO V_EMP;
                                    EXIT WHEN C_EMP%NOTFOUND;
                                    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
                        END LOOP;
            END;




Cursor for Loop
            BEGIN
                        FOR I IN (SELECT * FROM EMP) LOOP
                                    DBMS_OUTPUT.PUT_LINE(I.ENAME);
                        END LOOP;
            END;





Procedures

            IN PARAMETER

            CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO NUMBER)
            IS
                        V_ENAME EMP.ENAME%TYPE;
            BEGIN
                        SELECT ENAME
                        INTO V_ENAME
                        FROM EMP
                        WHERE EMPNO = P_EMPNO;
                        DBMS_OUTPUT.PUT_LINE(V_ENAME);
            END;

            Call Procedure
            BEGIN
                        P_EMP(100);
END;

            OUT PARAMETER
            CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO IN NUMBER, P_ENAME OUT VARCHAR2)
            IS
            BEGIN
                        SELECT ENAME
                        INTO P_ENAME
                        FROM EMP
                        WHERE EMPNO = P_EMPNO;
            END;


            Call Procedure
            DECLARE
                        X NUMBER;
            BEGIN
                        P_EMP(100,X);
END;

            IN OUT PARAMETER
            CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO_SAL  IN  OUT NUMBER)
            IS
            BEGIN
                        SELECT SAL
                        INTO P_EMPNO_SAL
                        FROM EMP
                        WHERE EMPNO = P_EMPNO_SAL;
            END;

            DECLARE
                        X NUMBER := 100;
            BEGIN
                        P_EMP(X);
END;


Functions
            CREATE OR REPLACE FUNCTION F_EMP(P_EMPNO IN NUMBER)
            RETURN VARCHAR2
            IS
                        V_EMP EMP%ROWTYPE;
            BEGIN
                        SELECT ENAME
                        INTO P_ENAME
                        FROM EMP
                        WHERE EMPNO = P_EMPNO;
            END;


CALL FUNCTION
            SELECT F_EMP(7788) FROM DUAL;

TRIGGER
            CREATE OR REPLACE TRIGGER INS_EMP
            AFTER INSERT OR DELETE OR UPDATE OF SAL ON EMP
            REFERENCES NEW AS N OLD AS O
            FOR EACH ROW
            BEGIN
                        IF INSERTING THEN
                        INSERT INTO EMP1(EMPNO,ENAME,JOB)
                                    VALUES  (:               
                        ELSIF UPDATING THEN
                                    UPDATE EMP1
                                    SET SAL = :N.SAL
                                    WHERE EMPNO = :O.EMPNO;
                        ELSIF DELETING THEN
                                    DELETE FROM EMP
                                    WHERE EMPNO = :O.EMPNO;
                        END IF;
            END;
           

No comments: