Oracle_

Document Sample
Oracle_ Powered By Docstoc
					Oracle 10g PPT


            Malli
    Topics
•   Database Introduction    •   Sub Quarries
•    Data Types              •   Locks
•   SQL Statements           •   Views
•   SQL Functions.           •   Materialized Views
•   Case and Decode          •   Index
    functions.               •   Sequence
•   Analytical functions .   •   Synonym
•   Set Operators.           •   Cluster
•   Constraints.             •   Advance SQL.
•   Joins
Day- 1                        Day- 4

1) Database Introduction.     10) Sub Queries.
2) Data Types                 11) Locks
3) SQL Statements.            12) Views
4) SQL Functions              13) Materialized Views.

Day- 2                        Day- 5

5) Case & Decode Functions.   14) Index.
6) Analytical Functions       15) Synonym.
7) Set Operators              16) Sequence.
                              17) Clusters.
Day- 3
                              Day- 6
8) Constraints.
9) Joins                      18) Advanced SQL.
Database Introduction
Data

Information
Data Base

Collection Of related information stored at
one location
     Data Base Management System
                (DBMS)

• Software that controls the organization,
  storage, retrieval, security and integrity of
  data in a database.
• It accepts requests from the application and
  instructs the operating system to transfer the
  appropriate data.
• (Examples: - FoxPro, IMS, Sybase,
  Foxpro )
Relational Database Management system
               (RDBMS )

• Software that controls the organization,
storage, retrieval, security and integrity of data
in a relational database
• It accepts requests from the application and
instructs the operating system to transfer the
appropriate data.
•Example :- Oracle.
   Difference Between DBMS & RDBMS

        DBMS                           RDBMS
1) Database management             1) Relational Database
   System                             management System
2) No relation between the         2) Relation between the tables
   tables                          3) Support Multi user
3) Support Single user             4) Support Client Server
4) No client Server Architecture      Architecture
   5) Supports Small volumes          5) Supports Large volumes of
   of data.                           data.
6) Supports 3 CODD Rules.          6) Supports 8 CODD Rules.
7) No Normalization process        7) Support Normalization,NULL
8) Examples: - FoxPro, IMS,           & Constraints
   Sybase, Foxpro.                 8) Examples: - Oracle,SQL
                                      Server.
    SQL
•   Structure Query language.
•   Interface between USER and DATABASE.
•   Developed by IBM in 1970’s by Dr. E F Codd’s based on Relational Model.
•   Supports with set of commands for managing RDBMS
•   According to ANSII, it is the Stander language for all RDBMS App..
•   Developed using 90% C language and 10 % Low Level Language.
Data Types
Data Types

    NUMBER ( P,S )
•   Stores zero, positive, and negative numbers, fixed or floating-point numbers
•   The precision p can range from 1 to 38.
•   The scale s can range from -84 to 127

    VARCHAR( S )
•   Support both character and numbers
•   Variable length Character
•   Range from 1 to 4000 bytes.

    CHAR( S)
•   Support only character.
•   Fixed length Character
•   Range from 1 to 2000 bytes.

    DATE ( ‘DD-MON-YY’)
•   Support data Values
•   Default Date format is DD-MON-YY / DD-MON-YYYY
•   Range from January 1, 4712 BC to December 31, 9999 AD.
Data Types

Long (Size)
•   A bigger version the VARCHAR2 Data type
•   A table can contain multiple LOB columns but only one LONG column
•   A table containing one or more LOB columns can be partitioned, but a table containing a LONG
    column cannot be partitioned


CLOB (Size)
•   Character Large Object
•   Supports 4Gigabytes


BLOB (Size)
•   Binary Large Object
•   Supports 4Gigabytes


NCLOB (Size)
•   National Character Large Object
•   Supports 4Gigabytes
Data Types

BFILES
• Pointer to binary file on disk
• Supports 4Gigabytes

XMLType
• Support XML data
• Supports 4Gigabytes

RAW
• Support Raw binary data of length size bytes.
Maximum size is 2000 bytes
SQL Statements
DDL - Data Definition Language
    CREATE       : to create a new data structure.
    ALTER        : to change an existing data structure.
    DROP          : to remove an entire data structure.
    TRUNCATE : to remove all rows permanently from table
    RENAME : to rename existing table
DML - Data Manipulation Language
    INSERT       : to add records into the table
    UPDATE       : to change column value in the table
    DELETE         : to remove rows from the table
DQL - Data Query Language
    SELECT       : used to retrieve the data present in the database tables
DCL - Data Control Language
    GRANT         :Allow access privileges to users
    REVOKE         :Revoke or cancel access privileges
TCL-Transaction Control Language.
    COMMIT :Save or enable DML changes to the database.
    ROLLBACK : To undo DML changes till in a transaction
    SAVEPOINT: To divide a transaction
                            DDL

    DDL - Data Definition Language
        - Auto commit Statements.

Commands :-
   CREATE   : to create a new data structure.
   ALTER    : to change an existing data structure.
   DROP     : to remove an entire data structure.
   TRUNCATE : to remove all rows permanently from table
   RENAME : to rename existing table
CREATE
                Table

Sno   Sname       DBO        Class

1     Ramu    11-Jun-1999   First
2     Gopi    21-Jul-1999   First
3     Ravi    17-Mar-1993   Seventh
4     Raju    03-Aug-1995 Fifth
Syntax

SQL> Create Table <T_Name>
            ( Col_name Datatype(Leng),
              Col_name Datatype(Leng),
              Col_name Datatype(Leng)
   );

SQL> Create Table Student
     ( Sno number(4),
       Sname Varchar(20),
       DBO Date,
       Class Char(25)
     );
    Examples
• SQL> Create table Stud as select * from Student;
• SQL> Create table Stud as select * from Student where 1=2;
• SQL> Create table Stud as select * from Student where Class=‘First’
• SQL> Create table Stud as select * from Student order by Class;
• SQL> Create table Stud as select Sno, Sname, Clas, Addr from Student
• SQL> Create table Stud as select * from Student;
   SQL> Create table T_emp as select eno, sum(sal) as T_sal from emp
                                        Group by eno;
  SQL> Create table T_emp as
•    Select empno,b.deptno Dept_no,Dname,Ename,Sal
•                           from emp a,dept b
•                              Where a.deptno=b.deptno;
ALTER
    Example

•   SQL> Alter table Stud ADD pin varchar(5);
•   SQL> Alter table Stud ADD (pin varchar(5));
•   SQL> Alter table Stud ADD (pin varchar(5) , C_id Number(4));
•   SQL> Alter table Stud DROP (pin) ;
•   SQL> Alter table Stud DROP (pin,C_id) ;
•   SQL> Alter table Stud DROP pin varchar(5) ;--------ERROR
•   SQL> Alter table Stud DROP (pin varchar(5) ) ;-----ERROR
•   SQL> Alter table Stud DROP COLUMN (pin,C_id) ;
•   Alter table Stud DROP COLUMN pin ;
•   SQL> Alter table Stud MODIFY pin,C varchar(5);
•   SQL> Alter table Stud MODIFY pin,C varchar(3);
•   SQL> Alter table Stud MODIFY pin,C Number(3);
•   SQL> Alter table Stud MODIFY pin,C varchar(3);
DROP
Example


• SQL>Drop table <Table_Name> ;
• SQL>Drop table Stud ;
• SQL>Drop table Emp;
TRUNCATE
Example


• SQL> Truncate table <Table_Name> ;
• SQL> Truncate table Stud ;
• SQL> Truncate table Emp;
RENAME
Example


• SQL> Rename <Table_Name> to <New_Table_name> ;
• SQL> Rename Emp to Employee ;
• SQL> Rename Dept to Department ;
                          DML
DML - Data Manipulation Language
    - We need to commit explicitly.

Commands :-
INSERT      : to add records into the table
UPDATE      : to change column value in the table
DELETE      : to remove rows from the table
INSERT
    Example

•   SQL> insert into Dept values (1,’Ramu’,’a’);
•   SQL> insert into Dept (deptno,dname) values (2,’Gopi’);
•   SQL> insert into Dept (1,2) values (2,’Y’);
•   SQL> insert into Dept values(&deptno,’&Dname’,’&loc’)
           Enter the Value for Deptno: 3
           Enter the Value for Dname: Ravi
           Enter the Value for Dname: Hyd
• SQL> insert into Dept (deptno,dname) values (&deptno,’&Dname’);
• SQL> Insert into T_emp select * from Student;
• SQL> Insert into T_emp select * from Student where Class=’X1’
• SQL> Insert into T_emp select * from Student order by Class;
• SQL> Insert into T_emp select Sno, Sname, Slas, Addr from
  Student
• SQL> Insert into T_emp select Deptno, sum(sal) as T_sal from emp
  Group by Deptno;
• SQL> Insert into T_emp (eno, ename) select eno, ename from emp
  ;
UPDATE
Example :-

SQL> Update emp set sal=100;
SQL> Update emp set sal=100 , ename =’Raj’ ;
SQL> Update emp set sal=100 where Deptno=10;
SQL> Update emp set sal=100 where Deptno =10 OR Deptno=20;
SQL> Update emp set sal=100 where Deptno =10 AND Deptno=20; --Error
SQL> Update emp set sal=100 where Deptno In (10,20);
DELETE
Example :-

SQL> Delete   From Emp ;
SQL> Delete   From Emp where Deptno=10;
SQL> Delete   From Emp where Deptno =10 OR Deptno=20;
SQL> Delete   From Emp where Deptno =10 AND Deptno=20;
SQL> Delete   From Emp where Deptno In (10,20);
SELECT
Syntax :   SELECT (column_list )
           FROM     (table_list )
           WHERE (row restriction)
           GROUP BY (attribute names)
           HAVING (group restriction)
           ORDER BY (attribute name or names)
    Example :-

•   SQL> Select * From EMP;
•   SQL> Select * From EMP where Deptno=10;
•   SQL> Select EMPNO, ENAME, SAL From EMP where Deptno=10;
•   SQL> Select * From EMP where Deptno=10;
•   SQL> Select * From EMP where Deptno <> 10;
•   SQL> Select * From EMP where Deptno =10 OR Deptno=20;
•   SQL> Select * From EMP where Deptno =10 AND Deptno=20;
•   SQL> Select * From EMP where Deptno In (10,20);
•   SQL> Select * From EMP where Deptno Not in (10,20);
•   SQL> Select * From EMP where Comm IS NULL;
•   SQL> Select * From EMP where Comm IS NOT NULL;
•   SQL> Select * From EMP where Sal BETWEEN 1000 AND 3000;
•   SQL> Select * From EMP where Ename LIKE ‘S’ ;
•   SQL> Select * From EMP where Ename LIKE ‘S%’
•   SQL> Select * From EMP where Ename LIKE ‘%S’
•   SQL> Select * From EMP where Ename LIKE ‘%S%’
                          DCL

DCL - Data Control Language
    - Used to Control the information between Users.

GRANT        :Allow access privileges to users
REVOKE        :Revoke or cancel access privileges
DCL

      GRANT

      Used to grant the permissions on the Database Object to the another
         user.


      Permissions ;-

      •   Select
      •   Insert
      •   Update
      •   Delete
      •   All
  Example :-
SQL> Conn Scott / tiger@orcl;
SQL> Show User;
      Scott
SQL> Grant All      On emp to User1;
SQL> Grant Select On emp to User1;
SQL> Grant Insert On emp to User1;
SQL> Grant Update On emp to User1;
SQL> Grant Delete On emp to User1;
SQL> Grant Select, Insert On emp to User1;
SQL> Grant Select On emp to PUBLIC;
SQL> Grant All On emp to User1 WITH GRANT OPTION;

SQL> Conn User1 / User1@Orcl
SQL> Show User;
     User1
SQL> Select * from Scott.Emp;
SQL> Grant Select, Insert On Scott.Emp to User2;
DCL
      Revoke
      Used to revoke the permissions on the Database Object from the users.


      Permissions ;-

      •   Select
      •   Insert
      •   Update
      •   Delete
      •   All
Example :-

     Revoke :-

    SQL> Conn Scott / tiger@orcl;
    SQL> Show User;
          Scott
    SQL> Revoke All from Emp from User1;
    SQL> Revoke Select from Emp from User1;
    SQL> Revoke Insert from Emp from User1;
    SQL> Revoke Update from Emp from User1;
    SQL> Revoke Delete from Emp from User1;
    SQL> Revoke Insert,Update from Emp from User1;
    SQL> Revoke All from Emp from PUBLIC;
Sharing a Column :-

    Supports Only in Insert and Delete.

    SQL> Grant Insert (Empno,Ename) On emp to User1;
    SQL> Grant Delete ( Empno,Sal) On emp to User1;
    SQL> Grant Delete ( Empno,Sal) On emp to PUBLIC;
    SQL> Grant Delete ( Empno,Sal) On emp to User1 WITH GRANT
      OPTION;
DCL

•   At a time we will give permission on One Table.
•   If parent lost its privileges then Child will also loss the Privileges.
•   Parent User can Revoke the Privileges from Childs and Grand Childs.
Oracle Functions
                   Functions

Input                                     Output
                       Function

   arg 1               Function
                    performs action
        arg 2
                                      Result
                                      value

           arg n
                        Numeric Functions

1.    ABS (+- N )
2.    SQRT ( N )
3.    POWER ( M , N )
4.    MOD ( M , N )
5.    SIGN( N )
6.    SIN ( N )
7.    COS ( N )
8.    TAN ( N )
9.    LOG ( M , N )
10.   LN ( N )
11.   ROUND ( M , N )
12.   TRUNC ( M , N )
13.   CELL ( N )
14.   FLOOR ( N )
                             ABS



SQL> SELECT ABS(-7872) from dual;

•   Eg: ABS(-7872)
        ----------
       7872


SQL> SELECT ABS(7872) from dual;

•   Eg: ABS(7872)
     ----------
    7872
                               SQRT



•   SQL> select SQRT(4) from dual;

    SQRT(4)
    ----------
      2

•   SQL> select SQRT(2) from dual;

    SQRT(2)
    ----------
    1.41421356
                                            Power and Mode
         ---------------------------------------------------------------------------------------------------------------------
                                                      ---- POWER ----

•    SQL> select POWER(2,3) from dual;

    POWER(2,3)
    ----------
       8
---------------------------------------------------------------------------------------------------------------------
                                                        ----     MOD ----

•    SQL> select MOD(4,2) from dual;

    MOD(4,2)
    ----------
        0

•    SQL> select MOD(3,2) from dual;

     MOD(3,2)
     ----------
       1
---------------------------------------------------------------------------------------------------------------------
                            SIGN

•   SQL> SELECT SIGN(0) FROM DUAL;

  SIGN(0)
---------------
  0

•   SQL> SELECT SIGN(-23423) FROM DUAL;

SIGN(-23423)
------------
        -1

•   SQL> SELECT SIGN(23423) FROM DUAL;

SIGN(23423)
-----------
        1
                            SIN,COS,TAN
                              --------SIN-------

•     SQL> SELECT SIN(90) FROM DUAL;

     SIN(90)
     ----------
    .893996664
                                -------COS------

•     SQL> SELECT COS(90) FROM DUAL;

    COS(90)
    ----------
    -.44807362
                               ---------TAN-------

•     SQL> SELECT TAN(90) FROM DUAL;

 TAN(90)
 ----------
-1.9952004
                            LOG


•   SQL> SELECT LOG(4,2) FROM DUAL;

  LOG(4,2)
----------
       .5

•   SQL> SELECT LN(4) FROM DUAL;

    LN(4)
----------
1.38629436
                           ROUND


•   SQL> SELECT ROUND(19) FROM DUAL;

    ROUND(19)
      ----------
     19
•   SQL> SELECT ROUND(19.4) FROM DUAL;

    ROUND(19.4)
    -----------
       19
•   SQL> SELECT ROUND(19.5) FROM DUAL;

    ROUND(19.5)
    -----------
       20

                                         Conti….
                          ROUND
•   Conti….

•   SQL> SELECT ROUND(19.254,2) FROM DUAL;

    ROUND(19.254,2)
    ---------------
        19.25

•   SQL> SELECT ROUND(19.255,2) FROM DUAL;

    ROUND(19.255,2)
    ---------------
       19.26

•   SQL> SELECT ROUND(19.256,2) FROM DUAL;

     ROUND(19.256,2)
     ---------------
        19.26
                             TRUNC

•   SQL> SELECT TRUNC(19) FROM DUAL;

 TRUNC(19)
----------
       19

•   SQL> SELECT TRUNC(19.4) FROM DUAL;

TRUNC(19.4)
-----------
       19

•   SQL> SELECT TRUNC(19.5) FROM DUAL;

TRUNC(19.5)
-----------
       19
                                         Conti…
                              TRUNC
Conti…

•   SQL> SELECT TRUNC(19.124,2) FROM DUAL;

TRUNC(19.124,2)
---------------
        19.12

•   SQL> SELECT TRUNC(19.125,2) FROM DUAL;

TRUNC(19.125,2)
---------------
        19.12

•   SQL> SELECT TRUNC(19.126,2) FROM DUAL;

TRUNC(19.126,2)
---------------
        19.12
                              CEIL

•   SQL> SELECT CEIL(19) FROM DUAL;

  CEIL(19)
----------
       19

•   SQL> SELECT CEIL(19.5) FROM DUAL;

CEIL(19.5)
----------
       20

•   SQL> SELECT CEIL(19.2) FROM DUAL;

CEIL(19.2)
----------
       20

•   SQL> SELECT CEIL(19.6) FROM DUAL;

CEIL(19.6)
----------
       20
                                  FLOOR

•    SQL> SELECT FLOOR(19) FROM DUAL;

    FLOOR(19)
    ----------
     19

•    SQL> SELECT FLOOR(19.2) FROM DUAL;

    FLOOR(19.2)
    -----------
       19

•    SQL> SELECT FLOOR(19.5) FROM DUAL;

     FLOOR(19.5)
     -----------
      19

•    SQL> SELECT FLOOR(19.6) FROM DUAL;

    FLOOR(19.6)
    -----------
      19
      Character Functions
                    Character
                    functions



Case-manipulation          Character-manipulation
    functions                    functions
     LOWER                       CONCAT
     UPPER                       SUBSTR
     INITCAP                     LENGTH
                                 INSTR
                                 LPAD | RPAD
                                 LTRIM | RTRIM
                                 REPLACE
                                 TRANSLATE
                       Character Functions

1. UPPER ( S )
2. LOWER ( S )
3. INITCAP ( S )
4. LENGTH ( S )
5. REVERSE ( S )
6. CONCAT ( S1 , S2 )
7. LPAD( S , C, L)
8. RPAD( S , C, L)
9. TRIM( S )
10. LTRIM( S ,C )
11. RTRIM ( S , C )
12. REVERSE( S )
13. TRANSLATE( S ,C)
14. ASCII( S )
15. CHR ( N )
16. VSIZE ( S )
17. SUBSTR( S ,F,T)
18. INSTR ( S , C , S, P )
        Department Table


DEPT NO            DNAME            LOC
-------------   -------------- -------------
10              ACCOUNTING        NEWYORK
 20             RESEARCH          DALLAS
 30             SALES            CHICAGO
 40             OPERATIONS        BOSTON
                    Employee Table

ENO ENAME     JOB         MGR     H-DATE    SAL     COMM     DEPTNO


7369 SMITH    CLERK       7902 17-DEC-80    800                20
7499 ALLEN    SALESMAN    7698 20-FEB-81    1600      300      30
7521 WARD     SALESMAN    7698 22-FEB-81    1250      500     30
7566 JONES    MANAGER      7839 02-APR-81   2975              20
7654 MARTIN   SALESMAN    7698 28-SEP-81    1250      1400    30
7698 BLAKE    MANAGER     7839 01-MAY-81     2850             30
7782 CLARK    MANAGER      7839 09-JUN-81    2450             10
7788 SCOTT     ANALYST    7566 19-APR-87     3000              20
7839 KING     PRESIDENT        17-NOV-81     5000             10
7844 TURNER   SALESMAN    7698 08-SEP-81    1500       0       30
7876 ADAMS    CLERK       7788 23-MAY-87    1100              20
7900 JAMES    CLERK       7698 03-DEC-81    950               30
7902 FORD      ANALYST    7566 03-DEC-81    3000              20
7934 MILLER   CLERK       7782 23-JAN-82    1300              10
                                                 Examples
•   SQL> SELECT ENAME,LOWER(ENAME) LOWER,
                      UPPER(ENAME) UPPER,
                     INITCAP(ENAME) INIT
                                         FROM EMP;

ENAME        LOWER UPPER              INIT
----------   ---------- ----------  ----------
SMITH        smith       SMITH       Smith
ALLEN        allen      ALLEN        Allen
WARD          ward      WARD        Ward
JONES         jones JONES           Jones
MARTIN         martin MARTIN        Martin
BLAKE         blake     BLAKE         Blake
CLARK         clark      CLARK        Clark
SCOTT         scott     SCOTT        Scott
KING         king       KING        King
TURNER       turner TURNER Turner
ADAMS         adams ADAMS            Adams
JAMES         james JAMES          James
FORD          ford     FORD        Ford
MILLER         miller MILLER       Miller
                            REVERSE
•   SQL> SELECT ENAME, REVERSE(ENAME) FROM EMP;


ENAME         REVERSE(EN
----------     ----------
SMITH        HTIMS
ALLEN        NELLA
WARD         DRAW
JONES        SENOJ
MARTIN        NITRAM
BLAKE        EKALB
CLARK         KRALC
SCOTT        TTOCS
KING          GNIK
TURNER        RENRUT
ADAMS         SMADA
JAMES        SEMAJ
FORD         DROF
MILLER       RELLIM
SQL> SELECT ENAME, LENGTH(ENAME)LEN_ENAME,
            DEPTNO, LENGTH(DEPTNO) LEN_DNO
                             FROM EMP;

ENAME      LEN_ENAME    DEPTNO            LEN_DNO
         ---------- -   ---------------- -------------
SMITH        5               20           2
ALLEN         5               30          2
WARD          4              30           2
JONES         5              20           2
MARTIN        6              30           2
BLAKE         5              30           2
CLARK         5             10             2
SCOTT         5             20            2
KING           4            10            2
TURNER         6            30            2
ADAMS          5             20           2
JAMES         5             30             2
FORD          4             20            2
MILLER        6             10             2
                                    CONCAT
SQL> select ename,sal, concat(ename,sal) CONC from emp;

ENAME           SAL                CONC
----------    ----------   --------------------
SMITH          800            SMITH800
ALLEN        1600             ALLEN1600
WARD         1250             WARD1250
JONES         2975            JONES2975
MARTIN         1250           MARTIN1250
BLAKE         2850            BLAKE2850
CLARK         2450            CLARK2450
SCOTT         3000            SCOTT3000
KING         5000             KING5000
TURNER       1500             TURNER1500
ADAMS         1100            ADAMS1100
JAMES          950            JAMES950
FORD         3000             FORD3000
MILLER        1300            MILLER1300
                                              PADDING
SQL> SELECT ENAME,LPAD (ENAME,15,'@') L_PAD ,
                  RPAD (ENAME,15,'@') R_PAD
                                         FROM EMP;

ENAME          L_PAD                                          R_PAD
----------    ------------------------------------   ------------------------- -
SMITH        @@@@@@@@@@SMITH                         SMITH@@@@@@@@@@
ALLEN        @@@@@@@@@@ALLEN                         ALLEN@@@@@@@@@@
WARD         @@@@@@@@@@@WARD                         WARD@@@@@@@@@@@
JONES        @@@@@@@@@@JONES                         JONES@@@@@@@@@@
MARTIN       @@@@@@@@@MARTIN                         MARTIN@@@@@@@@@
BLAKE        @@@@@@@@@@BLAKE                         BLAKE@@@@@@@@@@
CLARK        @@@@@@@@@@CLARK                         CLARK@@@@@@@@@@
SCOTT        @@@@@@@@@@SCOTT                         SCOTT@@@@@@@@@@
KING         @@@@@@@@@@@KING                         KING@@@@@@@@@@@
TURNER        @@@@@@@@@TURNER                        TURNER@@@@@@@@@
ADAMS        @@@@@@@@@@ADAMS                         ADAMS@@@@@@@@@@
JAMES        @@@@@@@@@@JAMES                         JAMES@@@@@@@@@@
                              TRIM

SQL> select ename, trim(ename) from emp;

ENAME         TRIM(ENAME
----------   ----------
SMITH        SMITH
ALLEN         ALLEN
WARD          WARD
JONES         JONES
MARTIN        MARTIN
BLAKE         BLAKE
CLARK        CLARK
SCOTT         SCOTT
KING          KING
TURNER        TURNER
ADAMS         ADAMS
JAMES        JAMES
FORD         FORD
MILLER       MILLER
                            Left and Right TRIM

Left Trim:                            Right Trim:

SQL> select ename, ltrim(ename)       SQL> select ename,rtrim(ename)
  from emp;                             from emp;

                                      ENAME           RTRIM(ENAM
ENAME         LTRIM(ENAM              ---------- -    ---------
----------     ----------             SMITH          SMITH
SMITH        SMITH                    ALLEN          ALLEN
ALLEN        ALLEN                    WARD           WARD
WARD         WARD                     JONES          JONES
JONES        JONES                    MARTIN          MARTIN
MARTIN        MARTIN                  BLAKE           BLAKE
BLAKE         BLAKE                   CLARK           CLARK
CLARK         CLARK                   SCOTT          SCOTT
SCOTT        SCOTT                    KING            KING
KING         KING                     TURNER          TURNER
TURNER       TURNER                   ADAMS           ADAMS
ADAMS        ADAMS
                            Left and Right TRIM
Left trim:                            Right Trim:

•    SQL> select ename                SQL> select ename
     ,ltrim(ename,'S') from emp;        ,rtrim(ename,'R') from emp;

ENAME          LTRIM(ENAM             ENAME         RTRIM(ENAM
---------- -   ---------              ----------     ----------
SMITH          MITH                   SMITH        SMITH
ALLEN          ALLEN                  ALLEN        ALLEN
WARD           WARD                   WARD         WARD
JONES          JONES
                                      JONES        JONES
MARTIN         MARTIN
BLAKE          BLAKE                  MARTIN        MARTIN
CLARK          CLARK                  BLAKE        BLAKE
SCOTT          COTT                   CLARK         CLARK
KING           KING                   SCOTT        SCOTT
TURNER         TURNER                 KING          KING
ADAMS           ADAMS                 TURNER        TURNE
JAMES          JAMES
                                      ADAMS         ADAMS
FORD           FORD
MILLER         MILLER                 JAMES        JAMES
                                      FORD         FORD
                                      MILLER       MILLE
                          REPLACE
•   SQL> select ename,                 •   SQL> select ename,
    replace(ename,'A','a') from emp;       replace(ename,'A','') from emp;

ENAME        REPLACE(EN                ENAME         REPLACE(EN
----------   ----------                ----------   ----------
SMITH      SMITH                       SMITH        SMITH
ALLEN       aLLEN                      ALLEN        LLEN
WARD        WaRD                       WARD         WRD
JONES       JONES                      JONES        JONES
MARTIN MaRTIN                          MARTIN        MRTIN
BLAKE       BLaKE                      BLAKE         BLKE
CLARK       CLaRK                      CLARK         CLRK
SCOTT       SCOTT                      SCOTT        SCOTT
KING       KING                        KING         KING
TURNER TURNER                          TURNER        TURNER
ADAMS        aDaMS                     ADAMS         DMS
JAMES       JaMES                      JAMES         JMES
FORD       FORD                        FORD         FORD
MILLER MILLER                          MILLER        MILLER
                            REPLACE

•   SQL> select ename, replace(ename,'A','') from emp;

ENAME         REPLACE(EN
----------     ----------
SMITH        SMITH
ALLEN        LLEN
WARD         WRD
JONES        JONES
MARTIN        MRTIN
BLAKE         BLKE
CLARK         CLRK
SCOTT        SCOTT
KING         KING
TURNER       TURNER
ADAMS         DMS
JAMES         JMES
FORD         FORD
MILLER        MILLER
                             TRANSLATE

•   SQL> select ename,                  •   SQL> select ename,
    replace(ename,'AL','a') from emp;       translate(ename,'AL','a') from
                                            emp;
ENAME           REPLACE(EN
---------- ----------                   ENAME        TRANSLATE(
SMITH          SMITH                    ----------  ----------
ALLEN          aLEN                     SMITH      SMITH
WARD           WARD                     ALLEN       aEN
JONES          JONES                    WARD        WaRD
MARTIN MARTIN                           JONES       JONES
BLAKE           BLAKE                   MARTIN MaRTIN
CLARK           CLARK                   BLAKE       BaKE
SCOTT          SCOTT                    CLARK        CaRK
KING          KING                      SCOTT       SCOTT
TURNER TURNER                           KING       KING
ADAMS           ADAMS                   TURNER TURNER
JAMES           JAMES                   ADAMS        aDaMS
FORD           FORD                     JAMES       JaMES
MILLER MILLER                           FORD       FORD
14 rows selected.
MILLER MIER
SQL> select ASCII('a') from dual;

ASCII('A')
----------
       97
SQL> select ASCII('A') from dual;

ASCII('A')
----------
       65
SQL> select CHR(97) from dual;

C
-
a

SQL> select CHR(65) from dual;

C
-
A
SQL> Select ename,length(ename)      SQL> select comm, length(ename),
  Len,vsize(Ename) Siz from emp;       vsize(ename) from emp;

ENAME          LEN         SIZ       COMM        LENGTH(ENAME) VSIZE(ENAME)
----------   ---------- ----------   ---------- -------------   ------------
SMITH          5            5                           5          5
ALLEN          5            5              300          5          5
WARD           4           4               500          4          4
JONES          5           5                            5          5
MARTIN          6          6              1400          6          6
BLAKE           5           5                           5          5
CLARK           5           5                           5          5
SCOTT           5          5                            5          5
KING            4          4                            4          4
TURNER         6           6                 0          6          6
ADAMS           5          5                            5          5
JAMES          5            5                           5          5
FORD           4           4                            4          4
MILLER         6           6                            6          6
SQL> desc emp;
Name                   Null?     Type
-------------------   --------   -------------
EMPNO                 NOT NULL    NUMBER(4)
ENAME                             VARCHAR2(10)
JOB                                VARCHAR2(9)
MGR                                NUMBER(4)
HIREDATE                           DATE
SAL                                NUMBER(7,2)
COMM                               NUMBER(7,2)
DEPTNO                              NUMBER(2)
                             SUBSTR

•   SQL> select SUBSTR('Rajesh',1,3) from dual;

SUB
---
Raj

•   SQL> select SUBSTR('Rajesh',3) from dual;

SUBS
----
Jesh

•   SQL> select SUBSTR('Rajesh',1) from dual;

SUBSTR
------
Rajesh
ADAMS  ADAM
JAMES  JAME
FORD   FORD
MILLER MILL
                                                SUBSTR Conti….
SQL> select ename,substr(ename,1,4) from emp;


ENAME           SUBS
---------- ----
SMITH           SMIT
ALLEN           ALLE
WARD             WARD
JONES           JONE
MARTIN MART
BLAKE            BLAK
CLARK            CLAR
SCOTT            SCOT
KING             KING
TURNER TURN
ADAMS           ADAM
JAMES           JAME
FORD            FORD
MILLER MILL
SQL> select instr('Database','a',1,1) from dual;

INSTR('DATABASE','A',1,1)
-------------------------
                    2

SQL> select instr('Database','A',1,1) from dual;

INSTR('DATABASE','A',1,1)
-------------------------
                    0

SQL> select instr('Database','a',1,2) from dual;

INSTR('DATABASE','A',1,2)
-------------------------
                    4
SQL> select instr('Database','a',1) from dual;

INSTR('DATABASE','A',1)
-----------------------
                  2
SQL> select instr('Database','a',2) from dual;
INSTR('DATABASE','A',2)
-----------------------
                  2
SQL> select instr('Database','a',3,1) from dual;
INSTR('DATABASE','A',3,1)
-------------------------
                    4
SQL> select instr('Database','a',3,2) from dual;
INSTR('DATABASE','A',3,2)
-------------------------
                    6
             Date Functions
Function          Description

MONTHS_BETWEEN    Number of months
                  between two dates
ADD_MONTHS        Add calendar months to
                  date
NEXT_DAY          Next day of the date
                  specified
LAST_DAY          Last day of the month
                  EXTRACT the particular
EXTRAC            value from the date
                  Truncate date
                 Date Functions



1) Sysdate
2) Months_between( D1 , D2)
3) Add_Months( D ,+/- N )
3) Last_day (D)
4) Next_day ( D , ‘Day’)
5) Extract
                                                    SYSDATE
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
14-JUL-07

SQL> SELECT CURRENT_TIMESTAMP FROM dual

CURRENT_TIMESTAMP
---------------------------------------
14-JUL-07 11.57.01.812000 AM +05:30

SQL> SELECT DBTIMEZONE FROM dual;

DBTIME
------
+00:00

SQL> SELECT SYSTIMESTAMP FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JUL-07 11.57.24.531000 AM +05:30
               Adding DAYS and MONTHS
            ADDING DAYS                     ADD_MONTHS

SQL> SELECT SYSDATE FROM DUAL;   SQL> SELECT ADD_MONTHS(SYSDATE,2)
                                    FROM DUAL;
SYSDATE                          ADD_MONTH
---------                        ---------
14-JUL-07                        14-SEP-07
                                 SQL> SELECT ADD_MONTHS(SYSDATE,-2)
SQL> SELECT SYSDATE + 10 FROM          FROM DUAL;
  DUAL;
                                 ADD_MONTH
SYSDATE+1                        ---------
                                 14-MAY-07
---------
                                 SQL> SELECT ADD_MONTHS(SYSDATE,1.9)
24-JUL-07                              FROM DUAL;

SQL> SELECT SYSDATE - 10 FROM    ADD_MONTH
  DUAL;                          ---------
                                 14-AUG-07
SYSDATE-1                        SQL> SELECT ADD_MONTHS(SYSDATE,0)
---------                              FROM DUAL;
                                 ADD_MONTH
04-JUL-07                        ---------
                                 14-JUL-07
                     LAST_DAY

•

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(
---------
31-JUL-07

SQL> SELECT LAST_DAY(ADD_MONTHS(SYSDATE,1)) FROM DUAL;

LAST_DAY(
---------
31-AUG-07
                      MONTHS_BETWEEN



SQL> SELECT MONTHS_BETWEEN(SYSDATE, '12-DEC-2007') FROM DUAL;

     // SOME TIMES IT WILL GIVE ERROR //



SQL> SELECT MONTHS_BETWEEN(SYSDATE,
                               TO_DATE('12-DEC-2007')) FROM DUAL;

MONTHS_BETWEEN(SYSDATE,TO_DATE('12-DEC-2007'))
----------------------------------------------
                               -4.918986
                       NEXT_DAY

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
14-JUL-07

SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

TO_CHAR(S
---------
SATURDAY

SQL> SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;

NEXT_DAY(
---------
16-JUL-07
                       NEXT_DAY

SQL> SELECT NEXT_DAY(SYSDATE,2) FROM DUAL;

NEXT_DAY(
---------
16-JUL-07

SQL> SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;

NEXT_DAY(          1--SUNDAY
---------          2-MONDAY
15-JUL-07

SQL> SELECT NEXT_DAY(SYSDATE,6) FROM DUAL;
NEXT_DAY(
---------
20-JUL-07
                        EXTRACT

SQL> SELECT EXTRACT(YEAR FROM DATE '2007-04-01') FROM dual;

EXTRACT(YEARFROMDATE'2007-04-01')
---------------------------------
                        2007

SQL> SELECT EXTRACT(MONTH FROM DATE '2007-04-01') FROM dual;

EXTRACT(MONTHFROMDATE'2007-04-01')
----------------------------------
                           4
SQL> SELECT EXTRACT(DAY FROM DATE '2007-04-01') FROM dual;

EXTRACT(DAYFROMDATE'2007-04-01')
--------------------------------
                          1
                       EXTRACT
SQL> SELECT EXTRACT(MINUTE FROM TO TIMESTAMP('01-JAN-2005
  19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS M FROM DUAL;

        M
----------
       15

SQL> SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2005
     19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS
  2 M FROM DUAL;
        M
----------
       19
SQL> SELECT EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2005
     19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS
  2 M FROM DUAL;
        M
----------
       26
                             GREATEST
SQL> SELECT GREATEST ('ANAND','anand') from dual;

GREAT
-----
anand

SQL> SELECT GREATEST('RAJESH','rajesh') from dual;

GREATE
------
rajesh

SQL> SELECT GREATEST(10,20,30,40) from dual;
GREATEST(10,20,30,40)
---------------------
                 40
 SQL> SELECT GREATEST(DEPTNO) FROM DEPT;
GREATEST(DEPTNO)
----------------
            10
            20
            30
            40
                               LEAST

SQL> SELECT LEAST('ANAND','anand') from dual;
LEAST
-----
ANAND

SQL> SELECT LEAST(10,20,30,40) FROM DUAL;
LEAST(10,20,30,40)
------------------
             10

SQL> SELECT LEAST(DNAME) FROM DEPT;
LEAST(DNAME)
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
Aggregate Functions
Aggregate Functions
    1.   MIN ( )
    2.   MAX ( )
    3.   SUM ( )
    4.   AVG ( )
    5.   COUNT ( * / Col )
    6.   STDDEV ( )
    7.   VARIANCE ( )
Aggregate Functions
SQL> select * from emp;

    EMPNO ENAME                 JOB               MGR HIREDATE                   SAL         COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------------------- ---------- --------- ------
        ---- ---------
     7369 SMITH             CLERK               7902 17-DEC-80                  800                   20
     7900 JAMES CLERK                           7698 03-DEC-81                  950                   30
     7876 ADAMS CLERK                           7788 23-MAY-87                1100                    20
     7521 WARD              SALESMAN 7698 22-FEB-81                           1250          500        30
     7654 MARTIN SALESMAN 7698 28-SEP-81                                      1250         1400       30
     7934 MILLER CLERK                          7782 23-JAN-82                  1300                  10
     7844 TURNER SALESMAN 7698 08-SEP-81                                       1500           0       30
     7499 ALLEN SALESMAN 7698 20-FEB-81                                        1600         300       30
     7782 CLARK MANAGER                          7839 09-JUN-81                 2450                   10
     7698 BLAKE MANAGER                          7839 01-MAY-81                2850                   30
     7566 JONES MANAGER                          7839 02-APR-81                2975                    20
     7788 SCOTT ANALYST                          7566 19-APR-87                3000                    20
     7902 FORD ANALYST                           7566 03-DEC-81                3000                    20
     7839 KING            PRESIDENT                       17-NOV-81            5000                    10
Aggregate Functions
SQL> select Min(sal) from emp;

  MIN(SAL)
----------
      800

SQL> Select Max(sal) from emp;

  MAX(SAL)
----------
     5000

SQL> Select Sum(sal) from emp;

  SUM(SAL)
----------
    29025

SQL> Select Sum(sal) from emp where deptno=10;

  SUM(SAL)
----------
     8750
Aggregate Functions
SQL> Select Avg(Sal) from emp;

  AVG(SAL)
----------
2073.21429

SQL> select Avg(Sal) from emp where deptno=10;

  AVG(SAL)
----------
2916.66667

SQL> select count(*) from emp;

  COUNT(*)
----------
       14

SQL> select count(comm) from emp;

COUNT(COMM)
---------------------
        4
Aggregate Functions

SQL> SELECT * FROM EMP ORDER BY DEPTNO;

    EMPNO ENAME                 JOB               MGR HIREDATE                   SAL         COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- --------- ---------- ---------- ----------
     7782 CLARK             MANAGER                7839        09-JUN-81            2450                     10
     7839 KING              PRESIDENT                         17-NOV-81            5000                     10
     7934 MILLER CLERK                             7782        23-JAN-82            1300                    10

    7566 JONES             MANAGER               7839        02-APR-81           2975                    20
    7902 FORD              ANALYST               7566        03-DEC-81           3000                    20
    7876 ADAMS             CLERK                 7788        23-MAY-87            1100                   20
    7369 SMITH             CLERK                  7902        17-DEC-80            800                   20
    7788 SCOTT             ANALYST               7566        19-APR-87            3000                   20

    7521 WARD              SALESMAN               7698        22-FEB-81            1250         500       30
    7844 TURNER            SALESMAN               7698        08-SEP-81            1500          0        30
    7499 ALLEN             SALESMAN                7698        20-FEB-81            1600        300        30
    7900 JAMES             CLERK                  7698        03-DEC-81             950                   30
    7698 BLAKE             MANAGER                7839        01-MAY-81            2850                   30
    7654 MARTIN            SALESMAN                7698       28-SEP-81            1250        1400       30
Aggregate Functions
SQL> SELECT DEPTNO , SUM (SAL) FROM EMP;
SELECT DEPTNO , SUM (SAL) FROM EMP
   *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> SELECT DEPTNO , SUM (SAL) FROM EMP GROUP BY DEPTNO;


   DNO SUM(SAL)
---------- -------------------- ----------
       10       8750
       20      10875
       30       9400
Aggregate Functions
SQL> SELECT DEPTNO , JOB , SUM (SAL) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO , JOB , SUM (SAL) FROM EMP GROUP BY DEPTNO
        *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> Select DEPTNO , JOB , SUM (SAL) From Emp
   GROUP BY DEPTNO,job order by deptno;

   DEPTNO JOB                 SUM(SAL)
---------- --------- --------------------
       10 CLERK                   1300
       10 MANAGER                 2450
       10 PRESIDENT               5000
       20 ANALYST                 6000
       20 CLERK                   1900
       20 MANAGER                 2975
       30 CLERK                    950
       30 MANAGER                 2850
       30 SALESMAN                5600
Aggregate Functions
SQL> SELECT DEPTNO , SUM (SAL) FROM EMP GROUP BY DEPTNO;

   DNO SUM(SAL)
---------- -------------------- ----------
       10       8750
       20      10875
       30       9400


SQL> select DEPTNO , SUM(SAL) FROM EMP
     WHERE DEPTNO IN (10,20) GROUP BY DEPTNO;

   DEPTNO SUM(SAL)
---------- ----------
       20      10875
       10       8750

SQL> select DEPTNO , SUM(SAL) FROM EMP
     GROUP BY DEPTNO
       HAVING SUM(SAL) < 10000;

   DEPTNO SUM(SAL)
---------- ----------
       30       9400
       10       8750
Aggregate Functions
SQL> select DEPTNO , SUM(SAL) FROM EMP
     GROUP BY DEPTNO
       HAVING SUM(SAL) < 10000 ORDER BY DEPTNO;

   DEPTNO SUM(SAL)
---------- ----------
       10       8750
       30       9400

-----------------------------------------------------------------------------------------------------------------------------------------------------



SQL> select DEPTNO , SUM(SAL) FROM EMP
     GROUP BY DEPTNO
      HAVING SUM(SAL) < 10000
       ORDER BY SUM(SAL);

   DEPTNO SUM(SAL)
---------- ----------
       10       8750
       30       9400
Analytical Functions
                 Analytical Functions


1.    RANK
2.    DENSE_RANK
3.    LEAD
4.    LAG
5.    NULLS FIRST
6.    NULLS LAST
7.    ORDER BY
8.    PARTITION BY
9.    ROLLUP
10.   CUBE
                                              RANK
•   SQL> select empno, sal,                          SQL> select empno,sal,deptno,
    RANK()over(order by sal) from emp;               RANK()over(order by deptno) from emp;

    EMPNO    SAL RANK() OVER(ORDERBYSAL)             EMPNO          SAL          DEPTNO     RANK ()OVER
----------     ----------   -------- -------------                                         (ORDERBYDEPTNO)
     7369     800                    1                ----------   ---------- ---------- -------------------------
     7900     950                    2               7782          2450           10                      1
     7876    1100                    3               7839          5000           10                      1
     7521    1250                    4               7934          1300           10                      1
     7654    1250                    4             7566          2975           20                      4  
     7934    1300                    6               7902          3000           20                      4
     7844    1500                    7               7876          1100           20                      4
     7499    1600                    8               7369           800          20                       4
     7782    2450                    9               7788          3000           20                      4
     7698    2850                   10               7521          1250           30                      9
     7566    2975                   11               7844          1500           30                      9
     7788    3000                   12               7499          1600           30                      9
     7902    3000                   12               7900           950           30                      9
     7839    5000                   14               7698          2850           30                      9
                                                     7654          1250           30                      9
                                 Dense_Rank
SQL> select empno, sal,                                SQL> select empno, sal, deptno,
  dense_rank()over(order by sal) from                    DENSE_RANK()over(order by
  emp;                                                   deptno) from emp;

    EMP                         DENSE_RANK()               EMPNO             SAL DEPTNO                        Dene
    NO           SAL             OVER(ORDERBYSAL)      ---------- ---------- ---------- --------------------------------
---------- ---------- ----------------------------          7782         2450          10                           1
     7369          800                         1            7839         5000          10                           1
     7900          950                         2            7934         1300          10                           1
     7876         1100                          3           7566         2975          20                           2
     7521         1250                          4           7902         3000          20                            2
     7654         1250                          4           7876         1100          20                           2
     7934         1300                          5         7369          800          20                           2
     7844         1500                          6           7788         3000          20                           2
     7499         1600                          7           7521         1250          30                           3
     7782         2450                          8           7844         1500          30                           3
     7698         2850                          9           7499         1600          30                           3
     7566         2975                         10           7900          950          30                           3
     7788         3000                         11           7698         2850          30                           3
     7902         3000                         11           7654         1250          30                           3
     7839         5000                         12
                      Employee Table

EMPNO ENAME     JOB         MGR HIREDATE       SAL      COMM    DEPTNO

  7782 CLARK    MANAGER       7839     09-JUN-81    2450            10
  7839 KING     PRESIDENT             17-NOV-81     5000            10
  7934 MILLER   CLERK         7782     23-JAN-82    1300            10

  7566 JONES    MANAGER      7839     02-APR-81     2975            20
  7902 FORD     ANALYST      7566     03-DEC-81     3000            20
  7876 ADAMS    CLERK        7788     23-MAY-87      1100           20
  7369 SMITH    CLERK         7902     17-DEC-80      800           20
  7788 SCOTT    ANALYST      7566     19-APR-87      3000           20

  7521 WARD     SALESMAN      7698     22-FEB-81     1250    500    30
  7844 TURNER   SALESMAN      7698     08-SEP-81     1500     0     30
  7499 ALLEN    SALESMAN       7698     20-FEB-81     1600   300     30
  7900 JAMES    CLERK         7698     03-DEC-81      950           30
  7698 BLAKE    MANAGER       7839     01-MAY-81     2850           30
  7654 MARTIN   SALESMAN       7698    28-SEP-81     1250    1400   30
                                        Group by

SQL> select deptno,count(*) from emp group by deptno;

   DEPTNO             COUNT(*)
-------------------   ---------------
        10                   3
         20                  5
         30                  6
                           COUNT
SQL> select deptno,                  SQL> select deptno,
count(*) OVER (partition bydeptno)     count(*) over() as cc
   as cc from emp;                               from emp;

   DEPTNO             CC                DEPTNO            CC
---------- ----------                --------- ----------
       10          3                       20         14
       10          3                       30         14
       10          3                       30         14
       20          5                       20         14
       20          5                       30         14
       20          5                       30         14
       20          5                       10         14
       20          5                       20         14
       30          6                       10         14
       30          6                       30         14
       30          6                       20         14
       30          6                       30         14
       30          6                       20         14
       30          6                       10         14
                                              COUNT
SQL> select empno, sal,deptno,                     SQL> select empno, sal, deptno,
   count(*) over                                   count(*) over(partition by deptno
(partition by deptno order by sal) cc                 order by deptno) cc
   from emp;                                       from emp;

    EMPNO             SAL DEPTNO              CC       EMPNO             SAL DEPTNO              CC
---------- ---------- ---------- ----------        ---------- ---------- ---------- ----------
     7934         1300          10            1         7782         2450          10            3
     7782         2450          10            2         7839         5000          10            3
     7839         5000          10            3         7934         1300          10            3
     7369          800         20             1         7566         2975          20            5
     7876         1100          20            2         7902         3000          20            5
     7566         2975          20            3         7876         1100          20            5
     7788         3000          20            5         7369          800         20             5
     7902         3000          20            5         7788         3000          20            5
     7900          950         30             1         7521         1250          30            6
     7654         1250          30            3         7844         1500          30            6
     7521         1250          30            3         7499         1600          30            6
     7844         1500          30            4         7900          950         30             6
     7499         1600          30            5         7698         2850          30            6
     7698         2850          30            6         7654         1250          30            6
                                     LEAD & LAG
SQL> select empno, sal,                               SQL> select empno, deptno, sal,
  lead(sal)over(order by sal) from                      lead(sal)over(order by deptno) a
  emp;                                                  from emp;
  EMPNO         SAL            LEAD(SAL)OVER              EMP       DEPT
                               (ORDERBYSAL)                  NO      NO           SAL         A
                                                      ----------   ----------   ----------- ----------
----------   ----------   -------------------------        7782       10          2450       5000
     7369      800                   950                   7839       10          5000       1300
     7900      950                  1100                   7934       10          1300       2975
     7876     1100                   1250                  7566       20          2975       3000
     7521     1250                   1250                  7902       20          3000       1100
     7654     1250                   1300                  7876       20          1100        800
     7934     1300                   1500                  7369       20           800      3000
     7844     1500                   1600                  7788       20           3000      1250
     7499     1600                   2450                  7521       30           1250      1500
     7782     2450                   2850                  7844       30           1500      1600
     7698     2850                   2975                  7499       30           1600       950
     7566     2975                   3000                  7900       30           950      2850
     7788     3000                   3000                  7698       30           2850      1250
     7902     3000                   5000                  7654       30           1250
     7839     5000
                               LEAD & LAG
SQL> select empno, deptno, sal,                 SQL> select empno, deptno, sal,
  lag(sal)over(order by sal) a from               lag(sal)over(order by deptno) a from
  emp;                                            emp;

   EMP DEPTNO SAL                   A            EMPNO DEPTNO SAL                    A
     NO                                          ---------- ---------- --------   ---------
  ---------- ---------- ---------- ----------       7782      10         2450
    7369        20       800                        7839      10        5000       2450
    7900        30       950      800               7934      10        1300       5000
    7876        20      1100       950              7566      20        2975      1300
    7521        30      1250      1100              7902      20        3000       2975
    7654        30      1250      1250              7876      20        1100      3000
    7934        10      1300      1250              7369      20        800        1100
    7844        30      1500      1300              7788      20        3000       800
    7499        30      1600      1500              7521      30        1250       3000
    7782        10      2450      1600              7844      30        1500      1250
    7698        30      2850      2450              7499      30        1600      1500
    7566        20      2975      2850              7900      30        950       1600
    7788        20      3000      2975              7698      30       2850        950
    7902        20      3000      3000              7654      30       1250        2850
    7839        10      5000      3000
                         NULLS FIRST / LAST
SQL> select empno, sal, comm,             SQL> select empno, sal, comm,
  rank()over(order by comm nulls            rank()over(order by comm Nulls
  first) a from emp;                        last) a from emp;

   EMPNO SAL            COMM         A        EMPNO             SAL         COMM        A
---------- ----------   ----------        ---------- ---------- ---------- ----------
     7369         800                 1        7844         1500             0          1
     7782        2450                 1        7499         1600            300         2
     7902        3000                 1        7521         1250            500         3
     7900         950                 1        7654         1250           1400         4
     7876        1100                 1        7788         3000                        5
     7566        2975                 1        7839         5000                        5
     7698        2850                 1        7876         1100                        5
     7934        1300                 1        7900          950                        5
     7788        3000                 1        7902         3000                        5
     7839        5000                 1        7934         1300                        5
     7844        1500      0         11        7698         2850                        5
     7499        1600     300        12        7566         2975                        5
     7521        1250     500        13        7369          800                        5
     7654        1250    1400        14        7782         2450                        5
SQL> select deptno, job, sum(sal), count(*) from emp
                       group by deptno , job;


   DEPTNO JOB                  SUM(SAL) COUNT(*)
---------- --------- ---------- ----------
       10 CLERK                   1300     1
       10 MANAGER                 2450     1
       10 PRESIDENT               5000     1

     20 ANALYST           6000        2
     20 CLERK             1900        2
     20 MANAGER           2975        1

     30 CLERK              950         1
     30 MANAGER           2850         1
     30 SALESMAN          5600         4
                                    ---------
                                       14
                                    --------
                            Template
   DEPTNO JOB              COUNT(*) SUM(SAL)
---------- --------- ---------- ----------

       10 CLERK                   1      1300
       10 MANAGER                 1      2450
       10 PRESIDENT               1      5000
------------------------------------------------
        10                        3      8750
       20 CLERK                   2      1900
       20 ANALYST                 2       6000
       20 MANAGER                 1      2975
---------------------------------------------------
         20                       5      10875
       30 CLERK                   1       950
       30 MANAGER                 1      2850
       30 SALESMAN               4       5600
---------------------------------------------------
         30                       6      9400

                                 14      29025
SQL>   SELECT deptno, job, count(*),sum(sal)     SQL> SELECT deptno,job,count(*),sum(sal)
        from EMP                                    F FROM emp
      GROUP BY ROLLUP(deptno,job);               G    GROUP BY CUBE(deptno,job);

                                                  DEPTNO COUNT(*) SUM(SAL)
   DEPTNO JOB             COUNT(*) SUM(SAL)       JOB
---------- --------- ---------- ----------          --------- --------- ---------- ----------
                                                                              14      29025
      10 CLERK                    1      1300          CLERK                    4      4150
      10 MANAGER                  1      2450          ANALYST                  2       6000
      10 PRESIDENT                1      5000          MANAGER                  3      8275
       10                        3       8750          SALESMAN                 4       5600
      20 CLERK                    2      1900          PRESIDENT                1      5000
      20 ANALYST                  2      6000
      20 MANAGER                  1      2975            10          3    8750
        20                       5       10875        10 CLERK       1    1300
      30 CLERK                    1       950         10 MANAGER     1    2450
      30 MANAGER                  1      2850         10 PRESIDENT   1    5000
      30 SALESMAN                4       5600         20           5   10875
        30                       6       9400         20 CLERK       2    1900
                                 14      29025        20 ANALYST     2    6000
                                                      20 MANAGER     1    2975
                                                      30           6   9400
                                                      30 CLERK       1     950
                                                      30 MANAGER     1    2850
                                                      30 SALESMAN    4   5600
  Conversion Functions
                Data type
               conversion




Implicit data type    Explicit data type
  conversion            conversion
Implicit Data Type Conversion
For assignments, the Oracle server can
  automatically convert the following:
From                   To

VARCHAR2 or CHAR      NUMBER

VARCHAR2 or CHAR      DATE

NUMBER                VARCHAR2

DATE                  VARCHAR2
Explicit Data Type Conversion
      TO_NUMBER            TO_DATE




 NUMBER             CHARACTER        DATE




          TO_CHAR          TO_CHAR
               RR Date Format
Current Year     Specified Date         RR Format     YY Format
1995             27-OCT-95              1995          1995
1995             27-OCT-17              2017          1917
2001             27-OCT-17              2017          2017
2001             27-OCT-95              1995          2095

         If the specified two-digit year is:

                0–49                    50–99

         The return date is in    The return date is in
         the current century      the century before
                                  the current one
                                    YY
SQL> SELECT TO_CHAR(TO_DATE('23-DEC-25','DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-2025

SQL> SELECT TO_CHAR(TO_DATE('23-DEC-49','DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;
 DATE
23-DEC-2049

SQL> SELECT TO_CHAR(TO_DATE('23-DEC-50','DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

 DATE
23-DEC-2050

SQL> SELECT TO_CHAR(TO_DATE('23-DEC-77','DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-2077
                                    RR
SQL> SELECT TO_CHAR(TO_DATE('23-DEC-25','DD-MON-RR'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-2025


SQL> SELECT TO_CHAR(TO_DATE('23-DEC-49','DD-MON-RR'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-2049


SQL> SELECT TO_CHAR(TO_DATE('23-DEC-77','DD-MON-RR'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-1977


SQL> SELECT TO_CHAR(TO_DATE('23-DEC-50','DD-MON-RR'),'DD-MON-YYYY') FROM DUAL;

DATE
23-DEC-1950
To_Char( Dt , Req_Format)
SQL> select SYSDATE FROM DUAL;
DATE
20-JUL-07

SQL> SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
DATE
6

SQL>   SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
DATE
20

SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;
DATE
FRIDAY

SQL> SELECT TO_CHAR(SYSDATE,'Day') FROM DUAL;
DATE
Friday
SQL>   SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
DATE
07

SQL> SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL;
DATE
JUL

SQL>   SELECT TO_CHAR(SYSDATE,'Mon') FROM DUAL;

TO_
---
Jul

SQL> SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL;

TO_CHAR(S
---------
JULY
SQL>   SELECT TO_CHAR(SYSDATE,'YY') FROM DUAL;
DATE
07

SQL> SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
DATE
2007

SQL>   SELECT TO_CHAR(SYSDATE,‘RR') FROM DUAL;
DATE
07

SQL> SELECT TO_CHAR(SYSDATE,‘RRRR') FROM DUAL;
DATE
2007

SQL>SELECT HIREDATE,
TO_CHAR(HIREDATE,'DDD,DD/MM/YYYY HH:MI:SS') FROM EMP;

17-DEC-80 352,17/12/1980 12:00:00
20-FEB-81 051,20/02/1981 12:00:00
 SQL> select to_char(sysdate,'HH : MI : SS') from dual;

TO_CHAR(SYSD
------------
04 : 13 : 53

SQL> select to_char(sysdate,'HH12 : MI : SS') from dual;

TO_CHAR(SYSD
------------
04 : 14 : 08

SQL>   select to_char(sysdate,'HH24 : MI : SS') from dual;

TO_CHAR(SYSD
------------
16 : 14 : 19

SQL> select to_char(sysdate,'HH12 : MI : SS AM ') from dual;

TO_CHAR(SYSDATE,
----------------
04 : 15 : 06 PM

SQL> select to_char(sysdate,'HH24 : MI : SS AM ') from dual;

TO_CHAR(SYSDATE,
----------------
16 : 15 : 16 PM
To_Date(String, Str_Format)
SQL> SELECT * FROM EMP ORDER BY HIREDATE;

     EMPNO   ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----------   ---------- --------- ---------- --------- ---------- ---------- ----------
      7369   SMITH      CLERK       7902   17-DEC-80        800                    20
      7499   ALLEN      SALESMAN    7698   20-FEB-81       1600        300         30
      7521   WARD       SALESMAN    7698   22-FEB-81       1250        500         30
      7566   JONES      MANAGER     7839   02-APR-81       2975                    20
      7698   BLAKE      MANAGER     7839   01-MAY-81       2850                    30
      7782   CLARK      MANAGER     7839   09-JUN-81       2450                    10
      7844   TURNER     SALESMAN    7698   08-SEP-81       1500          0         30
      7654   MARTIN     SALESMAN    7698   28-SEP-81       1250       1400         30
      7839   KING       PRESIDENT          17-NOV-81       5000                    10
      7900   JAMES      CLERK       7698   03-DEC-81        950                    30
      7902   FORD       ANALYST     7566   03-DEC-81       3000                    20
      7934   MILLER     CLERK       7782   23-JAN-82       1300                    10
      7788   SCOTT      ANALYST     7566   19-APR-87       3000                    20
      7876   ADAMS     CLERK        7788   23-MAY-87       1100                    20

14 rows selected.

SQL> SELECT * FROM EMP
             WHERE HIREDATE = '23-MAY-87';

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
 SQL> SELECT * FROM EMP
             WHERE HIREDATE = '23-MAY-87';

EMPNO ENAME   JOB     MGR HIREDATE     SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- -------
7876 ADAMS    CLERK 7788 23-MAY-87    1100             20

SQL> SELECT * FROM EMP
           WHERE HIREDATE = TO_DATE('23-MAY-87');

EMPNO ENAME   JOB     MGR HIREDATE     SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- -------
7876 ADAMS    CLERK 7788 23-MAY-87    1100             20

SQL> SELECT * FROM EMP
WHERE HIREDATE = TO_DATE('05-23-87','MM-DD-RR');

EMPNO ENAME   JOB     MGR HIREDATE     SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- -------
7876 ADAMS    CLERK 7788 23-MAY-87    1100             20

SQL> SELECT * FROM EMP
WHERE HIREDATE = TO_DATE('05-23-1987','MM-DD-YYYY');

EMPNO ENAME   JOB     MGR HIREDATE     SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- -------
7876 ADAMS    CLERK 7788 23-MAY-87    1100             20
 SQL> SELECT * FROM EMP
WHERE HIREDATE = TO_DATE('MAY-23-87','MON-DD-YY');

no rows selected


SQL> SELECT TO_CHAR( TO_DATE('MAY-23-87','MON-DD-YY'),'YYYY')
FROM DUAL;
 Date
2087

SQL> SELECT TO_CHAR( TO_DATE('MAY-23-87','MON-DD-RR'),'YYYY')
FROM DUAL;
 Date
1987

SQL> SELECT * FROM EMP WHERE
HIREDATE = TO_DATE('MAY-23-87','MON-DD-RR');

EMPNO ENAME   JOB     MGR HIREDATE     SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- -------
7876 ADAMS    CLERK 7788 23-MAY-87    1100             20
Set Operators
Commands :-

       •   UNION
       •   UNION ALL
       •   INTERSEC
       •   MINUS
Set Operators
UNION
• Combines the Queries output.
• Wont allow the Duplicates Records.

UNION ALL
• Rows in first Query not in the Following.
• Allow all Duplicates Records.

MINUS
• Rows from the first Query output, not in the Second
• Wont allow the Duplicates.

INTERSECT
• Common output of all the Queries.
Set Operators
Set Operators
Set Operators :-
  •   You can combine multiple Select queries using the set operators

  •   All set operators have equal precedence.

  •    If a SQL statement contains multiple set operators, then Oracle Database
      evaluates them from the left to right unless parentheses explicitly specify
      another order.

  •   The number of columns and there Data type should be match in all the
      Queries.

  •   If both queries select values of datatype CHAR of equal length, then the
      returned values have datatype CHAR of that length.

  •    If the queries select values of CHAR with different lengths, then the returned
      value is VARCHAR2 with the length of the larger CHAR value.
Set Operators :-
  •   If either or both of the queries select values of datatype VARCHAR2, then
      the returned values have datatype VARCHAR2.

  •   If all queries select values of type NUMBER, then the returned values have
      datatype NUMBER.

  •   If component queries select numeric data, then the datatype of the return
      values is determined by numeric precedence:
  •
  •   If any query selects values of type BINARY_DOUBLE, then the returned
      values have datatype BINARY_DOUBLE.
  •
  •   If no query selects values of type BINARY_DOUBLE but any query selects
      values of type BINARY_FLOAT, then the returned values have datatype
      BINARY_FLOAT.
Set Operators

Restrictions on the Set Operators ;-

   •    The set operators are not valid on columns of type BLOB, CLOB, BFILE,
       VARRAY, or nested table.
   •    The UNION, INTERSECT, and MINUS operators are not valid on LONG
       columns.

   •   If the select list preceding the set operator contains an expression, then
       you must provide a column alias for the expression in order to refer to it in
       the order_by_clause.

   •   You cannot also specify the for_update_clause with the set operators.

   •   You cannot specify the order_by_clause in the subquery of these
       operators
Set Operators
                                          UNION ALL

SQL> select * from dept
 2 UNION ALL
 3 select * from dept;

   DEPTNO DNAME              LOC
---------- -------------- -------------
      10 ACCOUNTING          NEW YORK
      20 RESEARCH           DALLAS
      30 SALES           CHICAGO
      40 OPERATIONS          BOSTON
      10 ACCOUNTING          NEW YORK
      20 RESEARCH           DALLAS
      30 SALES           CHICAGO
      40 OPERATIONS          BOSTON

8 rows selected.
Set Operators
                                          UNION
SQL> select * from dept
 2 UNION
 3 select * from dept;

   DEPTNO DNAME               LOC
---------- -------------- -------------
      10 ACCOUNTING          NEW YORK
      20 RESEARCH            DALLAS
      30 SALES              CHICAGO
      40 OPERATIONS          BOSTON
Set Operators
                                                       MINUS

SQL> SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
 2 MINUS
 3 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=10;

    EMPNO ENAME                SAL     DEPTNO
---------- ---------- ---------- ----------
     7369 SMITH               800        20
     7902 FORD              3000         20
     7876 ADAMS              1100        20
     7566 JONES             2975         20
     7788 SCOTT             3000         20

    7844   TURNER           1500        30
    7900   JAMES            950         30
    7521   WARD             1250        30
    7499   ALLEN           1600         30
    7654   MARTIN           1250        30
    7698   BLAKE           2850         30

11 rows selected.
-----------------------------------------------------------------------------------------
 SQL> select * from emp where deptno=10
  2 minus
  3 select * from emp ;

no rows selected
Set Operators

                                     INTERSECT

SQL> SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
 2 INTERSECT
 3 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=10;

    EMPNO ENAME                SAL     DEPTNO
---------- ---------- ---------- ----------
     7782 CLARK             2450         10
     7839 KING              5000         10
     7934 MILLER            1300         10
Set Operators
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                   ERROR – Data Type

SQL> select dname,deptno,loc from dept
  2    UNION
  3    select deptno,dname,loc from dept;
 select dname,deptno,loc from dept
      *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                ERROR – Number of Columns

SQL> select dname,deptno,loc from dept
  2 UNION
  3 select dname,deptno FROM DEPT;
select dname,deptno,loc from dept
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
Set Operators
                                          COLUMN NAME

SQL> select deptno as D ,dname,loc from dept
 2   UNION
 3   select * from dept;

       D DNAME            LOC
---------- -------------- -------------
      10 ACCOUNTING          NEW YORK
      20 RESEARCH            DALLAS
      30 SALES               CHICAGO
      40 OPERATIONS          BOSTON


SQL> select * from dept
 2 union
 3 select deptno as D ,dname,loc from dept;

   DEPTNO DNAME              LOC
---------- -------------- -------------
      10 ACCOUNTING          NEW YORK
      20 RESEARCH            DALLAS
      30 SALES               CHICAGO
      40 OPERATIONS          BOSTON
Set Operators
                                          Default Column

SQL> select dname,deptno,loc from dept
 2 UNION
 3 select dname,deptno,TO_CHAR(NULL) FROM DEPT;

DNAME              DEPTNO LOC
-------------- ---------- -------------
ACCOUNTING               10 NEW YORK
ACCOUNTING               10
OPERATIONS                40 BOSTON
OPERATIONS                40
RESEARCH                 20 DALLAS
RESEARCH                 20
SALES                    30 CHICAGO
SALES                    30
Set Operators
                                       ERROR - ORDER BY
 SQL> select * from dept order by deptno
  2    UNION ALL
  3    select * from dept order by deptno ;
ERROR at line 2:
ORA-00933: SQL command not properly ended
 ---------------------------------------------------------------------------------------------------------
SQL> SELECT * FROM EMP
  2 INTERSECT
  3 SELECT * FROM EMP WHERE DEPTNO=10 ORDER BY DEPTNO;
ERROR at line 3:
ORA-00904: "DEPTNO": invalid identifier
---------------------------------------------------------------------------------------------------------
SQL> SELECT DEPTNO,TO_NUMBER(2) AS NU FROM DEPT
  2 UNION
  3 SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
    DEPTNO          NU
---------- ----------
      10       2
      10       8750
      20       2
      20      10875
      30       2
      30       9400
      40       2
CONSTRAINTS
                                   Types of constraints
PRIMARY KEY
• Will not allow duplicate values and NULL’s are not allowed (NOT NULL & UNIQUE)
• Only one primary Kay can be defined in a table
UNIQUE
Will not allow duplicate values but allow NULL’s .
NOT NULL
• Will not allow NULL values but allow Duplicates values.
FOREIGN KEY
• A column value which is derived from PRIMARY KEY/UNIQUE column of same/another
 table.
CHECK
•Checks for the given condition before inserting the record into the Table
UNIQUE Key

Unique key constraints are appropriate for any column where
duplicate values are not allowed.
NOT NULL & CHECK Constraints

By default, all columns can contain nulls.
Define NOT NULL constraints for columns of a table that
absolutely require values at all times.

                                    CHECK(SAL<=9000)
                                  Primary Key
Each table can have one primary key, which uniquely identifies each row in a table and
ensures that no duplicate rows exist.
Create Table T_Emp
      (
Eno        Number (5),
Ename Varchar2 (20),
J_Date Date,
Sal        Number (6),
          );
 Create Table T_Emp(
Eno      Number (5) Primary Key,
Ename Varchar2 (20) Unique ,
J_Date Date          Not Null,
Sal      Number (6) Check (Sal > 1000 ),
Deptno Number        REFERENCES Dept (Deptno)
       );
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from
user_constraints
 2 where table_name='T_EMP';

CONSTRAINT_NAME                        C
------------------------------ -
SYS_C005186                        C
SYS_C005187                        C
SYS_C005188                        P
SYS_C005189                        U
Create Table T_E (
Eno Number (5),
Ename Varchar2 (20),
J_Date Date,
Sal    Number (6),
Constraint c_pk Primary Key (eno),
Constraint c_un unique (ename),
Constraint c_ck check ( sal >1000 ),
Constraint fk_De FOREIGN KEY (DEPTNO)
                                     references DEPT(DEPTNO));
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints
 2 where table_name='T_E';

C_name                C_type
------------------------------ -
C_CK                        C
C_PK                        P
C_UN                        U
fk_De                       R
 Create Table Test
(
Eno Number (5) Constraint c_pk Primary Key,
Ename Varchar2 (20) Constraint c_un unique (ename),
J_Date Date         Constraint c_nn NOT NULL ,
Sal     Number (6) Constraint c_ck check ( sal >1000 ),
Deptno Number(9) References DEPT(DEPTNO)

        );

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints
 2 where table_name='TEST';

CONSTRAINT_NAME                    C
------------------------------ -
T_NN                        C
C_CTK                      C
T_PK                        P
T_UN                        U
SYS_C005205                 R
SQL> Alter Table Test ADD Constraint CPK Primary Key (eno);
SQL> Alter Table Test ADD Constraint CUK Unique (ename);
SQL> Alter Table Test ADD Constraint CCK Check (Sal > 1000);
SQL> Alter Table Test ADD Constraint CFK
              FOREIGN KEY (DEPTNO) references DEPT(DEPTNO);

SQL> Alter Table Test MODIFY J_Date Varchar2(20) NOT NULL;
SQL> Alter Table Test MODIFY Deptno Number(8) Default     10;

SQL> Alter Table Test Drop Primary Key;
SQL> Alter Table Test Drop Unique (ename);
SQL> Alter Table Test Drop CONSTRAINT <C_NAME>;
SQL> Alter Table Test Drop CONSTRAINT CPK ;


SQL> Alter Table Test ENABLE Constraint <C_NAME>;
SQL> Alter Table Test DISABLE Constraint <C_NAME>;
JOINS
                                   JOINS
JOINS:
  JOINs are used to retrieve information from multiple tables.


  TYPES OF JOINS:
  1. EQUI-JOIN :
  2. NON-EQUI JOIN :
  3. OUTER JOIN
         * LEFT OUTER JOIN
         * RIGHT OUTER JOIN
         * FULL OUTER JOIN
  4. SELF JOIN
                                                                 TABLES
----------------------------------------------------------------------------------------------------------------------
 ENO ENAME                 JOB                  MGR HIREDATE                   SAL COMM DEPTNO
----------------------------------------------------------------------------------------------------------------------
     7782 CLARK             MANAGER                7839        09-JUN-81            2450                    10
     7839 KING              PRESIDENT                        17-NOV-81             5000                     10
     7934 MILLER CLERK                             7782        23-JAN-82           1300                    10

    7566 JONES             MANAGER                7839        02-APR-81            2975                   20
    7902 FORD              ANALYST                7566        03-DEC-81            3000                   20
    7876 ADAMS             CLERK                  7788        23-MAY-87            1100                   20
    7369 SMITH             CLERK                  7902        17-DEC-80             800                   20
    7788 SCOTT             ANALYST                7566        19-APR-87             3000                  20

     7521 WARD              SALESMAN               7698         22-FEB-81            1250         500        30
     7844 TURNER SALESMAN                          7698         08-SEP-81            1500           0        30
     7499 ALLEN             SALESMAN                7698         20-FEB-81           1600          300       30
     7900 JAMES             CLERK                   7698       03-DEC-81             950                     30
     7698 BLAKE             MANAGER                 7839        01-MAY-81            2850                    30
     7654 MARTIN SALESMAN                           7698        28-SEP-81            1250        1400        30
----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------
 DEPT NO               DNAME                     LOC
 -----------------------------------------------------------
10                ACCOUNTING                NEWYORK
20               RESEARCH                  DALLAS
30               SALES                     CHICAGO
40               OPERATIONS                 BOSTON
-----------------------------------------------------------
                                                  EQUI-JOIN
SQL> select empno,ename,sal,comm,EMP.deptno ,dname from emp,dept
 2 where emp.deptno=dept.deptno;


    EMPNO ENAME                       SAL         COMM DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- --------------
     7369 SMITH                  800                        20        RESEARCH
     7499 ALLEN                  1600         300           30        SALES
     7521 WARD                   1250          500         30         SALES
     7566 JONES                  2975                      20         RESEARCH
     7654 MARTIN                  1250        1400         30         SALES
     7698 BLAKE                  2850                       30        SALES
     7782 CLARK                  2450                      10         ACCOUNTING
     7788 SCOTT                  3000                       20        RESEARCH
     7839 KING                 5000                        10         ACCOUNTING
     7844 TURNER                   1500           0        30         SALES
     7876 ADAMS                   1100                     20         RESEARCH
     7900 JAMES                   950                      30         SALES
     7902 FORD                  3000                        20        RESEARCH
     7934 MILLER                 1300                      10         ACCOUNTING

14 rows selected.
                                   RIGHT OUTER JOIN
SQL> select empno,ename,sal,comm,DEPT.deptno ,dname from emp,dept
 2 where emp.deptno(+)=dept.deptno;

    EMPNO ENAME                       SAL         COMM DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- ------------
     7782 CLARK                  2450                      10 ACCOUNTING
     7839 KING                 5000                       10 ACCOUNTING
     7934 MILLER                 1300                      10 ACCOUNTING
     7566 JONES                  2975                      20 RESEARCH
     7902 FORD                  3000                       20 RESEARCH
     7876 ADAMS                   1100                     20 RESEARCH
     7369 SMITH                  800                       20 RESEARCH
     7788 SCOTT                  3000                      20 RESEARCH
     7521 WARD                   1250          500         30 SALES
     7844 TURNER                   1500           0        30 SALES
     7499 ALLEN                  1600         300           30 SALES
     7900 JAMES                   950                       30 SALES
     7698 BLAKE                  2850                        30 SALES
     7654 MARTIN                  1250        1400           30 SALES
                                                              40 OPERATIONS
                                         RIGHT OUTER JOIN
SQL> select empno,ename,sal,comm,EMP.deptno ,dname from emp,dept
 2 where emp.deptno(+)=dept.deptno;

    EMPNO ENAME                    SAL        COMM DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- ---------------------------------
     7782 CLARK                  2450                          10 ACCOUNTING
     7839 KING                 5000                            10 ACCOUNTING
     7934 MILLER                 1300                         10 ACCOUNTING
     7566 JONES                  2975                          20 RESEARCH
     7902 FORD                  3000                           20 RESEARCH
     7876 ADAMS                   1100                         20 RESEARCH
     7369 SMITH                  800                          20 RESEARCH
     7788 SCOTT                  3000                         20 RESEARCH
     7521 WARD                   1250          500            30 SALES
     7844 TURNER                   1500           0          30 SALES
     7499 ALLEN                  1600         300            30 SALES
     7900 JAMES                   950                        30 SALES
     7698 BLAKE                  2850                        30 SALES
     7654 MARTIN                  1250        1400           30 SALES
                                                                 OPERATIONS

15 rows selected.
                                      LEFT OUTER JOIN
SQL> select empno,ename,sal,comm,EMP.deptno ,dname from emp,dept
 2 where emp.deptno=DEPT.DEPTNO(+);

    EMPNO ENAME                       SAL         COMM DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- ---------------------- ------------
     7369 SMITH                  800                       20 RESEARCH
     7499 ALLEN                  1600         300          30 SALES
     7521 WARD                   1250          500          30 SALES
     7566 JONES                  2975                       20 RESEARCH
     7654 MARTIN                  1250         1400        30 SALES
     7698 BLAKE                  2850                      30 SALES
     7782 CLARK                  2450                      10 ACCOUNTING
     7788 SCOTT                  3000                      20 RESEARCH
     7839 KING                 5000                       10 ACCOUNTING
     7844 TURNER                   1500           0         30 SALES
     7876 ADAMS                   1100                     20 RESEARCH
     7900 JAMES                   950                      30 SALES
     7902 FORD                  3000                       20 RESEARCH
     7934 MILLER                 1300                      10 ACCOUNTING

14 rows selected.
                             LEFT OUTER JOIN
SQL> select empno,ename,sal,comm,DEPT.deptno ,dname from emp,dept
 2 where emp.deptno=DEPT.DEPTNO(+);

    EMPNO ENAME                       SAL         COMM DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- -------------
     7369 SMITH                  800                   20 RESEARCH
     7499 ALLEN                  1600         300 30 SALES
     7521 WARD                   1250          500 30 SALES
     7566 JONES                  2975                  20 RESEARCH
     7654 MARTIN                  1250         140 30 SALES
     7698 BLAKE                  2850                  30 SALES
     7782 CLARK                  2450                  10 ACCOUNTING
     7788 SCOTT                  3000                  20 RESEARCH
     7839 KING                 5000                    10 ACCOUNTING
     7844 TURNER                   1500           0 30 SALES
     7876 ADAMS                   1100                 20 RESEARCH
     7900 JAMES                   950                  30 SALES
     7902 FORD                  3000                   20 RESEARCH
     7934 MILLER                 1300                  10 ACCOUNTING

14 rows selected.
                                             NON EQUEJOINS
                                                    7900 JAMES       20 RESEARCH
SQL> SELECT
                                                       7902 FORD        20 RESEARCH
  EMPNO,ENAME,DEPT.DEPTNO,DNA                          7934 MILLER      20 RESEARCH
  ME FROM EMP,DEPT;                                    7369 SMITH       30 SALES
                                                       7499 ALLEN       30 SALES
    EMPNO ENAME                    DEPTNO DNAME        7521 WARD        30 SALES
---------- ---------- ---------- --------------        7566 JONES       30 SALES
     7369 SMITH                  10 ACCOUNTING         7654 MARTIN       30 SALES
     7499 ALLEN                   10 ACCOUNTING        7698 BLAKE       30 SALES
     7521 WARD                    10 ACCOUNTING        7782 CLARK       30 SALES
     7566 JONES                   10 ACCOUNTING        7788 SCOTT        30 SALES
     7654 MARTIN                   10 ACCOUNTING       7839 KING       30 SALES
     7698 BLAKE                   10 ACCOUNTING        7844 TURNER        30 SALES
     7782 CLARK                   10 ACCOUNTING        7876 ADAMS        30 SALES
     7788 SCOTT                   10 ACCOUNTING        7900 JAMES       30 SALES
     7839 KING                  10 ACCOUNTING          7902 FORD        30 SALES
     7844 TURNER                    10 ACCOUNTING      7934 MILLER      30 SALES
     7876 ADAMS                    10 ACCOUNTING       7369 SMITH       40 OPERATIONS
     7900 JAMES                   10 ACCOUNTING        7499 ALLEN       40 OPERATIONS
     7902 FORD                   10 ACCOUNTING         7521 WARD        40 OPERATIONS
     7934 MILLER                  10 ACCOUNTING        7566 JONES       40 OPERATIONS
     7369 SMITH                  20 RESEARCH           7654 MARTIN       40 OPERATIONS
     7499 ALLEN                   20 RESEARCH          7698 BLAKE       40 OPERATIONS
     7521 WARD                    20 RESEARCH          7782 CLARK       40 OPERATIONS
     7566 JONES                   20 RESEARCH          7788 SCOTT        40 OPERATIONS
     7654 MARTIN                   20 RESEARCH         7839 KING       40 OPERATIONS
     7698 BLAKE                   20 RESEARCH          7844 TURNER        40 OPERATIONS
     7782 CLARK                   20 RESEARCH          7876 ADAMS        40 OPERATIONS
     7788 SCOTT                   20 RESEARCH          7900 JAMES       40 OPERATIONS
     7839 KING                  20 RESEARCH            7902 FORD        40 OPERATIONS
     7844 TURNER                    20 RESEARCH        7934 MILLER      40 OPERATIONS
     7876 ADAMS                    20 RESEARCH      56 rows selected.
                                      FULL OUTER JOIN
SQL> SELECT EMPNO,ENAME,SAL,COMM, EMP.DEPTNO,DEPT.DEPTNO ,DNAME FROM
           EMP FULL OUTER JOIN DEPT
           ON EMP.deptno=DEPT.deptno ;

 EMPNO ENAME                       SAL        COMM DEPTNO DEPTNO DNAME
---------- ---------- ---------- ---------- ---------- ---------- ----------
     7369 SMITH                  800                       20         20 RESEARCH
     7499 ALLEN                  1600         300          30         30 SALES
     7521 WARD                   1250          500         30         30 SALES
     7566 JONES                  2975                      20         20 RESEARCH
     7654 MARTIN                  1250        1400        30         30      SALES
     7698 BLAKE                  2850                     30         30      SALES
     7782 CLARK                  2450                     10         10      ACCOUNTING
     7788 SCOTT                  3000                     20         20      RESEARCH
     7839 KING                 5000                       10         10      ACCOUNTING
     7844 TURNER                   1500           0      30         30       SALES
     7876 ADAMS                   1100                   20         20       RESEARCH
     7900 JAMES                   950                    30         30       SALES
     7902 FORD                  3000                     20         20       RESEARCH
     7934 MILLER                 1300                    10         10       ACCOUNTING
                                                                    40       OPERATIONS

15 rows selected.
                  SELF JOIN
SQL>   SELECT E.ENAME EMP_NAME,
  2    M.ENAME MGR_NAME
  3    FROM EMP E,EMP M
  4    WHERE E.MGR=M.EMPNO;

EMP_NAME     MGR_NAME
----------   ----------
SMITH        FORD
ALLEN        BLAKE
WARD         BLAKE
JONES        KING
MARTIN       BLAKE
BLAKE        KING
CLARK        KING
                            JOIN SYNTAX
Equi Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno
       From Emp , Dept
       Where Emp.Deptno=Dept.Deptno;

Left Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno
       From Emp , Dept
       Where Emp.Deptno=Dept.Deptno(+);

Right Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno
       From Emp , Dept
       Where Emp.Deptno(+)=Dept.Deptno;

Full Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno
       Emp FULL OUTER JOIN DEPT
       ON EMP.deptno=DEPT.deptno;
                             JOIN SYNTAX
Equi Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno from
       Emp JOIN DEPT
       ON EMP.deptno=DEPT.deptno;

Left Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno from
       Emp RIGHT OUTER JOIN DEPT
       ON EMP.deptno=DEPT.deptno;

Right Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno from
       Emp LEFT OUTER JOIN DEPT
       ON EMP.deptno=DEPT.deptno;

Full Outer Join
SQL> SELECT Empno,Ename,Sal,Comm,Emp.Deptno,Dept.Deptno from
       Emp FULL OUTER JOIN DEPT
       ON EMP.deptno=DEPT.deptno;
SUB QUERY
Sub Query :-
 • Subquery is a query within a query.
 • These subqueries are also called Nested subqueries.
 • A subquery can also be found in the FROM clause.
   These are called inline views.
 • In Oracle, you can create subqueries within your SQL
   statements.
TYPES OF SUB QUERY        :-
  1.   Single Row Sub Query
  2.   Multi Row Sub Query
  3.   Multi Column Sub Query
  4.   Co-Related Sub Query
                             Sub Query
•   These subqueries can reside in the following Clauses
•   1) SELECT
•   2) FROM -----( In-line Query )
•   3) WHERE
•   4) HAVING BY
•   5) ORDER BY
•   6) INSERT
•   7) UPDATE

• Most often, the subquery will be found in the WHERE clause.
• Oracle allows up to 255 levels of subqueries in the WHERE clause.
• Oracle allows an unlimited number of subqueries in the FROM
  clause.
                                                      Sub Query

•   SQL> select * from emp;
•
•       ENO ENAME JOB                       MGR HIREDATE                   SAL        COMM DEPTNO
•   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
•        7369 SMITH            CLERK                  7902 17-DEC-80                800        20
•        7499 ALLEN            SALESMAN              7698 20-FEB-81               1600    300  30
•        7521 WARD              SALESMAN              7698 22-FEB-81               1250    500  30
•        7566 JONES             MANAGER                7839 02-APR-81              2975        20
•        7654 MARTIN SALESMAN 7698 28-SEP-81                                       1250   1400 30
•        7698 BLAKE             MANAGER               7839 01-MAY-81               2850        30
•        7782 CLARK             MANAGER               7839 09-JUN-81               2450        10
•        7788 SCOTT             ANALYST                7566 19-APR-87              3000        20
•        7839 KING            PRESIDENT                       17-NOV-81            5000        10
•        7844 TURNER SALESMAN 7698 08-SEP-81                                       1500     0  30
•        7876 ADAMS              CLERK              7788 23-MAY-87                 1100        20
•        7900 JAMES             CLERK              7698 03-DEC-81                   950        30
•        7902 FORD             ANALYST              7566 03-DEC-81                 3000        20
•        7934 MILLER CLERK                         7782 23-JAN-82                  1300        10
                                          Single Row Sub Query
•   SQL> select Min(sal) from emp;
•   MIN
•   800

•   SQL> select * from emp where sal = (select Min(sal) from emp);
•
•    ENO ENAME JOB                     MGR HIREDATE                   SAL        COMM         DEPTNO
•   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
•        7369 SMITH            CLERK              7902 17-DEC-80               800                  20
•     (select Max(sal) from emp);
•   SQL> select * from emp where sal =
•
•   ENO ENAME JOB                      MGR HIREDATE                  SAL         COMM         DEPTNO
•   ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
•        7839 KING            PRESIDENT                  17-NOV-81            5000                  10
•
•   SQL> select * from emp where sal <= (select Avg(sal) from emp);
•
•   ENO ENAME JOB                      MGR HIREDATE                  SAL         COMM DEPTNO
•   ---------- ---------- --------- ---------- --------- ---------- ---------- -------------------- ---------- ----------
•        7369 SMITH            CLERK                7902 17-DEC-80               800                  20
•        7499 ALLEN            SALESMAN 7698 20-FEB-81                         1600         300       30
•        7521 WARD             SALESMAN 7698 22-FEB-81                         1250         500        30
•        7654 MARTIN SALESMAN 7698 28-SEP-81                                   1250        1400        30
•        7844 TURNER SALESMAN 7698 08-SEP-81                                   1500           0        30
•        7876 ADAMS             CLERK              7788 23-MAY-87               1100                   20
•        7900 JAMES            CLERK               7698 03-DEC-81               950                   30
•        7934 MILLER CLERK                         7782 23-JAN-82             1300                    10
                                              Multi Row Sub Query
SQL> select * from emp where deptno in ( select deptno from dept);
ENO ENAME JOB                      MGR HIREDATE                  SAL         COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH            CLERK                7902 17-DEC-80                800            20
     7499 ALLEN            SALESMAN             7698 20-FEB-81              1600        300  30
     7521 WARD             SALESMAN              7698 22-FEB-81              1250       500  30
     7566 JONES            MANAGER              7839 02-APR-81               2975            20
     7654 MARTIN SALESMAN 7698 28-SEP-81                                    1250      1400   30
     7698 BLAKE            MANAGER              7839 01-MAY-81               2850            30
     7782 CLARK            MANAGER              7839 09-JUN-81             2450              10
     7788 SCOTT             ANALYST            7566 19-APR-87              3000              20
     7839 KING            PRESIDENT                   17-NOV-81                5000          10
     7844 TURNER SALESMAN 7698 08-SEP-81 1500                                         0      30
     7876 ADAMS             CLERK              7788 23-MAY-87               1100            20
     7900 JAMES            CLERK               7698 03-DEC-81               950             30
     7902 FORD             ANALYST              7566 03-DEC-81              3000            20
     7934 MILLER CLERK                         7782 23-JAN-82             1300              10

14 rows selected.


SQL> select        * from dept where deptno in ( select Distinct(deptno) from emp );
   DEPTNO DNAME                     LOC
---------- -------------- -------------
       10 ACCOUNTING NEW YORK
       20 RESEARCH               DALLAS
       30 SALES              CHICAGO
                                 Multi Column Sub Query
SQL> select empno, ename ,dept.deptnodname         SQL> select empno, ename ,dept.deptno,dname
        from emp full outer join dept                     from emp full outer join dept
       on emp.deptno=dept.deptno;                         on emp.deptno=dept.deptno
                                                         where (emp.deptno,dept.dname) in
    EMPNO ENAME                     DEPTNO DNAME
---------- ---------- ---------- --------------                     ( select deptno,dname from dept);
     7369 SMITH                   20 RESEARCH
     7499 ALLEN                   30 SALES             EMPNO ENAME                     DEPTNO DNAME
     7521 WARD                     30 SALES        ---------- ---------- ---------- --------------
     7566 JONES                    20 RESEARCH          7369 SMITH                   20 RESEARCH
     7654 MARTIN                   30 SALES             7499 ALLEN                   30 SALES
     7698 BLAKE                   30 SALES              7521 WARD                     30 SALES
     7782 CLARK                    10 ACCOUNTING        7566 JONES                    20 RESEARCH
     7788 SCOTT                    20 RESEARCH          7654 MARTIN                   30 SALES
     7839 KING                   10 ACCOUNTING          7698 BLAKE                   30 SALES
     7844 TURNER                    30 SALES            7782 CLARK                    10 ACCOUNTING
     7876 ADAMS                    20 RESEARCH          7788 SCOTT                    20 RESEARCH
     7900 JAMES                    30 SALES             7839 KING                   10 ACCOUNTING
     7902 FORD                    20 RESEARCH           7844 TURNER                    30 SALES
     7934 MILLER                   10 ACCOUNTING        7876 ADAMS                    20 RESEARCH
                                   40 OPERATIONS        7900 JAMES                    30 SALES
15 rows selected.                                       7902 FORD                    20 RESEARCH
                                                        7934 MILLER                   10 ACCOUNTING
                                                   14 rows selected.
                                         Sub Query ( EXISTS )
SQL> select * from emp
     where deptno=10
       and
     EXISTS ( SELECT DEPTNO FROM EMP);

    EMPNO ENAME                 JOB               MGR HIREDATE                   SAL    COMM   DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7782 CLARK             MANAGER                7839 09-JUN-81              2450       10
     7839 KING            PRESIDENT                   17-NOV-81            5000           10
     7934 MILLER CLERK                         7782 23-JAN-82               1300          10

SQL> select * from emp
     where deptno=10
       and
     EXISTS ( SELECT DEPTNO FROM EMP where 1=2);

no rows selected

SQL> select * from emp
      where deptno=10
       and
      EXISTS ( 1=1);
EXISTS ( 1=1)
     *
ERROR at line 4:
ORA-00928: missing SELECT keyword
                                                         Sub Query ( ANY , ALL)
SQL> select sal from emp where deptno=10 order by sal;
                                                                                                             SQL> Select * from emp
      SAL
----------
                                                                                                              2 where sal < all ( select sal from emp where
     1300                                                                                                    deptno=10)
     2450                                                                                                     3 order by sal;
     5000
                                                                                                                 EMPNO ENAME                 JOB              MGR HIREDATE                   SAL      COMM
SQL> Select * from emp                                                                                       DEPTNO
 2 where sal < any ( select sal from emp where deptno=10)                                                    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 3 order by sal;                                                                                                  7369 SMITH            CLERK               7902 17-DEC-80               800              20
                                                                                                                  7900 JAMES            CLERK               7698 03-DEC-81                950             30
                                                                                                                  7876 ADAMS CLERK                         7788 23-MAY-87              1100              20
    ENO ENAME              JOB               MGR HIREDATE                  SAL        COMM DNO                    7521 WARD             SALESMAN 7698 22-FEB-81                        1250         500   30
---------- ---------- --------- ---------- --------- ---------- ---------- -------------------- ----------        7654 MARTIN SALESMAN 7698 28-SEP-81                                 1250         1400   30
     7369 SMITH            CLERK                 7902 17-DEC-80               800                    20
     7900 JAMES            CLERK                  7698 03-DEC-81               950                   30
     7876 ADAMS CLERK                            7788 23-MAY-87 1100                                20
     7521 WARD             SALESMAN 7698 22-FEB-81                          1250         500         30
     7654 MARTIN SALESMAN                       7698 28-SEP-81              1250        1400         30
     7934 MILLER CLERK                           7782 23-JAN-82             1300                    10
     7844 TURNER SALESMAN                        7698 08-SEP-81              1500          0         30
     7499 ALLEN SALESMAN                        7698 20-FEB-81              1600         300        30
     7782 CLARK MANAGER                         7839 09-JUN-81              2450                    10
     7698 BLAKE MANAGER                         7839 01-MAY-81 2850                                30
     7566 JONES            MANAGER               7839 02-APR-81              2975                    20
     7788 SCOTT ANALYST                         7566 19-APR-87              3000                     20
     7902 FORD             ANALYST              7566 03-DEC-81              3000                     20

13 rows selected.
INDEX
                                  INDEX

INDEX:-

•   An index is a performance-tuning method of allowing faster retrieval of
    records.
•   An index creates an entry for each value that appears in the indexed
    columns.
•   By default, Oracle creates B-tree indexes.
•   You should have INDEX privilege on the table to be indexed.
•   You should have CREATE ANY INDEX system privilege to create a Index.

The syntax for creating a index is:

SQL> Create [UNIQUE] index I_Name ON T_Name ( C1 , C2 , C3 ) [
  COMPUTE STATISTICS ];
                                  INDEX

COMPUTE STATISTICS

•   It tells Oracle to collect statistics during the creation of the index.
•   The statistics are then used by the optimizer to choose a "plan of execution"
    when SQL statements are executed.
•   If you forgot to define the COMPUTE STATISTICS while creating the Index
    ,you can add the Option by using the ALTER INDEX command.

SQL> ALTER INDEX I_Name REBUILD COMPUTE STATISTICS ;
                 INDEX

Types Of INDEX

1)   UNIQUE INDEX
2)    BEE TREE INDEX
3)    BIT MAP INDEX
4)    FUNCTIONED INDEX
5)    REVERSE INDEX
6)    INVISABLE INDEX
7)     INDEX ONLINE
                                 INDEX

UNIQUE INDEX

•   Indexes can be unique or non-unique.
•   Unique indexes guarantee that no two rows of a table have duplicate values
    in the key column (or columns).
•   Non-unique indexes do not impose this restriction on the column values
•   UNIQUE indicates that the combination of values in the indexed columns
    must be unique

SQL> CREATE UNIQUE INDEX dept_unique_index ON dept (dname);
                                  INDEX
BEE TREE INDEX :-

•   High Cardinality ( Less Distinct and More Duplicates )
•   Primarily used in OLTP systems .
•   By default oracle creates the Bee Tree Index.
•   The oldest and popular type of index
•   Excellent in simple Quarries.
•   It avoids large Sorting operations.
•   It wont support any Functions.
•   You can define sorting order on the indexed columns.

SQL> Create INDEX I_dno on Dept( Deptno);
SQL> Create INDEX I_dno on Dept( Deptno Asc , Sal Desc);
                                   INDEX
BIT MAP INDEX :-

•   Low Cardinality ( More Duplicates and Less Distinct )
•   Primarily used in the Data Warehousing ( OLAP )
•   Data Should be Nonvolatile (Static )
•   By using this you will improve the Query response time Drastically.
•   Internally rows become Columns.


SQL>Create BITMAP index I_BM ON Emp (Ename )
                                  INDEX

BIT MAP JOINJ INDEX

From oracle 9i onwards we will create this join index

SQL> create bitmap index i_join on emp ( d.deptno )
     From EMP e, DEPT d
     Where e.deptno=d.deptno;
                                      INDEX
    Function Base Index ;-

•     It facilitate queries that qualify a value returned by a function or expression.
•     The value of the function or expression is precomputed and stored in the
      index.
•     In addition to the prerequisites for creating a conventional index, if the index
      is based on USER-DEFINED functions, then those functions must be
      marked DETERMINISTIC.
•     Also, you just have the EXECUTE object privilege on any user-defined
      function(s) used in the function-based index if those functions are owned by
      another user
•     NULL values wont store in Function based Index.

SQL> Create Index I_Ename on Emp ( Upper(ename) ) ;

SQL> Select * from EMP
     Where Upper(Ename) is not NULL;

         Function Base Index Wont support NULL Values.
                                    INDEX
Invisible Indexes

•   Beginning with Release 11g, you can create invisible indexes.
•   An invisible index is an index that is ignored by the optimizer unless you
    explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization
    parameter to TRUE at the session or system level.
•   Making an index invisible is an alternative to making it unusable or dropping
    it.

SQL> CREATE INDEX emp_ename ON emp(ename) INVISIBLE;

SQL> SET OPTIMIZER_USE_INVISIBLE_INDEXES TRUE;




Reference Web Site :-
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm

http://www.techonthenet.com/oracle/indexes.php
VIEW
                                 VIEW

•   A view is a logical representation of table or combination of tables.
•   A view derives its data from the tables on which it is defined.
•   A view does not hold any data.
•   You can perform all DML ( Insert , Update , Delete , Select ) on
    View.
•   Base tables might in turn be actual tables or might be views
    themselves.
•   All operations performed on a view actually affect the base table of
    the view.
•   You can use views in almost the same way as tables.
•   You can query, update, insert into, and delete from views, just as
    you can standard tables.
                              VIEW
SQL> Create View V1
      AS
        Select * from Emp ;
SQL> Create View V2
      AS
       Select * from Emp Where Deptno=10 ;
SQL> Create View V3
      AS
        Select Deptno , sum(sal) as SL from Emp Group By Deptno
SQL> Create View V4
      AS
       Select Eno ,Ename from Emp ;
SQL> Create View V5
      AS
         Select Eno, Substr(Ename,1,3) AS NAME from Emp ;
                                VIEW
View With Check Option ;-

SQL> Create View V1
       AS
         Select * from Emp
                 Where Deptno=10 With Check option;
• If you select the data from emp table you can see all the Dept values.
• If you select the data from V1 View you can see only Deptno =10 data.
• You can Not insert other then Deptno=10 data into the View but you can
  insert Other the Deptno=10 data in base table

ERROR
view WITH CHECK OPTION where-clause violation
                                VIEW

View With read only Option ;-



SQL> Create View V1 (Eno, sal )
     AS
        Select Deptno,Comm from Emp with READ ONLY ;

•   You can only Read the Data.
•   You can not Apply DML’s on the VIEW.
•   You can Apply DML’s on the Base TABLE.
                                    VIEW
Forced View ;-

•   If base table dose not exist also, it will create the VIEW but you can not
    perform DML operations.
•   If base table exist then you can perform DML operations.

SQL> Create FORCE View V1 AS Select * from Emp ;
SQL> Create FORCE View V1 AS Select * from JHGAKSDGSA ;

Complex / Join View ;-

• View is created based on more then one table;
• In 7.x version this Join view are Read only.
• In 8.x at time we can perform DML operations on only one table .
In 9.x at time we can perform DML operations on Both table by using INSTEAD
    OF TRIGGERS.
SQL> Create View V1
      AS
          Select Eno, Ename, Sal, Dname from Emp , Dept
                               Where Emp.Deptno=Dept.Deptno ;
Materialized View
                    Materialized Views


Materialized Views ;-

•   It is Static View, means it will hold the data.
•   Used for Analyzing and Reporting.
•   Base table should have Primary key.
•   We need to have Create Materialized View Privilege for creating of
    it.
                                     Materialized Views
SQL>CREATE MATERIALIZED VIEW MV_Emp
          AS
         SELECT * from emp;
------------------------------------------------------------------------------------------------------------
SQL> CREATE MATERIALIZED VIEW MV_Em1
         AS
         SELECT Empno,ename,Dname from emp,dept
                      where emp.deptno=dept.deptno;
------------------------------------------------------------------------------------------------------------
SQL> CREATE MATERIALIZED VIEW MV_Em1
       BUILD IMMEDIATE
       REFRESH FORCE ON COMMIT
       WITH ROWID
        AS
            SELECT * from emp;
------------------------------------------------------------------------------------------------------------
 SQL> CREATE MATERIALIZED VIEW MV_Em1
       BUILD IMMEDIATE
       REFRESH FORCE ON DEMAND
       WITH ROWID
        AS
            SELECT * from emp;
                                     Materialized Views
SQL> CREATE MATERIALIZED VIEW MV_Em1
         REFRESH COMPLETE
         START WITH SYSDATE
         NEXT SYSDATE + 1
         AS
          SELECT * from emp;
------------------------------------------------------------------------------------------------------------

SQL> CREATE MATERIALIZED VIEW MV_Em1
       USING INDEX
       REFRESH COMPLETE
        AS
           SELECT * from emp;
------------------------------------------------------------------------------------------------------------
SQL> CREATE MATERIALIZED VIEW MV_Em1
         REFRESH ON DEMAND
         ENABLE QUERY REWRITE
         AS
             SELECT * from emp;
                           Materialized Views
LOG ;-

•    When DML changes are made to master table data,
•   Oracle Database stores rows describing those changes in the materialized view log
    and then uses the materialized view log to refresh materialized views based on the
    master table.
•   This process is called incremental or fast refresh.
•   Oracle Database must reexecute the materialized view query to refresh the
    materialized view.
•   This process is called a complete refresh. Usually, a fast refresh takes less time
    than a complete refresh.


SQL> Create materialized View Log on Emp;

SQL> CREATE MATERIALIZED VIEW MV_Em1
    REFRESH ON DEMAND
    ENABLE QUERY REWRITE
    AS
       SELECT * from emp;
                       Materialized Views
QUERY REWRITE

•   When base tables contain large amount of data, it is an expensive and time
    consuming process to compute the required aggregates or to compute joins
    between these tables.
•   In such cases, queries can take minutes or even hours to return the answer.
•   Because materialized views contain already precomputed aggregates and joins,
•   Oracle employs an extremely powerful process called query rewrite to quickly
    answer the query using materialized views.
•   One of the major benefits of creating and maintaining materialized views is the
    ability to take advantage of query rewrite.

SQL> CREATE MATERIALIZED VIEW MV_Em1
     BUILD IMMEDIATE
      REFRESH ON DEMAND
      ENABLE QUERY REWRITE
      AS
     SELECT * from emp;
                         Materialized Views
•    which transforms a SQL statement expressed in terms of tables or views into a
    statement accessing one or more materialized views that are defined on the
    detail tables.
•   The transformation is transparent to the end user or application, requiring no
    intervention and no reference to the materialized view in the SQL statement.
    Because query rewrite is transparent, materialized views can be added or
    dropped just like indexes without invalidating the SQL in the application code.
•   A query undergoes several checks to determine whether it is a candidate for
    query rewrite.
•   If the query fails any of the checks, then the query is applied to the detail tables
    rather than the materialized view.
•   This can be costly in terms of response time and processing power.
•   The optimizer uses two different methods to recognize when to rewrite a query
    in terms of a materialized view.
•   The first method is based on matching the SQL text of the query with the SQL
    text of the materialized view definition.
•   If the first method fails, the optimizer uses the more general method in which it
    compares joins, selections, data columns, grouping columns, and aggregate
    functions between the query and materialized views.
SEQUENCE
                           Sequence

Sequence :-

• It is a Database Object DBO
• In Oracle, you can create an auto number field by using sequences.
• It generates Numbers Automatically.
• It is having two Pseudo column ( NEXTVAL & CURRVAL )
• This can be useful when you need to create a unique number to act as
  a primary key.
• If you specify the MIN and MAX values then it will generate the values
  in that range.
• If the it exceeds the MAX value and you didn’t specified the CYCLE
  then it will through ERROR.
• Default it will take NO CYCLE
                              Sequence
SQL> Create Sequence S1;

SQL> Create Sequence S2 MINVALUE 1
                       MAXVALUE 100
                       START WITH 1
                       INCREMENT BY 1
                       CATCH     20
                       CYCLE ;

SQL> Create Sequence S3 MINVALUE 1
                       MAXVALUE 100
                       START WITH 1
                       INCREMENT BY 3
                       CATCH 20
                       CYCLE ;

SQL> Create Sequence S2 MINVALUE 4
                       MAXVALUE 100
                       START WITH 2
                       INCREMENT BY 1
                       CATCH 20
                       NO CYCLE ;
•
                          Sequence


SQL> Create Sequence S2 MINVALUE 1
                        MAXVALUE 100
                        START WITH 1
                        INCREMENT BY 1
                        CATCH 20
                        CYCLE ;
SQL>CREATE SEQUENCE seq_order START WITH 1 ORDER;

SQL> Create Sequence S2 MINVALUE 1
                       MAXVALUE 100
                       START WITH 1
                       INCREMENT BY 1
                       CATCH 20
                       CYCLE ;
                              Sequence

SQL>   ALTER   SEQUENCE   seq_inc_by_ten INCREMENT BY 20;
SQL>   ALTER   SEQUENCE   seq_maxval MAXVALUE 10;
SQL>   ALTER   SEQUENCE   seq_cycle NOCYCLE;
SQL>   ALTER   SEQUENCE   seq_cache NOCACHE;
SQL>   ALTER   SEQUENCE   seq_order NOORDER;

SQL> DROP SEQUENCE seq_cache;
SYNONYM
                           Synonym
Synonym :-

• A synonym is an alias for a schema object.
• Synonyms can provide a level of security by masking the name and
  owner of an object .
• Synonyms allow underlying objects to be renamed or moved,
• where only the synonym needs to be redefined and applications
  based on the synonym continue to function without modification.
• You can create both public and private synonyms.
• A public synonym is owned by the special user group named
  PUBLIC and is accessible to every user in a database.
• A private synonym is contained in the schema of a specific user and
  available only to the user and the user's grantees
• We can Performa all DML operations on the Synonym.
                           Synonym


You can define the Synonym on the Following.

1.   Table
2.   Package
3.   View
4.   Materialized view
5.   Sequence
6.   Stored procedure
7.   User-defined object
8.   Function
9.   Synonym
                           Synonym
Private Synonym :-

• A private synonym is contained in the schema of a specific user and
  available only to the user and the user's grantees

Syntax
SQL> Create [PUBLIC] Synonym S_Name
      FOR
       [schema .] object_name ;

Example :-
SQL> Create Synonym S_emp for emp ;
SQL> Create Synonym S_emp for scott.emp. ;
SQL> Create Synonym S_emp for emp ;
                              Synonym
Public Synonym :-

•   A public synonym is owned by the special user group named PUBLIC and is
    accessible to every user in a database.

SQL> Conn scott / tiger @ orcl;
SQL> Show User;
     Scott

    SQL>Select * from emp;
    SQL> Create Public Synonym S_emp for emp;

SQL> Conn user_1 / Uswer_1 @ orcl;
SQL> Show User;
     User_1

SQL>Select * from emp; -------- ERROR
SQL> Select * from S_emp; --- DATA

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:67
posted:9/8/2012
language:Latin
pages:217
Description: Oracle_