Docstoc

GTU MCA Full PL_SQLBy Keval

Document Sample
GTU MCA Full PL_SQLBy Keval Powered By Docstoc
					Structured Query Language

          (SQL)




           Developed By :- Keval.M.Nagaria
                          What is SQL?
• SQL Stands for Structured Query Language.
• SQL is used to communicate with database.
• It is standard language for RDBMS Relational Database
  Management System.
• SQL Statements are perform some task like update data
  in database, retrieve data from database etc…
• Some common RDBMSs that use SQL are : Microsoft
  SQL Server, Oracle, Sybase, Access…
• SQL is made up by three sub languages :
  – Data Definition Language (DDL)
  – Data Manipulation (DML)
  – Data Control Language (DCL)
       Data Definition Language (DDL)
• Data Definition Language (DDL) statements are used to define the
  database structure or schema. Some examples:
• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all
  spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object
      Data Manipulation Language (DML)
   Data Manipulation Language (DML) statements are used for
    managing data within schema objects. Some examples:
   SELECT - retrieve data from the a database
   INSERT - insert data into a table
   UPDATE - updates existing data within a table
   DELETE - deletes all records from a table, the space for the records
    remain
   MERGE - UPSERT operation (insert or update)
   CALL - call a PL/SQL or Java subprogram
   EXPLAIN PLAN - explain access path to data
   LOCK TABLE - control concurrency
      Data Manipulation Language (DML)

   Data Control Language (DCL) statements are used to
    make access control on Data. Some of the Examples are :


   GRANT - gives user's access privileges to database
   REVOKE - withdraw access privileges given with
    the GRANT command
                 Basic Data Types in Oracle
•   There are six basic data types in oracle to store
    different types of data in table format.

 CHAR (size) :-
 • It is used to store the Character type of data.
 • It can hold maximum 255 characters.

 VARCHAR (size) / VARCHAR2 (size) :-
 • It is used to store the variant type of data.
 • It can hold maximum up to 4000 Characters.
 • But CHAR is much faster then VARCHAR2.
       DATE :
    •     It is used to store the Date in table.
    •     The default format to store date is DD-MON-YYYY
    •     For Example : 24-Feb-1985


       NUMBER(P,S) :-
    •     It is used to store the numeric type of data.
    •     It can hold maximum up to 38 digits.
    •     P = Precision S = Scale
    •     Precision is define the total length of data Including Decimal
          point.
    •     Scale shows the position of decimal point in numeric value.
       LONG :-
    •     It is used to store the Variable length character string.
    •     It can be store maximum up to 2GB of data.
    •     Only one LONG value can be define per table.
    •     LONG values can not be used in Subqueries, Functions,
          Expressions, Where clauses or Indexes.


       RAW / LONG RAW :-
    •     This data type is used to store BINARY DATA.
    •     Such as Digitized Picture or Image.
    •     RAW Data type can have a maximum length of 255 bytes.
    •     It can store up to 2GB Data.
Create Table In Oracle


        (SQL)
• Table Basics :-

  – A relational database contain one or more Objects
    called Tables.
  – The Data or Information are stored in this table.
  – Tables are uniquely identify by their name and
    comprised of columns and rows.
  – Columns contain column name, datatypes and any
    other attributes.
  – Row contains the records or data for the columns.
    • Example of Simple Table :-
                              Student
    S_ID        S_Name          Gender       B_Date          City
     101     Hardik            Male        4-Jan-1985      Rajkot

     102     Rohan             Male        24-Feb-1984 Baroda

     103     Shital            Female     31-Mar-1986 Surat

     104     Shrikant          Male        27-Apr-1987 Pune

    S_ID, S_Name, Gender, B_Date, City are the columns.
    Rows contains the data of this table.
 Simple Create Table Command :

  Syntax :-

 CREATE TABLE < Table Name >
 (   <Field Name1> <Data Type> (<Size>),
     <Field Name2> <Data Type> (<Size>),
     <Field Name3> <Data Type> (<Size>),
                        |
                        |
     <Field NameN> <Data Type> (<Size>)
 );
• Example :
• Create table named “Student” with fields S_ID, S_Name, Gender,
  B_Date, Phone.


       CRERATE TABLE STUEDENT
       (
          S_ID VARCHAR2(5),
          S_Name VARCHAR2(30),
          Gender CHAR(1),
          B_Date    DATE,
          Phone     NUMBER(10,0)
       );
   Create Table From another Table :

        Syntax :-

     CREATE TABLE < Table Name >
     ( <Field Name1>, <Field Name2>,
        <Field Name3> <Field Name4> )
     AS
     SELECT <Field Name1>, <Field Name2>,
             <Field Name3>, <Field Name4>
     FROM <Table Name>;
   Example :
   Existing table named “Student” with fields S_ID, S_Name, Gender,
    B_Date, Phone.
   Create New table “Employee” from “Student” :


        CRERATE TABLE EMPLOYEE
        (      E_ID, E_Name, Gender, Phone )
        AS
        SELECT
          S_ID, S_Name, Gender, Phone
        FROM
          STUDENT;
Insert Data Into Table

        (SQL)
   Insert Data into Single Row but all Columns :
      Syntax :-
    INSERT INTO <Table Name> VALUES
    ( ‘<F Value1>’, ‘<F Value2>’, ‘<F Value3>’,
        ‘<F Value4>’,….. ‘<F Value N>’
    );
     Example :-

    INSERT INTO STUDENT VALUES
    ( ‘M101’ , ’Hardik Dave’ , ‘M’ ,
        ’14-MAR-1985’ , 9898544570
    );
   Insert Data into Single Row but Specific Columns
      Syntax :-

     INSERT INTO <Table Name>
     ( <F Name1>, <F Name2>,… <F Name N> )
     VALUES
     ( ‘<F Value1>’, ‘<F Value2>’, …‘<F Value N>’ );

       Example :-
    INSERT INTO STUDENT
         (S_ID, S_Name, Phone)
    VALUES
        (       ‘M101’ , ’Hardik Daave’ , 9898544570   );
   Insert Data into Single Row but all Columns using
    VARIABLES :
      Syntax :-
    INSERT INTO <Table Name> VALUES
    ( ‘<&F Name1>’, ‘<&F Name2>’, ‘<&F Name3>’,
        ‘<&F Name4>’,….. ‘<&F Name N>’
    );
     Example :-

    INSERT INTO STUDENT VALUES
    (   ‘&S_ID’ , &S_Name’ , ‘&Gender’ ,
        ’&Date’ , &Phone
    );
   Insert Data into Single Row but Specific Columns using
    VARIABLES :
       Syntax :-
    INSERT INTO <Table Name>
    ( <F Name1>, <F Name2>,… <F Name N> )
    VALUES
    ( ‘<&F Name 1>’, ‘<&F Name 2>’, …‘<&F Name N>’ );


       Example :-
    INSERT INTO STUDENT
         (S_ID, S_Name, Phone)
    VALUES
        ( ‘&S_ID’ , ‘&S_Name’ , &Phone );
   Insert Data into Table using another table data :
   For that the structure of both the tables, the table from which we
    are entering data and the table into which we are entering data
    must be the same.
       Syntax :-
    INSERT INTO <Table Name>
    SELECT ( <F Name 1>, <F Name 2>, …<F Name N> );
    FROM <Table Name>;
       Example :-
    INSERT INTO STUDENT
    SELECT ( Emp_ID, Emp_Name, Gender, Phone )
    FROM EMPLOYEE;
RETRIVING DATA FROM TABLE


          (SQL)
   RETRIVE All the data (all rows and all columns) :-
      Syntax :-

     SELECT * FROM <Table Name> ;
     (* Is used for all field name)
   RETRIVE data from all rows and specified fields:-
      Syntax :-

     SELECT <Field Name1>, <Field Name2>……….
     FROM <Table Name> ;

   RETRIVE data from specified rows and fields:-
      Syntax :-

     SELECT <Field Name1>, <Field Name2>……….
     FROM <Table Name>
     WHERE <Condition> ;
   Example :-
   Table Name :- STUDENT
   Field Names :- SID, S_Name, Gender, B_Date, Phone

1.)SELECT * FROM STUDENT;

2.)SELECT SID, S_NAME, Gender FROM STUDNT;

3.)SELECT SID, S_Name, Gender
       FROM STUDENT
       WHERE SID = ‘101’ ;
   Example :-
   Table Name :- STUDENT
   Field Names :- SID, S_Name, Gender, B_Date, Phone
   Multiple Conditions :-

1.)SELECT SID, S_Name, Gender
       FROM STUDENT
       WHERE SID = ‘101’ AND S_Name = ‘Jay’ ;           Logical

                                                        Operator
2.)SELECT SID, S_Name, Gender
       FROM STDENT
       WHERE SID = ‘101’ OR SID = ‘102’ ;
   Pattern Matching :-
   Table Name :- STUDENT
   Field Names :- SID, S_Name, Gender, B_Date, Phone

   Now suppose we want some record whose name is starting from A
    then :
     SELECT * FROM STUDENT WHERE S_Name LIKE ‘A%’ ;

   Now we want THIRD character as A then :
    SELECT * FROM STUDENT WHERE S_Name LIKE ‘_ _A%’ ;

   Now we want LAST character as A then :
    SELECT * FROM STUDENT WHERE S_Name LIKE ‘%A’ ;
   Range Selection :-
   Table Name :- MARKS
   Field Names :- SID, Marks1, Marks2……., Percentage

   Now suppose we want some record whose Percentage is in
    between 50% and 70% then:
    SELECT * FROM MARKS
    WHERE Percentage BETWEEN 50 AND 70 ;

   Now we want Percentage Which value is from (50,55,41,78,56)
    then :
    SELECT * FROM MARKS
    WHERE Percentage IN (50,55,41,78,56) ;
   Column Alias and Column Expression :-
   Table Name :- MARKS
   Field Names :- SID, M1, M2, M3, M4, M5, M6, TotalM
   Now Suppose Total subjects are 7 and we want to count percentages from given
    7 subjects marks then :

    SELECT SID,M1,M2,M3,M4,M5,M6,(TotalM/6) AS “Average”
    FROM MARKS;

   AS is the keyword use to give a temporary ALIAS name to newly created
    column.
   Here this command will display all the marks with its average by using
    expression (Total/6) in place of column name.
   Alias name will show as a heading of that column. If alias name is not given
    then expression will show as column head.
DELETE Records From the Table


            (SQL)
   Remember while performing DELETE Operation :

   The DELETE Command will delete the row(s) from the
    table.
   If we specifying WHERE Clause with it then it will delete
    some specific rows which are satisfying the condition
    otherwise it will delete all the rows from the table.
   Structure will remain as it is, means it will not effect to
    the structure of the table.
   Delete all rows from the table :

     Syntax :-
    DELETE FROM <Table Name>;

   Delete Specific rows from the table :

     Syntax :-
    DELETE FROM <Table Name>
    WHERE <Condition> ;
   Example :-
   Table Name : STUDENT
   Fields : S_ID, S_Name, Gender, B_Date, Phone

       If we want to delete all the records from the table whose
        Gender = ‘Female’ :

    DELETE FROM STUDENT
    WHERE Gender = ‘Female’ ;

       DELETE FROM STUDENT will delete all the entries from the
        STUDENT Table.
UPDATE Records into the Table


            (SQL)
   Remember while UPDATING Records :

   The UPDATE Command will use to update the field value
    into the table.
   The Structure of the table will not be changed by using
    UPDATE Command.
   If we use Update command without specifying WHERE
    Clause if will effect to all the values of particular
    columns.
   WHERE clause must be necessary if we want to update
    any specific field value only.
   Update all the Field Values :-
      Syntax :-

     UPDATE <Table Name>
     SET <Field Name> = <New Value>
         [ ,<Field Name> = <New Value>,……..]

   Update some specific Field Values :-
      Syntax :-

     UPDATE <Table Name>
     SET <Field Name> = <New Value>
         [ ,<Field Name> = <New Value>,……..]
     WHERE <Condition>
   Example :-

    UPDATE STUDENT
    SET B_Date = ‘7-Aug-1985’ , Phone = 9898232314;

       The above given command will change B_Date and Phone of
        all the student.
       Now suppose if I want to change B_Date and Phone value of a
        student whose S_ID is ‘101’ then following command will be
        used :

    UPDATE STUDENT
    SET B_Date = ‘7-Aug-1985’ , Phone = 9898232314
    WHERE S_ID = ‘101’;
Modify the Structure of Table


            (SQL)
   If we want to modify the structure of table no data of table then
    ALTER TABLE Command is used .

   For Example :
      If we want to ADD one new COLUMN to the table or

      If we want to modify the existing column then this command
        will used.

   Limitations of ALTER TABLE Command :-

       It can not rename the table name.
       It can not rename the field name.
       It can not decrease the field size If Data exist in that field is
        larger then the size at which you want to decrease.
   ADD new column to the table :-
      Syntax :-

     ALTER TABLE <Table Name>
     ADD ( <New Column> <Data Type>,
           <New Column> <Data Type>……);
   DROPING existing column from table :-
      Syntax :-

     ALTER TABLE <Table Name>
     DROP COLUMN <Column Name>;
   MODIFYING existing column from table :-
      Syntax :-

     ALTER TABLE <Table Name>
     MODIFY (<Field Name> <New Data Type>);
   ADD PRIMARY KEY Constraints to the column :-
      Syntax :-

     ALTER TABLE <Table Name>
     ADD PRIMARY KEY ( <Field Name> );

   DROPING PRIMARY KEY from table :-
      Syntax :-

     ALTER TABLE <Table Name>
     DROP PRIMARY KEY;
   Examples :-
   TABLE : STUDENT
   FIELDS : S_ID, S_Name, Gender, B_Date, Phone

   Add New Column ‘Address’ to STUDENT

    ALTER TABLE STUDENT
    ADD ( Address VARCHAR2(30) ) ;

   DROP column ‘Address’ From STUDENT

    ALTER TABLE STUDENT
    DROP COLUMN ( Address ) ;
   Examples :-
   TABLE : STUDENT
   FIELDS : S_ID, S_Name, Gender, B_Date, Phone

   MODIFY S_Name Field :
   Increase the size of this field to 40
    ALTER TABLE STUDENT
    MODIFY ( S_Name VARCHAR(40) ) ;

   Add PROMARY KEY on ‘S_ID’
    ALTER TABLE STUDENT    ADD PRIMARY KEY ( S_ID ) ;

   DROP Primary from table
     ALTER TABLE STUDENT DROP PRIMARY KEY ;
   ADD FOREIGN KEY Constraint :-
      Syntax :-

     ALTER TABLE <Table Name>
     ADD CONSTRAINT <Cons Name>
     FOREIGN KEY (<Field Name>)
     REFERENCES <Table Name> [<Field Name>] ;

   DROP FOREIGN KEY Constraint :-
      Syntax :-

     ALTER TABLE <Table Name>
     DROP CONSTRAINT <Cons Name>
   Example :-

   ADD FOREIGN KEY Constraint to MARKS on S_ID with giving
    REFERENCE to STUDENT table:-

    ALTER TABLE MARKS
    ADD CONSTRAINT F_Key
    FOREIGN KEY (S_ID)
    REFERENCES STUDENT (S_ID);

   DROP FOREIGN KEY Constraint :-

    ALTER TABLE MARKS
    DROP CONSTRAINT F_Key
DATA CONSTAINTS

    (SQL)
   DATA CONSTRAINTS are nothing but the rules that are
    enforced on data being stored in table.
   You can define a Integrity constraints to enforce business
    rules on data in your table.
   Business rules specify conditions and relationship that
    must always be true or must always be false.
   Because each company defines its own policies about
    things like salaries, employee numbers, inventory
    tracking, and so on, you can specify a different set of
    rules for each table.
   When an integrity constraints apply to a table, all data in
    a table must conform to the corresponding rule.
   DATA CONSTRAINTS are nothing but the rules that are
    enforced on data being stored in table.
   You can define a Integrity constraints to enforce business
    rules on data in your table.
   Business rules specify conditions and relationship that
    must always be true or must always be false.
   Because each company defines its own policies about
    things like salaries, employee numbers, inventory
    tracking, and so on, you can specify a different set of
    rules for each table.
   When an integrity constraints apply to a table, all data in
    a table must conform to the corresponding rule.
               Applying Data Constraints :-
• Oracle permits data constraints to be attached to table
  columns via SQL syntax that checks data for integrity
  prior storage.
• Once data constraints are part of the table column
  construct, the oracle database engine check the data
  being entered into a table column against the data
  constraints.
• If the data passes to this check, it is stored into a table
  column, else the data is rejected.
• Even if the single column of the record being entered into
  the table fails a constraint, the entire record is rejected
  and not stored into the table.
1. Primary Key Constraint
 A primary key is one or more column(s) in a table used to
  uniquely identify each row in the table.
 None of the field that are part of the primary key can contain a
  NULL value.
 A table can have only one primary key.
 A primary key column has a special attributes :
   It define a column as a mandatory column. As a Not Null
    attribute is activated.
   The data held across the column MUST be UNIQUE.
 A single column primary key is called Simple Key.
 A multicolumn primary key is called a Composite Primary
  Key.
   A primary key can be define in either CREATE TABLE
    statement or ALTER TABLE statement.

   Features of Primary Key :-

1. Uniquely Identify the row.
2. Not allow Duplicate and NULL value.
3. Not compulsory but recommended.
4. Can not be LONG or LONG ROW data type.
5. Only one primary key is allowed per table.
6. One table can combine up to 16 columns in a Composite
    Primary Key.
       Syntax :- (With CREATE TABLE)
        Column Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type> Primary Key,
         <column Name> <Data Type>,
         <column Name> <Data Type> ) ;

        Table Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type>,
         <column Name> <Data Type>,
        Primary Key (<CN 1>,<CN 2>…) ) ;
   Syntax :- (With ALTER TABLE)

    ALTERTABLE < Table Name >
    ADD PRIMARY KEY (<column name>);

    ALTERTABLE < Table Name >
    DROP PRIMARY KEY ;
2. Foreign Key Constraint
 Foreign Key represent relationship between tables.
 A Foreign key is a column whose values are derived from
  the Primary Key or Unique Key of some other table.
 The table in which the foreign key is defined is called a
  Foreign Table or Detail Table.
 A table that define Primary Key or Unique Key and is
  referenced by the Foreign Key is called Primary Table or
  Master Table.
 Foreign Key can be defined in either CREATE TABLE or
  ALTERTABLE statement.
    Features of Foreign Key :-

1. Parent that is being referenced has to be Unique or Primary
     Key.
2.   Child may have Duplicate or NULL values unless it specified.
3.   Foreign Key constraint can be specified on child but not on
     parent.
4.   Master table can not be updated if child record record exist.
5.   Parent record can be delete provided on child record exist.
 Records can not be Inserted into a Detail Table
  or Foreign Table if corresponding records in
  master table do not exist.
 Records in the Master Table can not be deleted if
  corresponding record in the detail table actually
  exist.
 Oracle display an error message when a record
  in a master table is deleted and corresponding record
  is exit in detail table and Prevent the Delete
  operation from going through.
   Principles of Foreign Key :-

1. Reject Insert or Update value if corresponding value does
   not exist in master table.
2. If ON DELETE CASCADE option is set, a DELETE operation
   in master table will trigger Delete operation for corresponding
   records in all detail tables.
3. If ON DELETE SET NULL option is set, a DELETE operation
   in master table will set the value held by the foreign key of the
   detail table to NULL.
4. DataTypes must be MATCHED.
       Syntax :- (With CREATE TABLE)

        Column Level :-

CREATE TABLE <Table Name>
( <Column Name> <Data Type>,
  <Column Name> <Data Type>
  REFERENCES <Master Table Name> [<Column Name>]
  [ON DELETE CASCADE | ON DELETE SET NULL] ,
 <Column Name> <Data Type>
);
       Syntax :- (With CREATE TABLE)

         Table Level :-

CREATETABLE <Table Name>
( <Column Name> <Data Type>,
  <Column Name> <Data Type>,
  <Column Name> <Data Type>,
CONSTRAINT <Const Name>
FOREIGN KEY <Column Name> REFERENCES <Table Name> [<Column
    Name>]
  [ON DELETE CASCADE | ON DELETE SET NULL]
);
   Syntax :- (With ALTER TABLE)

ALTERTABLE <Table Name>
ADD CONSTRAINT <Const Name>
FOREIGN KEY <Column Name> REFERENCES <Table Name> [<Column
   Name>]
 [ON DELETE CASCADE | ON DELETE SET NULL]
);

   To DROP Constraint :-

ALTERTABLE <Table Name>
DROP CONSTRAINT <Const Name> ;
3. Unique Key Constraint
    A Unique column constraint permits multiple entries of NULL into the
     column.
    These NULL values are clubbed at the top of the column in the order in
     which they are entered in to the table.
    Key Points about Unique Constraint :-
1.   Will not allow Duplicate values.
2.   Unique index is created automatically.
3.   A table can have more then one Unique Keys that is not possible in
     Primary Key.
4.   Unique Key can combine up to 16 columns in one Composite Unique
     Key.
5.   It can not apply on LONG and LONG RAW data type.
       Syntax :-
        Column Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type> UNIQUE,
         <column Name> <Data Type>,
         <column Name> <Data Type> ) ;

        Table Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type>,
         <column Name> <Data Type>,
        UNIQUE (<CN 1>,<CN 2>…) ) ;
4. Check Key Constraint
 Oracle applies Business Rules to the table columns.
 Business Rules can be implemented in Oracle by
  using CHECK constraint.
 Business rule validation checks are performed when
  any table write operation is carried out.
 Any INSERT or UPDATE statement cause the
  relevant check constraint to be involved.
       Syntax :-
        Column Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type> CHECK <Condition>,
         <column Name> <Data Type>,
         <column Name> <Data Type> ) ;

        Table Level.

        CREATETABLE < Table Name >
        ( <column Name> <Data Type>,
         <column Name> <Data Type>,
        CONSTRAINT <Const Name> CHECK <Condition>) ;
TABLE JOINING


    (SQL)
   Sometimes it is necessary to work with multiple tables as
    though they were a single Entity.
   Then a single SQL sentence can manipulate data from all
    the tables and JOINS are used to achieve this.
   Tables are joined on columns that have the same data
    types and data width in the table.
   Tables in database can be related to each others with
    keys.
   Types of Join :- 1.) INNER JOIN / EQUI JOIN
                            2.) OUTER JOIN (Right & Left)
                            3.) CROSS JOIN
                            4.) SELF JOIN
   INNER JOIN :-

   Inner Joins are also known as Equi Join. This is the most
    common join used in SQL *PLUS.
   In fact many systems are use this type as a default Join
    type.
   This type of Join is used in a situation where selecting
    only those rows that have values in common in the
    columns in both parent table as well as child table.
   In sort Inner Join will return all the rows from both the
    tables where they are matched.c
   Syntax :-

 ANSI-Style :-
SELECT <Field Name1>, <Field Name2>…<Field Name N>
FROM <Table Name 1> INNER JOIN <Table Name 2>
ON <TN1>.<Field Name1> = <TN2>.<Field Name2>
WHERE <Condition>

 Theta-Style :-
SELECT <Field Name1>, <Field Name2>…<Field Name N>
FROM <Table Name1> <Alias 1>, <Table Name2> <Alias 2>
WHERE <Alias 1>.<Field Name1> = <Alias 2>.<Field Name2>
AND <Condition>
   Example :-
          Table 1 (STUDENT)
    SID                          S_Name       Gender          B_Date              Phone
    101         Parimal Desai               Male        4-Jan-1985         9898565632
    102         Hardik Trivedi              Male        24-Feb-1984        9988541245
    103         Hetal Joshi                 Female      31-Mar-1986        9554482631
    104         Jashmine Devid              Female      27-Apr-1987        9563214785
    105         Pratik Parmar               Male        21-Jun-1985        9887745216

          Table 2 (MARKS)
          SID                       Mark1     Mark2              Mark3              Total
          101                        89            85                 68            242
          102                        78            48                 63            189
          105                        74            42                 58            174
          108                        71            54                 64            189
          110                        75            75                 74            124
SELECT S.SID, S.S_Name, M.Mark1, M.Mark2, M.Mark3, M.Total
FROM STUDENT S INNER JOIN MARKS M
ON S.SID = M.SID WHERE Total > 180 ;

   This Query will give output of the rows which are heaving a common SID
    in both the tables and also have Total > 180
   So there are 3 records heaving common SID in both the tables but only
    tow of them have more then 180 Total.

   OUTPUT :-
    SID              Name           Mark1     Mark2     Mark3      Total

    101    Parimal Desai             89        85         68        242

    102    Hardik Trivedi            78        48         63        189
   OUTER JOIN :-

   Outer Joins are similar to the Inner Join, but give a bit
    more flexibility when selected data from related tables.
   This type of Join can be used in a situation where it is
    desired to select all the rows from the table on the Left
    or Right regardless of whether the value is there in other
    table or not.
   If there is no common record in other table then it will
    put NULL value at that place but the first tabl value will
    be display.
   Syntax :-

    1.) LEFT OUTER JOIN :-

       ANSI-Style :-

         SELECT <Field Name1>, <Field Name2>…<Field Name N>
         FROM <Table Name 1> LEFT JOIN <Table Name 2>
         ON <TN1>.<Field Name1> = <TN2>.<Field Name2>
         WHERE <Condition>

       Theta-Style :-

         SELECT <Field Name1>, <Field Name2>…<Field Name N>
         FROM <Table Name1> <Alias 1>, <Table Name2> <Alias 2>
         WHERE <Alias 1>.<Field Name1> = <Alias 2>.<Field Name2>(+)
         AND <Condition>
   Syntax :-

    1.) RIGHT OUTER JOIN :-

       ANSI-Style :-

         SELECT <Field Name1>, <Field Name2>…<Field Name N>
         FROM <Table Name 1> RIGHT JOIN <Table Name 2>
         ON <TN1>.<Field Name1> = <TN2>.<Field Name2>
         WHERE <Condition>

       Theta-Style :-

         SELECT <Field Name1>, <Field Name2>…<Field Name N>
         FROM <Table Name1> <Alias 1>, <Table Name2> <Alias 2>
         WHERE <Alias 1>.<Field Name1>(+) = <Alias 2>.<Field Name2>
         AND <Condition>
   Example :-
       Table 1 (EMP)




       Table 2 (EMP_ADD)
         SELECT E.EMPNO, E.ENAME, EA.ADDRESS, EA.PHONE
         FROM EMP E LEFT JOIN EMP_ADD EA
         ON E.EMPNO = EA.EMPNO

         • Above command will produce following OUTPUT




   It means LEFT JOIN will show all the records from <Table 1>, but it will show
    only common records from <Table2>.
   In RIGHT JOIN the thing will be different from LEFT JOIN.
   CROSS JOIN :-

   CROSS JOIN can be used in a situation where it is desired
    to compare each and every row of one table with each
    and every row of another table.

   Syntax :-

    SELECT <FieldName1>,<FieldName2>…<FieldName N>
    FROM <Table Name1> CROSS JOIN <Table Name 2> ;
   Example :-




   It will Compare each row from EMP with each of EMP_ADD and produce
    output.
   So 4 rows from EMP and 3 rows from EMP_ADD means 4 X 3 = 12 rows will
    be display as OUTPUT.
   OUTPUT :-
   SELF JOIN :-

   In some situation it is necessary to join a table to itself, as thought
    join two separate tables. This is refer to as a Self Join.
   To join a table to itself, two of the same table have to be opened in
    memory. Hence in the FROM clause the table name need to
    mention twice.
   Since the table names are the same, the second table will
    overwrite the first table and in effect, result in only one table
    being in memory. This is because the table name is translated into
    a specific memory location. To avoid this each table is opened
    using a ALIAS.
   Now this table aliases will cause two identical tables to be opened
    in different memory locations.
   Syntax :-
SELECT <Alias1>.<FieldName1>,<Alias1>.<FieldName2>,
            <Alias2>.<FieldName3>…
   FROM <TableName1> <Alias>, <TableName2> <Alias2>
   WHERE <Condition> ;
   Example :-    Emp_No          Fname           Lname        Mngr_No
                  E1       Ivan           Bayross
                  E2       Amit           Desai
                  E3       Maya           Joshi
                  E4       Peter          Joseph          E2
                  E5       Mandhar        Dalvi
                  E6       Sonal          Khan            E1
                  E7       Anil           Kambli          E5
                  E8       Seema          Apte            E3
                  E9       Vikram         Randiv          E7
                  E10      Anjali         Pathak          E1
    SELECT EMP.FNAME “EMPLOYEE”, MNGR.FNAME “MANAGER”
    FROM EMP_MASTER EMP, EMP_MASTER MNGR
    WHERE EMP.MNGR_NO = MNGR.EMP_NO;

   Above Query will Produced the following OUTPUT :-

                   EMPLOYEE                MANAGER
          Peter                  Amit
          Sonal                  Ivan
          Anil                   Mandhar
          Seema                  Maya
          Vikram                 Anil
          Anjali                 Ivan
   NATURAL JOIN :-

    A natural join offers a further specialization of equi-
    joins. The join predicate arises implicitly by comparing
    all columns in both tables that have the same column-
    name in the joined tables. The resulting joined table
    contains only one column for each pair of equally-
    named columns.

    Syntax :-

    SELECT *
    FROM <Table Name1> NATURAL JOIN <Table Name2>
   Example:-

    SID           NAME              SID             M1             M2        M3
101       Raj                 101              87             77        65
102       Jay                 102              65             47        85
103       Vijay               103              66             74        84
104       Shital

SELECT * FROM STUDENT NATURAL JOIN MARKS ;

    Above Query produced following OUTPUT.
    SID           NAME        M1               M2             M3
101       Raj            87               77             65
102       Jay            65               47             85
103       Vijay          66               74             84
             GROUP BY Clause
• The GROUP BY Clause is the another section
  of SELECT Statement.
• This optional clause tell oracle to group rows
  based on distinct values that exist for specified
  columns.
• The GROUP BY Clause creates a data set,
  containing several sets of records grouped
  together based on condition.
 Syntax :-


  SELECT <Field Name1>, <Field Name2>, <Field Name N>
  AGGREGATE FUNCTION ( <Expression> )
  FROM < Table Name > WHERE < Condition >
  GROUP BY <Field Name1>,…<Field Name N> ;



 Example :-


  SELECT Class, COUNT( SID ) AS “No Of Student”
  FROM MARKS
  GROUP BY Class;
 HAVING Clause :-
 HAVING Clause imposes a condition in the GROUP BY Clause,
  which further filter the group created by GROUP BY clause.
 HAVING Clause must occur within a statistical function or must
  occur in a list of columns named in GROUP BY clause.
 Syntax :-
  SELECT <Field Name1>, <Field Name2>, <Field Name N>
  AGGREGATE FUNCTION ( <Expression> )
  FROM < Table Name > WHERE < Condition >
  GROUP BY <Field Name1>,…<Field Name N>
  HAVING (<condition>) ;
 Example :-
  SELECT Class, COUNT( SID ) AS “No Of Student”
  FROM MARKS
  GROUP BY Class HAVING COUNT(SID) >= 2;
 RULES for GROUP BY and HAVING Clause :-


 Columns listed in the SELECT statement have to be
  listed in GROUP BY clause.
 Columns listed in GROUPBY clause need not to be listed
  in SELECT statement.
 Only group function can be used in the HAVING clause.
 The group functions listed in the HAVING clause need
  not be listed in the SELECT statement.
           AGGREGATE FUNCTIONS
1.) AVG ( [ Distinct | All ] <n>)
   SELECT AVG(M1), AVG(M2), AVG(M3), AVG(M4),
   AVG(M5),AVG(M6) FROM MARKS;
 AVG(M1)   AVG(M2)       AVG(M3)   AVG(M4)      AVG(M5)   AVG(M6)
  56.65     85.23        75.214    45.6365      65.325     77.00

2.) MIN ( [ Distinct | All ] <n>)
   SELECT S.Gender, MIN(M.Per) “Min Per” FROM MARKS M,
   STUDENT S WHERE S.SID = M.SID GROUP BY S.Gender;
                Gender               Min Per
                     F              45.658965
                     M              71.568456
3.) MAX ( [ Distinct | All ] <n>)
    Reverse of MIN

4.) Count ( [ Distinct | All ] <n>)

    COUNT(*) display the total No. of ROWS.

    SELECT Gender, COUNT(*) FROM STUDENT GROUP BY Gender;
           Gender             COUNT(*)
              F                     3
             M                      4

    SELECT COUNT(*) FROM STUDENT GROUP BY Gender;
            COUNT(*)
                  7
5.) SUM ( [ Distinct | All ] <exp>)

    SUM will display the total for the particular FIELD Values.

    SELECT S.Gender, SUM(M.M1) “M1”
    FROM STUDENT S, MARKS M
    WHERE S.SID + M.SID GROUP BY S.Gender ;

            Gender                    M1
               F                      457
              M                       785
               Numeric Functions
1.) ABS ( n )
  ABS function will return the absolute value for ‘n’.
  e.g. :- ABS (-12) ==> Return 12

2.) POWER ( m,n )
  POWER function will return value for ‘m’ raised to
  the power of ‘n’.
  e.g. :- POWER ( 3,2 ) ==> Return 9
3.) ROUND ( n[,m] )
  ROUND function will return the rounded value for ‘n’ up to the ‘m’
  position.
  e.g. :- ROUND (13.5468,3) ==> Return 13.547

4.) SQRT ( n )
  SQRT function will return Square root of ‘n’.
  e.g. :- SQRT ( 25 ) ==> Return 5

5.) EXP ( n )
  EXP function will return ‘e’ raised to the power of ‘n’.
  WHERE ‘e’ = 2.71828183
  e.g. :- EXP ( 5 ) ==> Return 148.413159
6.) EXTRACT ( {year|month|day|hour|minute|second}
    FROM {Date Value} )
  EXTRACT function will return the particular value from your
  date value.
  e.g. :- EXTRACT ( YEAR FROM SYSDATE)
              Return ==> 2009

7.) GREATEST(exp1,exp2…,exp3)
Return the greatest value written in expression list.
e.g. : GREATEST(4,5,17) Return ==> 17
       GREATEST(‘4’,’5’,’17’) Return ==> 5
8.) LEAST(exp1,exp2…,exp3)

 Return the lowest value written in expression list.
 e.g. : LEAST(4,5,17) Return ==> 4
    LEAST (‘4’,’5’,’17’) Return ==> 17

9.) MOD(m,n)

 Return the reminder of the first value divided by the second
 value passed in parameter.
 e.g. :- MOD ( 15,10) return ==> 5
10.) TRUNC(number, [Decimal Place])
 Return a number truncated to a certain number of decimal
  place. Default decimal place is 0(Zero)

 e.g. :- TRUNC( 12.23234 ) return ==> 12
         TRUNC( 12.23234 , 2 ) return ==> 12.23
         TRUNC(125.435 , -1) return ==> 120
         TRUNC(125.3234 , -2) return ==> 100

 If decimal place is of negative value it will reduce that
 much place from the left side of decimal point and
 replace it with 0 (Zero).
11.) FLOOR(number)
  Return a largest inter value that is equal to or less then the
  number.
  e.g. :- FLOOR( 12.23234 ) return ==> 12
           FLOOR( 12.9856 ) return ==> 12
          FLOOR( -12.9856 ) return ==> -13
12.) CEIL(number)
  Return a smallest inter value that is equal to or greater then
  the number.
  e.g. :- CEIL( 12.23234 ) return ==> 13
           CEIL( 12.9856 ) return ==> 13
          CEIL( -12.9865 ) return ==> -12
                String Functions
1.) LOWER(char)
Return character with all letters in Lowercase.
e.g. :- LOWER(‘SHRI RAM’) ==> shri ram
2.)UPPER(char)
Return character with all letters in Uppercase.
e.g. :- UPPER(‘shri ram’) ==> SHRI RAM
3.)INITCAP(char)
  Return a string with first character of each word
  in Uppercase
e.g. :- INITCAP(‘SHRI RAM’) ==> Shri Ram
4.) SUBSTR(<String>,<Start>[,<length>])

• Return a part of the string starting at the position
  <Start> up to the specified Length.
• If Length is not specified then it will taqke up to the end
  of string.

e.g. :- SUBSTR(‘sardar patel’,3) ==> rdar patel
       SUBSTR(‘sardar patel’,3,6)==> rdar p
5.) ASCII(Single character)
  Return the number code that represents the specified
  character.
e.g. :- ASCII(‘a’) ==> 97   ASCII(‘A’)==> 65

6.) INSTR(<string1>,<string2>[,<start position>,
  <nth_appearance>] )

Return the location of the string2 in string1.
e.g. :- INSTR(‘sardar patel’,’r’) ==> 3
       INSTR(‘sardar patel’,’r’,4) ==> 6
       INSTR(‘sardar patel’,’a’,4,2) ==> 9
7.) LENGTH(<String>)
  Return the total length of the string.
e.g. :- LENGTH(‘sardar patel’) ==> 12

8.) LTRIM(string[,set])
  Remove characters from the Left of the string.
  e.g. :- LTRIM(‘sardar patel’,’s’) ==> arder patel
          LTRIM(‘sardar patel’,’sar’) ==> dar patel
9.) LTRIM(string[,set])
Remove characters from the Right of the string.
  e.g. :- RTRIM(‘sardar patel’,’l’) ==> sarder pate
          RTRIM(‘sardar patel’,’tel’) ==> sardar pa
10.) TRIM ( [leading | trailing | both [<trim_char>FROM] ]
       <string> )
  Return the Trimmed string from the specified side.
e.g. :- TRIM(‘ RAJ ‘) ==> RAJ
       TRIM(LEADING’x’ FROM ‘xxxRAMxx’) ==>RAMxx
       TRIM(TRAILING’x’ FROM ‘xxRAMxxx’) ==>xxRAM
       TRIM(BOTH’x’ FROM ‘xxRAMxx’) ==> RAM

TRIM(BOTH’1’ FROM ‘112ed12313111’) ==> 2ed12313
11.) TRANSLATE(<string>, <string_to_replace>,
  <replacement_string> )

  It will replace the replacement string into the original
  string with the string to replace.

e.g. :-

TRANSLATE(‘1sc1t523’,’123’,’7a9’) ==> 7sc7t5a9

  It will replace all ‘1’ with ‘7’ and all ‘2’ with ‘a’ and all ‘3’
  with ‘9’
12.) LPAD(<string>,n[,char])

 Return Left-padded string to length ‘n’ with the
 sequence of characters specified in ‘char’.
 If ‘char’ is not specified then oracle use ‘blank space’.

e.g. :- LPAD(‘RAJ’,5,’*’) ==> RAJ*****
       LPAD(‘RAJ’,4,’#’) ==> RAJ# # # #
13.) RPAD(<string>,n[,char])

 Return Right-padded string to length ‘n’ with the
 sequence of characters specified in ‘char’.
 If ‘char’ is not specified then oracle use ‘blank space’.

e.g. :- RPAD(‘RAJ’,5,’*’) ==> *****RAJ
       RPAD(‘RAJ’,4,’#’) ==> # # # #RAJ
               DATE Functions
1.) TO_DATE (date [, fmt])
  It will convert the character field to date field.
e.g. :- TO_DATE(‘11-11-2009’,’DD-MM-YYYY’)
       11-NOV-2009
  Because oracle can store date in ‘DD-MON-
  YYYY’ Format

e.g. :- TO_DATE(‘03-31-08’,’MM-DD-YY’)
       31-MAR-2008
2.) TO_CHAR (date [, fmt])
  It will display date in specified format
e.g. :- TO_CHAR(SYSDATE,’DD-MM-YYYY’)
       20-09-2009
TO_CHAR(SYSDATE,’Mon-DD-YY’) Sep-31-08
TO_CHAR(SYSDATE,’DAY’) ==> SUNDAY
TO_CHAR(SYSDATE,’Month’) ==> September

Three special Date Formats are used…(With Number)
1.) SP
2.) TH
3.) SPTH
e.g. :-
TO_CHAR(SYSDATE,’DDSP-Mon-YYYY’)
Twenty – Sep – 2009


TO_CHAR(SYSDATE,’DDTH-Mon-YYYY’)
20th – Sep – 2009


TO_CHAR(SYSDATE,’DDSPTH-Mon-YYYY’)
Twentieth – Sep - 2009


TO_CHAR(‘25-jan-2005’,’DDSPTH/Month/YYYYSP’)
Twenty-fifth / September / Two Thousand - Nine
3.) ADD_MONTHS(Date,n)

Return the date after adding the number of months.

e.g. :- ADD_MONTHS(SYSDATE,2)
        20-Nov-2009

ADD_MONTHS(’28-Feb-2008’,2)==> 30-Apr-2008
ADD_MONTHS(‘31-Dec-2009’,3)==> 31-Mar-2010
4.) LAST_DAY(Date)

 Return the Last day of the month specified with the
 function

e.g. :- LAST_DAY(SYSDATE)
       30-Nov-2009

LAST_DAY(’20-Feb-2000’)==> 29-Feb-2009
5.) MONHS_BETWEEN(Date1,Date2)

 Return the Number of months between Date1 and
 Date2

MONTHS_BETWEEN(‘2-feb-2009’,’2-jan-2009’)==> 1

MONTHS_BETWEEN(‘2-feb-2009’,’2-jan-2008’)==>13

MONTHS_BETWEEN(‘2-feb-2007’,’2-Apr-2007’)==>-2
6.) NEXT_DAY(Date1,char)

 Return the Date of the first weekday named by char
 that is after the date named by char.
 char must be day of the week.
SYSDATE = 20-Sep-2009 SUNDAY

NEXT_DAY(SYSDATE,’MONDAY’)=> 21-Sep-2009

NEXT_DAY(SYSDATE,’THRUSDAY’)=> 24-Sep-2009

NEXT_DAY(SYSDATE,’SUNDAY’)=> 28-Sep-2009
7.) ROUND(Date1[,UNIT])

 Return the Date Rounded to the specific unit of
 measure.
SYSDATE = 20-Sep-2009 SUNDAY

ROUND(SYSDATE,’YEAR’) => 1-JAN-2009
ROUND(SYSDATE,’MONTH’) => 1-OCT-2009
 If DD I greater then 15 then it will round to the next
 month else it will round to the current month.
ROUND(12-SEP-2009,’MONTH’) => 1-SEP-2009
Unit                Rounding Rule
YYYY    Round up on July 1st
        Same Day of the week as a first day of
WW
        YEAR
        Same Day of the week as a first day of
 W
        MONTH
MONTH   Round up on July 1st
                     INDEXES
• Data retrieval from the table buy using an index is
  much faster then data retrieval from the table
  where indexes are not defined.
• Oracle allow two types of Indexes :-
1. Duplicate Index :-
   Indexes that allow duplicate values for the indexed
   column are called Duplicate Indexes.
2. Unique Index :-
   Indexes that deny duplicate values for the indexed
   column are called Unique Indexes.
             Creation of an INDEX
• An index can be created on one or more columns.
  Based on the number of columns included in the index,
  an index can be :
1. Simple Index :-
   An index created on a single column is called simple
   index
1. Complex Index :-
   An index created on multiple columns is called
   Complex Index.
 Creation of Simple Index :-


    Syntax :-
   CREATE INDEX <index name>
   ON <table name>
   (<column name>);

    Example :-
   CRATE INDEX ind_student
   ON STUDENT (SID);
 Creation of Composite Index :-


    Syntax :-
   CREATE INDEX <index name>
   ON <table name>
   (<column name>,<column name2>…);

    Example :-
   CRATE INDEX ind_student
   ON STUDENT (SUENAME,FNAME,LNAME);
 Creation of Unique Index :-
 An Unique Index can be created on one or more columns.
 If an index is created on a single column, it is called a
 Simple Unique Index.
   Syntax :-
   CREATE UNIQUE INDEX <index name> ON <table name>
   (<column name>);
 If an index is created on more then one columns, it is
 called a Composite Unique Index.
   Syntax :-
   CREATE UNIQUE INDEX <index name> ON <table name>
   (<column name>,<column name2>…,<column name N>);
 Function Based Index :-
 A column’s index will not be used when the same column is
  expressed in an arithmetic expression or function in the
  WHERE clause.
 To facilitate such an operation oracle allows creating index
  based on function or expression mapped to one or more
  columns in a table.
 Function based indexes are very useful when a WHERE clause
  contains function or expression to evaluate query.
    Syntax :-
   CREATE INDEX <index name> ON <table name>
   (<Function>(<column name>)) ;
    Example :-
   CREATE INDEX ind_student ON STUDENT UPPER(SNAME);
 Dropping Index :-
 Indexes associated with the table can be remove by using
  DROP INDEX command.
    Syntax :-
   DROP INDEX <index name> ;
    Example :-
   DROP INDEX ind_student ;


 Multiple Indexes on a Table :-


 Oracle prepare a query plan to decide on the index that must be
  used for specific data retrieval based on the WHERE or ORDER BY
  Clause specified in SELECT statement.
 Whenever a SELECT statement is executed, The Oracle
  engine prepares a query plan identifying the data retrieval
  method.
 The query plan hold the name off the table from which
  the will be retrieved and name of the index that must be
  used for data retrieval.
 If a WHERE Clause or an ORDER BY Clause is specified, the
  oracle uses the index created on a column on which
  WHERE Clause or an ORDER BY Clause is specified.
 If there is no Index for a column in the WHERE Clause or
  an ORDER BY Clause is specified, that is retrieved without
  index.
                 SUB QUERIES
• A SUBQUERY is a form of an SQL statement that
  appears inside another SQL statement.
• It is also termed as NESTED QUERY.
• The statement containing a Subquery is called
  PARENT statement. The parent statement uses the
  rows returned by subquery.
• It can be used to provide values for conditions in
  WHERE, HAVING, IN and so on used with SELECT,
  UPDATE, and DELETE statement.
• The result of subquery can be used as a table in
  SELECT statement after the FROM key word.
 Example :-


SELECT ROWNUM,M1,M2,M3,M4,M5,"TOTAL","PER" FROM
(
  SELECT       M1,M2,M3,M4,M5,M6,(M1+M2+M3+M4+M5+M6)
  "TOTAL",ROUND((M1+M2+M3+M4+M5+M6)/6,2) "PER" FROM
  MARKS ORDER BY "PER" DESC
)
 WHERE ROWNUM < 4 ;
 Example :-


 Sub query with IN :-


SELECT ROWNUM,M1,M2,M3,M4,M5,"TOTAL","PER"
FROM MARKS WHERE SID IN
( SELECT SID
  FROM STUDENT WHERE CLASS = ‘MCA’ ) ;
 Example :-


 Multicolumn Sub query :-


SELECT ROWNUM,M1,M2,M3,M4,M5,"TOTAL","PER"
FROM MARKS WHERE (SID,SNAME) IN
( SELECT SID,SNAME
  FROM STUDENT WHERE CLASS = ‘MCA’ ) ;
         CASE Statement in Query
SELECT ename, empno,
(CASE
  WHEN sal < 1000 THEN 'Low'
  WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
  WHEN sal > 3000 THEN 'High'
  ELSE 'N/A‘
END) salary
FROM emp
ORDER BY ename ;
SELECT M1,M2,M3,M4,M5,M6,TOTAL,PER,
(CASE
  WHEN PER BETWEEN 40 AND 50 THEN 'PASS'
  WHEN PER BETWEEN 50 AND 60 THEN 'SECOND'
  WHEN PER BETWEEN 60 AND 70 THEN 'FIRST'
  WHEN PER BETWEEN 70 AND 100 THEN 'DISTINCTION'
  ELSE 'FAIL'
END) GRADE
FROM
(
SELECT M1,M2,M3,M4,M5,M6,(M1+M2+M3+M4+M5+M6) TOTAL,
  (M1+M2+M3+M4+M5+M6)/6 PER
FROM MARKS
)
                    UNION CLAUSE
• Multiple queries can be put together and their
  output can be combined using the UNION Clause.
• The UNION Clause merges the output of two or
  more queries into a single set of rows and columns.


     Records      Common        Records
     only in      Records       only in
     Query one    from Both     Query two
                  Queries
UNION               A               B



        Records         Common            Records
        only in         Records           only in
        Query one       from Both         Query two
                        Queries
             A-B                        B-A




 UNION


   INTERSECTION
 Following points can be consider with UNION :-


• The number of columns and the data types of the
    columns being selected must be identical in all the SELECT
    statements used in the query. The name of the column
    need not be identical.
•   UNION operates over all the columns being select.
•   NULL values are not ignored when duplicate checking.
•   The IN operator has a higher precedence then the UNION
    operator.
•   By default the output is stored in ascending order of the
    first column of the SELECT clause.
 Syntax :-


     <SELECT Statement 1>
     UNION
     <SELECT Statement 2>


   Example :-

    SELECT SID,SNAME FROM STUDENT
    UNION
    SELECT ENO,ENAME FROM EMP ;
            INTERSECT CLAUSE
• Multiple queries can be put together and their
  output combined using intersect clause.
• The INTERSECT clause outputs only rows
  produced by both the queries intersected. (rows
  retrieve common to both).
 Syntax :-
      <SELECT Statement 1>
      INTERSECT
      <SELECT statement 2>
                MINUS CLAUSE
• Multiple queries can be put together and their
  output combined using intersect clause.
• The MINUS clause outputs rows produced by first
  query after filtering the rows retrieved by second
  query. Means the rows produced by first query but
  not in second query.
 Syntax :-
      <SELECT Statement 1>
      MINUS
      <SELECT statement 2>
                      VIEWS
• After a table is created and populates with data, it
  may become necessary to prevent all users from
  accessing all columns of table, for data security
  reasons.
• To reduce redundant data to the minimum
  possible, Oracle allows the creation of an object
  called VIEW.
• A view is mapped, to a SELECT sentence.
• The table on which the view is based is described in
  the FORM clause of the SELECT statement.
• The SELECT clause consists of a sub-set of the
  columns of a table.
 Thus a view, which is mapped to a table will in effect have
    a sub-set of the actual columns of the table from which it
    is built.
   This technique offers a simple way of hiding columns of a
    table.
   An interesting fact about a View is that it is stored only a
    definition of Oracle’s system catalog.
   When a reference is made to a View, its definition is
    scanned , the based table is opened and the view created
    on top of the base table.
   Hence a view holds no data at all, until a specific call to a
    view is made.
   Oracle engine treats a View just as through it was a based
    table.
 Some Views are used only for looking at table data. Other
  Views can be used to Insert, Update and Delete table data
  as well as View data.
 If the view is used only to looking table data is called
  Read-Only View.
 A View is used to look at table data as well as Insert,
  Update and Delete table data is called Updateable View.
 Reasons why Views are created :
1. When data security is required.
2. When data redundancy is to be kept to the minimum
    while maintaining data security.
 Creating a View :-


    Syntax :-


   CREATE VIEW <View Name> AS
   SELECT <Col Name 1>,<Col Name 2>,…..
   FROM <Table Name> WHERE <condition>
   GROUP BY <Col Name> HAVING <condition> ;

    Selecting Data set from View :-


   SELECT <CN1>,<CN2>…. FROM <View Name> ;
 Updateable View :-
 View can also be used for data manipulation.
 View on which data manipulation can be done are called
  Updateable View.
 When an Updateable View name is given in an Insert,
  Update and Delete SQL statement, modifications to data
  in the view will be immediately passed to the underlying
  table.
 For view to be update it should meet following criteria :
1. Views defined from Single Table.
2. If user want to insert records with the help of a view,
    then the primary key column(s) and all the NOT NULL
    columns must be included.
3. The user can Update, Delete records with the
  help of view even if the PRIMARY KEY column
  and NOT NULL column are excluded from the
  view.
               SEQUENCE
• Syntax :-
CREATE SEQUENCE <Seq. Name>
[    INCREMENT BY <Integer Value>
     START WITH <Integer Value>
     MAXVALUE <Integer Value> / NOMAXVALUE
     MINVALUE <Integer Value> / NOMINVALUE
     CYCLE / NOCYCLE
     CACHE <Integer Value> / NOCYCLE
     ORDER / NOORDER ] ;
 Keywords and Parameters :-
1.) INCREEMENT BY : Specify the interval between sequence
    numbers. It can be any positive or negative value but not
    Zero.
2.) MINVALUE : Specify the sequence Minimum Value.
3.) NOMINVALUE : Specifies a minimum value of 1 for an
    ascending sequence and –(10)^26 for a descending
    sequence.
4.) MAXVALUE : Specify the maximum value for a seq.
5.) NOMAXVALUE : 10^27 for ascending and -1 for
    descending. This is default clause.
6.) START WITH : Specify the first sequence number to
    generate. Default for asc 1 and for desc -1.
7.) CYCLE : Specify that the sequence continue to generate
    repeat values after reaching either its maximum value.
8.) NOCYCLE : Specify that sequence can not generate more
    value after reaching maximum value.
9.) CACHE : Specify how many values of a sequence oracle
    per-allocates and keeps in memory for faster access. The
    min value for this is 2.
10.) NOCACHE : Specify that value for sequence are not pre-
    allocate.
11.) ORDER : Sequence numbers are generate in order of
    request.
12.) NOORDER : Sequence number does not generated in
    order of request.
•   Example :-
    CREATE SEQUENCE stud_sid
    INCREMENT BY 1 START WITH 1
    MINVALUE 1 MAXVALUE 999 NOCYCLE ;

    SELECT <seq. name>.NextVal FROM DUAL ;

INSERT INTO student VALUES
( stud_sid.NextVal,’Patel Raj’,’M’,’10-JAN-1985’) ;
PL/SQL
           Introduction to PL / SQL
•  Though SQL is a natural language of the DBA, it suffers from
  various disadvantages like :
1. SQL does not have any Procedural Capacities i.e. SQL does not
   provide the programming techniques of condition checking,
   looping and branching that is vital for data testing before
   permanent storage.
2. SQL statements are passed to the Oracle Engine one at a time.
   Each time an SQL statement is execute, a call is made to the
   engine’s resource. This add the traffic to the network in multi-
   user environment.
3. While processing an SQL statement if an error occurs, the
   oracle engine displays its own message. SQL has no facility for
   programmed handling of error that arise during the
   manipulation of data.
 Although   SQL is a very powerful tool, its set of
  disadvantages prevent, it from being a fully
  structured programming language. For fully
  structure programming language SQL produce a
  PL/SQL.
 PL/SQL is a block-structured language that enables
  developers to combine the power of SQL with
  procedural statement.
 PL/SQL    bridges the gap between database
  technology and procedural programming language.
    Advantages of PL/SQL :-
1.    PL/SQL is a developing tool that not only support data
      manipulation but also provides facilities of conditional
      checking, branching and looping.
2.    PL/SQL send an entire block of SQL statements to the oracle
      engine all in one go. Communication between the program
      block and oracle engine reduce considerable, reducing
      network traffic.
3.    PL/SQL also permits dealing with error as required, and
      facilitates displaying user-friendly messages when errors are
      encountered.
4.    PL/SQL allows declaration and use of variables in block of
      statements. These variables can be used to store intermediate
      result of a query for later processing, or calculate values and
      insert them into a oracle table later.
    Features of PL/SQL :-


1.   Block Structure :-
2.   Error Handling :-
3.   Variables and Types :-
4.   Conditionals :-
5.   Looping Constructs :-
6.   Cursors :-
7.   Procedures and Functions :-
8.   Packages :-
9.   Collections :-
              1.) Block Structure :-
•   The basic unit of PL/SQL is a block.
•   All PL/SQL programs are made up of blocks.
•   Blocks can be nested within each other.
•   Block has the following structure :-

    DECLARE
      Declaration Section – PL/SQL variables, Types, Cursors etc…
    BEGIN
      Executable statements –Procedural & SQL statements.

    EXCEPTION
      Exception Handling Section – Error handling statements.

    END;
 Only  the execution section is required.
 Declaration and Exception Sections are
  Optional.
 Execution section must also contain at
  least one executable statement.
 The different section of a block separate
  different functions of a PL/SQL programs.
2.) ERROR HANDLING :-

The  exception handling section of the
 block is used to response to run time
 errors encountered by your program.
By separating the error handling code
 from the main body of the program, the
 structure of the program itself is clear.
3.) VARIABLES & TYPES:-

 Information is transmitted between PL/SQL and database
  with Variables.
 Variable is a storage location that can be read from or
  assigned to by the programs.
 Variables are declared in declaration section of the BLOCK.

 Every has a specific type associated with it.

 The type defines what kind of information the variable can
  hold.
 PL/SQL variables can be of the same type as database
  column.
   Example :-

DECLARE
      S_Sid Varchar2(5) ;
      S_Name Varchar2(20) ;
      Curr_Bal Number(10) ;
BEGIN
      -- -- -- -- -- -- -- -- -- -- --
      -- -- -- -- -- -- -- -- -- -- --
EXCEPTION
      -- -- -- -- -- -- -- -- -- -- --
      -- -- -- -- -- -- -- -- -- -- --
END ;
4.) CONDITIONALS :-


A    PL/SQL program can conditionally
  execute the different portion of the code
  depending on the result of a test.
 A statement like this is known as “
  Conditional Statement “.
 The main construct for this is known as IF
  statement.
   Example :-

DECLARE
          Row_Count Number ;


BEGIN


          SELECT COUNT(*) INTO Row_Count FROM STUDENT ;
          IF Row_Count = 0 THEN
                          __ __ __ __ __ __ __
          ELSEIF Row_Count > 10 THEN
                          __ __ __ __ __ __ __
          END IF ;


EXCEPTION
          -- -- -- -- -- -- -- -- -- -- --
          -- -- -- -- -- -- -- -- -- -- --
END ;
    5.) LOOPING CONSTRUCTS :-
   A PL/SQL supports different kinds of LOOPS.
   A loop allows you to execute the same sequence of statements repeatedly.

   Example :-

DECLARE
          Loop_Count BINARY_INTEGER := 1 ;
BEGIN
          LOOP
                   INSERT INTO STUDENT (SID) VALUES (Loop_Count);
                   Loop_Count := Loop_Count + 1 ;
                   EXIT WHEN Loop_Count > 50 ;
          END LOOP ;
END ;
 6.) CURSORS:-
A  cursor is used to process multiple rows
 retrieved from the database (with a SELECT
 statement).
Using a cursor, your program can step
 through the set of rows return one at a
 time, processing each one.

 Example   :-

                                     Count …..
DECLARE
          Fname Varchar2 (20) ;
          Lname Varchar2 (20) ;
          /* Cursor Declaration */
          CURSOR C_Student IS
                   SELECT F_Name, L_Name FROM STUDENT ;
BEGIN


  OPEN C_Student ;
  LOOP
          FETCH C_Student INTO Fname, Lname ;
          EXIT WHEN C_Student %NOTFOUND ;
          /* Data Process Here */
  END LOOP
  CLOSE C_Student ;


END ;
7.) PROCEDURES & FUNCTIONS :-

Procedures  and Functions            (
 together known a subprograms ) are a
 special type of PL/SQL blocks that can
 be stored in database in compiled
 form, and then called from subsequent
 block.
o   Example :-

CREATE OR REPLACE PROCEDURE PrintStudent (
       p_major IN Student.major%TYPE ) AS
       /* Cursor Declaration */
       CURSOR C_Student IS
                SELECT F_Name, L_Name FROM Student
                WHERE major = p_major ;
BEGIN

    FOR V_Rec IN C_Student LOOP
    DBMS_OUTPUT.PUT_LINE ( V_Rec.F_Name || ‘ ‘ ||
                                  V_Rec.L_Name ) ;
    END LOOP ;

END ;
 8.) PACKAGES :-

 Subprograms    along with variables and types, can be
  grouped together into a packages.
 A package has two parts :

   1.   The Specification and
   2.   Body
 Together they allow related objects to be stored
  together in a database.
Example  that INSERT New Room and
 DELETE Room from ‘rooms’ Table.
o   Example :-

CREATE OR REPLACE PACKAGE RoomPkg AS

    PROCEDURE NewRoom ( p_Building rooms.building%TYPE,
                              p_RoomNo rooms.room_no%TYPE ,
                              p_NewSeat rooms.No_seat%TYPE ,
                              p_Descr rooms.descr%TYPE ) ;

     PROCEDURE DeleteRoom ( p_RoomId IN RoomsId%TYPE) ;

END RoomPkg ;

                                                               Count…….
CREATE OR REPLACE PACKAGE BODY RoomPkg AS

  PROCEDURE NewRoom ( p_Building rooms.building%TYPE,
                              p_RoomNo rooms.room_no%TYPE ,
                              p_NewSeat rooms.No_seat%TYPE ,
                              p_Descr rooms.descr%TYPE ) IS
     BEGIN
             /* INSERT Query */
     END NewRoom ;

  PROCEDURE DeleteRoom ( p_RoomId IN RoomsId%TYPE) ;
     BEGIN
              /* DELETE Query */
     END DeleteRoom ;

END RoomPkg ;
PL/SQL BLOCK
        Variable Declaration :-
• It can be done within the DECLARE section
  of the PL/SQL Block.
• Syntax :-
  DECLARE
       <V_Name> <Data Type> ;
• Example :-
  DECLARE
       Ename Varchar2(50);
       Salary Number;
       P_Counter Binary_Integer := 0;
       Temp Number Not Null := 0;
       Age Constant Number := 25;
                            Delimiters :-
+    Addition Operator               -    Subtraction Operator
*    Multiplication Operator         /    Division Operator
=    Equality Operator               >    Greater-than Operator
<    Less-than Operator              ;    Statement Terminator
%    Attribute Identifier            @    Database Link Indicator
<>   Not-Equal-to                    !=   Not-Equal-to
~=   Not-Equal-to                    ^=   Not-Equal-to
:=   Assignment Operator             ..   Range Operator
--   Single Line Comment             ||   String Connector
/*   Begin of Multi Line Comment     */   End of Multi Line Comment
<<   Begin of Label delimiter        >>   End Label Delimiter
‘    Character String Delimiter      “    Quoted string Delimiter
**   Exponentiation Operator
 Data Types in PL/SQL :-
          Numeric Family             Character Family
Binary_Integr              Char
Int                        Character
Integer                    NChar
Float                      NVarchar2
Double Precision           Varchar
Number                     Varchar2
Numeric                    String
Real
           Date Family               Boolean Family
Date                       Boolean
                    Numeric Family
      Declaration            Assignment           Stored Value
NUMBER                1234.5678           1234.5678
NUMBER(3)             123                 123
NUMBER(3)             1234                Error
NUMBER(4,3)           123.4567            Error
NUMBER(4,3)           1.234567            1.234
NUMBER(7,2)           12345.67            12345.67
NUMBER(3,-3)          1234                1000
NUMBER(3,-1)          1234                1230
         Looping Statements
• Simple Loop :-


Syntax :-

      LOOP

             <Sequence of Statements>

             EXIT WHEN <Condition>

      END LOOP;
• While Loop :-

• Syntax :-

     WHILE <Condition>

     LOOP

              <Sequence of statements>

     END LOOP;
• For Loop :-


• Syntax :-

  FOR <Variable> IN [Reverse] Start..End

  LOOP

      <Sequence of Statements>

  END LOOP;
• Examople :-

DECLARE
  pi CONSTANT NUMBER(4,2) := 3.14;
  radius NUMBER(5);
                                               RADIUS   AREA
  area NUMBER(14,2);
                                                 3      28.26
BEGIN
                                                 4      50.24
  radius := 3;
                                                 5       78.5
  WHILE radius <= 7
                                                 6      113.04
  LOOP
                                                 7      153.86
       area := pi * POWER(redius,2);
       INSERT NTO areas VALUES (radius, area);
       radius := radius + 1;
  END LOOP;

END;
• Examople :-

DECLARE
   given_num VARCHAR2(5) := ‘5639’;
   str_length NUMBER(2);
   inverted_num VARCHAR2(5);
BEGIN
   str_length := LENGTH(given_num);
   FOR cntr in reverse 1..str_length
   LOOP
         inverted_num := inverted_num ||
                                     SUBSTR(given_num, cntr, 1);
   END LOOP;
   dbms_output.put_line(‘Given Number is ‘ || given_num);
   dbms_output.put_line(‘Inverted Number is’ ||
                                     inverted_num);
END;
• Example :-
 DECLARE
       CONT VARCHAR(2) := ‘y’;
       I NUMBER(3) := 0;
 BEGIN
       WHILE CONT := ‘y’
       LOOP
              INSERT INTO STUDENT VALUES
                        (&SID,’&SNAME’,’&B_DATE’,&PHONE);
              DBMS_OUTPUT.PUT_LINE(‘Record is inserted’);
               DBMS_OUTPUT.PUT_LINE
                        (‘Do you want to Insert more records?’);
              CONT := ‘&CONT’;            I := I + 1;
       END LOOP ;
        DBMS_OUTPUT.PUT_LINE(‘Total || To_Char(i) || Records
                     are Inserted);
 END ;
• Conditional Statement :-
IF-THEN-ELSEIF-ELSE-END IF;
Syntax :-

      IF <Condition> THEN
            <Action>
      ELSEIF <Condition> THEN
            <Action>
      ELSE
            <Action>
      END IF;
CASE Statement :--

Syntax :-

  CASE <Variable>
      WHEN Value1 THEN Sequence of Statements1;
      WHEN Value2 THEN Sequence of Statements2;
      WHEN Value3 THEN Sequence of Statements3;
      WHEN Value4 THEN Sequence of Statements4;
     [ELSE Else_Sequence;]
 END CASE;
Labeled CASE Statement :--

Syntax :-
 <<MyCase>>
 CASE <Variable>
     WHEN Value1 THEN Sequence of Statements1;
     WHEN Value2 THEN Sequence of Statements2;
     WHEN Value3 THEN Sequence of Statements3;
     WHEN Value4 THEN Sequence of Statements4;
    [ELSE Else_Sequence;]
END CASE MyCase;
Searched CASE Statement :--

Syntax :-

CASE

  WHEN <Condition1> THEN Sequence of Statements1;
  WHEN <Condition2> THEN Sequence of Statements2;
  WHEN <Condition3> THEN Sequence of Statements3;
  WHEN <Condition4> THEN Sequence of Statements4;

  [ELSE Else_Sequence;]

END CASE ;
GOTOs and LABELs :--
Example :-

DECLARE
   V_Counter BINARY_INTEGER := 1;
BEGIN
   LOOP
    INSERT INTO temp_table VALUES
         (V_Counter, ’Loop Count’) ;
   V_Counter := V_Counter + 1;
                                                   Restrictions :-
       IF V_Counter >= 50 THEN



                                       Statement
                                        Jumping
         GOTO End_Of_Loop;                         1.) Inside Inner
                                                      Block
       END IF;
   END LOOP;                                       2.) Inside IF
   << End_Of_Loop >>                                  Condition
END;
             PL/SQL Records :-
• The scalar types (Number, Varchar2, Date and so on)
  are already predefined in package STANDARD.
  Therefore, in order to use one of these types in your
  program, you need only to declare a variable of the
  required type.
• Composite type, on the other hand, are user defined.
• In order to use a composite type, you must first define
  a type, and then declare a variable of that type.
• PL/SQL records are the similar to C Structure.
• A record provide a way to deal with a separate but
  related variables as a unit.
                      Syntax :-
TYPE <Type_Name> AS RECORD
(
   field1 type1 [Not Null] [:= expr1],
   field2 type2 [Not Null] [:= expr1],
   ……..
   fieldn typen [Not Null] [:= expr1],
);
 field1 is the name of the variable.
 type1 is the type of the field.
 Not Null is optional.
 := expr1 is the initial value for the field.
                              Example :-
DECLARE

        TYPE Rec_Type1 AS RECORD (
          Field1 NUMBER,
          Field2 VARCHAR2(5) );
        TYPE Rec_Type2 AS RECORD (
          Field1 NUMBER,
          Field2 VARCHAR2(5) );
        V_Rec1 Rec_Type1;
        V_Rec2 Rec_Type2;


BEGIN

  V_Rec21 := V_Rec2;
  V_Rec1.Field1 = V_Rec2.Field1;
  V_Rec1.Field2 = V_Rec2.Field2;

END;
               Using %Rowtype
• It is common in PL/SQL to declare a record with the same
  type as a database row. PL/SQL provide the %ROWTYPE
  operator to facilitate this.
• %ROWTYPE will return a type based on the table
  definition.
• If the table definition changes, %ROWTYPE changes along
  with the table definition.
• Any NOT NULL define with the column is not include.
• Example :-
  DECLARE
        V_Fname Student.Fname%ROWTYPE;
        V_Lname Student.Lname%ROWTYPE;
  BEGIN
PL/SQL CURSOR
              What is Cursor?

• In order to process a SQL statements, Oracle will
  allocate an area of memory known as the context
  area.
• The context area contain the information necessary
  to complete the processing, including the number
  of rows processed by the statement.
• A CURSOR is a handle, or pointer, to the context
  area.
• Through the cursor a PL/SQL can control the
  context area.
         Processing Explicit Cursor :-

1. Declare the Cursor.
2. Open the Cursor for Query.
3. Fetch the results in PL/SQL variables.
4. Close the Cursor.
        1.Declaring a Cursor :-
• Declaring a cursor define the name of the cursor and
  associates it with a SELECT statement.
• SYNTAX :-
  CURSOR Cursor_Name IS Select_Statement;
• Where the Cursor_Name is the Name of the cursor
  and,
• Select_Statement is the query to b processed by this
  cursor.
• Cursor name is the PL/SQL identifier so it must be
  declare before it is referenced.
• Any SELECT statements are legal, Including JOIN,
  UNION and MINUS clause.
             EXAMPLE :-
DECLARE

 V_Dept Classes.Department%ROWTYPE;
 V_Course Classes.Course%ROWTYPE;

 CURSOR C_Classes IS
    SELECT * FROM Classea
    WHERE department = V_Dept AND
             Course = V_Course;

BEGIN
 The    cursor declaration can referenced PL/SQL
  variable as well. Therefore variables are considered
  bind variables, so these variables must be visible at
  the point of the cursor declaration.
 To ensure that all variables referenced in cursor
  declaration are declared before the reference, you
  can declare al the cursors at the end of the
  declaration section.
 In our example the declaration of variables (V_Dept
  and V_course) after the declaration of cursor is
  illegal, because they must be declare before they
  referenced.
        2.Opeaning a Cursor :-
• SYNTAX :-
  OPEN Cursor_Name ;

• Where the Cursor_Name identifies a cursor that
  has previously been declared .
• When a cursor is opened, the following things
  happen:
  – The values of the bind variable are examined.
  – Based on the values of the bind variables and the
    content of the table(s) referenced in the query, the
    active set is determined.
  – The active set pointer is set to the first row.
                       EXAMPLE :-
DECLARE
   V_RoomID Classes.RoomID%ROWTYPE;
   V_Building Classes.Building%ROWTYPE;
   V_Department Classes.Department%ROWTYPE;
   V_Courses Classes.Courses%ROWTYPE;
   CURSOR C_Building IS
   SELECT Building FROM Room, Classes
   WHERE Room.RoomID = Classes.RoomID AND
   Department = V_Department AND Courses = V_Courses ;
BEGIN
   -- Assign the bind variables before the cursor is opened.
   V_Department := ‘HIS’ ;
   V_Courses := 101;
   OPEN C_Building ;
END ;
     3.Fetching from a Cursor :-
• SYNTAX :-
  FETCH Cursor_Name INTO List_of_Variables ;
                 and
  FETCH Cursor_Name INTO PL/SQL_Records ;

• Where the Cursor_Name identifies the previously
  declared and opened cursor.
• List_of_Variables is the comma separated list of the
  previously declared PL/SQL variables.
• PL/SQL_Records is the previously declared PL/SQL
  record.
 After each FETCH the active set pointer is increased
  to the next row.
 Thus, each FETCH will return successive rows in the
  active set, until the entire set is returned.
 The %NOTFOUND attribute, describe the “Cursor
  Attributes” selection, is used to determine when the
  entire active set has been received.
 The last FETCH will not assign the value to the output
  variables.
                     EXAMPLE :-
DECLARE
   V_Dept Classes.Department%ROWTYPE;
   V_Course Classes.Course%ROWTYPE;
   CURSOR C_Classes IS
         SELECT * FROM Classea
         WHERE department = V_Dept AND Course = V_Course;
BEGIN
   -- Assign the bind variables before the cursor is opened.
    V_Dept := ‘HIS’ ;
    V_Course := 101;
   OPEN C_Classes ;
   FETCH C_Classes INTO V_Dept, V_Course ;
   FETCH C_Classes INTO V_Dept, V_Course ;
   FETCH C_Classes INTO V_Dept, V_Course ;
END ;
           4.Closing a Cursor :-
• When all of the active sets has been received, the
  cursor should be closed.
• This tell PL/SQL that the program is finished with the
  cursor, and the resources associated with it can be free
• These resources include the storage used to hold the
  active set, as well as any temporary space used for
  determining the active set.
• SYNTAX :-
  CLOSE Cursor_Name ;
• Where the Cursor_Name identifies the previously
  declared and opened cursor.
• Once cursor is closed, it is illegal to fetch from it.
          Parameterize Cursor :-
• There is an additional way of using bind variable in a
  cursor.
• A parameterized cursor take arguments like procedure.
• Consider the following example….

DECLARE
  V_Dept Classes.Department%ROWTYPE;
  V_Course Classes.Course%ROWTYPE;
  CURSOR C_Classes IS
     SELECT * FROM Classea
     WHERE department = V_Dept
     AND Course = V_Course;
 Previous example can also be written using parameterized cursor like……
DECLARE
        CURSOR C_Classes(P_Dept Classes.Department%ROWTYPE;
                                P_Course Classes.Course%ROWTYPE; )
        IS
        SELECT * FROM Classes
        WHERE department = P_Dept AND Course = P_Course;
BEGIN
        OPEN C_Classes(‘HIS’, 101);

        FETCH C_Classes INTO V_Dept, V_Course ;
        FETCH C_Classes INTO V_Dept, V_Course ;
        FETCH C_Classes INTO V_Dept, V_Course ;

        CLOSE C_Classes;
END ;
   Cursor Fetch Using Simple Loop:-
DECLARE
   V_Fname Student.FName%ROWTYPE;
   V_Lname Stusdent.LName%ROWTYPE;
   CURSOR C_Student IS
   SELECT FName, LName FROM Student;
BEGIN
   OPEN C_Student;
   LOOP
        FETCH C_Student INTO V_Fname, V_Lname ;
        EXIT WHEN C_Student%NOTFOUND;
        UPDATE Student SET (FName = UPPER(V_Fname),
                                  LName = UPPER(V_Lname) )
        WHERE FName = V_Fname AND LName = V_Lname ;
   END LOOP;
   SELECT * FROM Student;
END ;
    Cursor Fetch Using While Loop:-
DECLARE
   V_Fname Student.FName%ROWTYPE;
   V_Lname Stusdent.LName%ROWTYPE;
   CURSOR C_Student IS
   SELECT FName, LName FROM Student;
BEGIN
   OPEN C_Student;
   WHILE C_Student%FOUND LOOP
        FETCH C_Student INTO V_Fname, V_Lname ;
        UPDATE Student SET (FName = UPPER(V_Fname),
                                LName = UPPER(V_Lname) )
        WHERE FName = V_Fname AND LName = V_Lname ;
   END LOOP;
   SELECT * FROM Student;
END ;
       Cursor Fetch Using For Loop:-
DECLARE
   CURSOR C_Student IS
   SELECT FName, LName FROM Student;
   V_Studentdata C_Student%ROWTYPE;
BEGIN
   OPEN C_Student;
   FOR V_Studentdata IN C_Student LOOP
        FETCH C_Student INTO V_Studentdata ;
        UPDATE Student SET
                (FName = UPPER(V_Studentdata.FName),
                 LName = UPPER(V_Studentdata.LName) )
        WHERE FName = V_Studentdata.FName AND
                  LName = V_Studentdata.LName ;
   END LOOP;
   SELECT * FROM Student;
END ;
ERROR HANDLING
            What is Exception?
• One of the feature of PL/SQL is Exception mechanism.
• By using an Exception and Exception Handler you can
  make your PL/SQL programs to be able to deal with both
  unexpected and expected during the execution of
  program
• PL/SQL Exceptions are similar to JAVA Exceptions.
• Exceptions are designed for run-time error handling
  rather then compile-time error handling.
• The errors that occur during the compilation phase are
  detected by PL/SQL engine and reported to the users.
• The program can not handle this because the program
  still not run yet.
   What kinds of errors can be occur in PL/SQL program?

     Error Type       Reported By              How to Handled
Compile-time      PL/SQL Compiler    Interactively-compiler reports error,
                                     and you have to correct them
Run-time          PL/SQL Runtime     Programmatically-exception are
                  engine             raised and caught by exception
                                     handlers


 Exceptions and exception handlers are the method by which the
  program reacts and deal with runtime errors.
 Run-time include SQL errors like :

1. ORA-1 : unique constraint violated
2. ORA-06502 : PL/SQL : numeric or value error etc…
 When an error occurs, an exception is RAISED. When this
  happens, control is passed to the exception handler, which is s
  separate section of the program.
 This separates the error handling from the rest of the program,
  which makes the logic of the program easier to understand.
 This also ensure that all errors will be trapped.

 No matter which statement fail, the program will detect and
  handle the error.
 The program execution will not continue from the statement that
  raised the error.
 Instead, execution will continue to the exception handler and
  then to any outer block.
              Declaring Exception :-

• Exceptions are declare in the DECLARE section
  of the block, RAISED in the BEGIN section, and
  handled in the EXCEPTION section.
• There are two types of exceptions :
   – Used-Defined.
   – Predefined.
1. User-Defined Exception :-
 A User-Defined Exception is an error that is defined by
   the programmer.
 The error that is signifies is not necessarily an Oracle
   Error, it could be an error with the data.
 User-Defined Exception is declare in DECLARE section
   just like a variable.
 Syntax :-
   <EName> EXCEPTION ;
 Ename is an identifier that will be until the end of this
   block.
 Scope of an exception is the same as the scope of the
   other variables and cursor in the same DECLARE
   section.
2. Predefined Exception :-

 Oracle has predefined several exceptions that
  correspond to the most common Oracle errors.
 Like the predefined data types, the identifiers for this
  exceptions are defined in the STANDARD packages.
 Because of this they are already available to the
  program, no need to declare them in the DECLARE
  section like an User-Defined Exception.

 To see the Oracle Exception :
  Refer the page No.: 265, 266, 267
  Oracle9i PL/SQL Programming
            - By :- SCOTT URMAN.
              Handling Exception :-
• Syntax :-

  EXCEPTION
     WHEN EName1 THEN
           Sequence of Statements 1;
     WHEN EName2 THEN
           Sequence of Statements 2;
     [ WHEN OTHERS THEN
           Sequence of Statements 3; ]
  END;
 Each Exception Handler consist of the WHEN clause and
   statements to execute when the exception is RAISED.
 Example :-
DECLARE
e_exp1 EXCEPTION;
e_exp2 EXCEPTION;
BEGIN
RAISE e_exp2;
EXCEPTION
WHEN e_exp1 THEN
 DBMS_OUTPUT.PUT_LINE('Handler 1 Executed.!');
WHEN e_exp2 THEN
 DBMS_OUTPUT.PUT_LINE('Handler 2 Executed.!');
END;
DECLARE
V_WID WORKER.WORKERID%TYPE;
E_EXP EXCEPTION;
CURSOR C_WORKER IS SELECT WORKERID FROM WORKER;
BEGIN
OPEN C_WORKER;
LOOP
 FETCH C_WORKER INTO V_WID;
 IF C_WORKER%NOTFOUND THEN
  RAISE E_EXP;
 END IF;
 DBMS_OUTPUT.PUT_LINE(V_WID);
END LOOP;
CLOSE C_WORKER;
EXCEPTION
WHEN E_EXP THEN
 DBMS_OUTPUT.PUT_LINE('RECORD NOT FOUND');
END;
  The OTHERS Exception Handler :-
• PL/SQL define a special exception handler, known
  WHEN OTHERS.
• This handler will execute for all raised exceptions
  that are not handle by any other WHEN clauses
  defined in the EXCEPTION section.
• It should always be the last handler in the block, so
  that all previous handler will be scanned first.
• WHEN OTHERS will trap all the exception whether
  they are User-Defined or Predefined.
• It is good programming practice to have OTHERS
  handler to ensure that no errors go undetected.
 Example :-

DECLARE
e_exp1 EXCEPTION;
e_exp2 EXCEPTION; e_exp3 EXCEPTION;
BEGIN
RAISE e_exp3;
EXCEPTION
WHEN e_exp1 THEN
 DBMS_OUTPUT.PUT_LINE('Handler 1 Executed.!');
WHEN e_exp2 THEN
 DBMS_OUTPUT.PUT_LINE('Handler 2 Executed.!');
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(‘OTHERS Executed.!');
END;
       SQLCODE and SQLERRM :-
• Inside an OTHERS handler, it is often useful to know
  which Oracle error raised the exception, whether or
  not the error has a predefined exception for it.
• PL/SQL provide this information via two built-in
  functions :
  – SQLCODE
  – SQLERRM
• SQLCODE return the current error code.
• SQLERRM return the current error message text
• For User-Defined exception SQLCODE return 1 and
  SQLERRM will return “User-Defined Exception”
 Example (Without Exception):-
BEGIN
INSERT INTO WORKER(WORKERID) VALUES ('W101');
END;

• Example (With Exception):-
BEGIN
 INSERT INTO WORKER(WORKERID) VALUES ('W101');
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' ||
   SQLERRM);
END;
CREATING PROCEDURES,
FUNCTIONS & PACKAGES
      Procedures & Functions :-
• PL/SQL procedures and functions behave
  very much like other 3GLs.
• They share many of the same properties.
• Collectively procedures and functions are
  called subprograms.
• Procedure is created first with the CREATE
  OR REPLACE PROCEDURE statement.
• When procedure is created, it first compiled,
  and then stored in the database in compiled
  form.
• This compiled code can then be run later
  from another PL/SQL block.
 When    the procedure is called, parameters can be
  passed.
 A procedure call is the PL/SQL statement by itself.
  It is not called a part of an expression.
 When a procedure is called the control is passed to
  the first executable statement inside the
  procedure.
 When the procedure finish control resume to the
  statement following the procedure call statement.
 A procedure is a PL/SQL block with a declarative
  section, an executable section and an exception
  handling section.
 SYNTAX :-

CREATE [OR REPLACE] PROCEDURE pro_name

[ (argument1 [{IN|OUT|IN OUT}] type ,

 argument2 [{IN|OUT|IN OUT}] type ,
 …..
 argumentN [{IN|OUT|IN OUT}] type ) ] {IS|AS}

Procedure_body
 Where   pro_name is the name of the procedure to be
  created.
 argument is the name of the procedure parameter.

 type is the data type of the associated parameter.

 Procedure_body is a PL/SQL block that makes up the
  code of the procedure.
 In order to change the code of the procedure the
  procedure must be drop and recreated.
 Because this is the common operation while the
  procedure is under development, the OR REPLACE
  keyword allow this in one operation.
 If the procedure exist it is drop first, without a
  warning message.
 If the procedure does not exist it simply created.
 If the procedure is exist and OR REPLACE
  keyword is not present the create statement will
  return the Oracle error - ”ORA-955: Name is
  already use by an existing object.”
 Either the IS or the AS keyword can be used-both
  are equivalent.
 The Procedure Body :-
  The body of a procedure is a PL/SQL block with
    declarative, executable and exception sections.
  The declarative section is located between IS or AS
    keyword and BEGIN keyword.
  The executable section is located between BEGIN
    and EXCEPTION keyword, or BEGIN and END
    keyword if there is no EXCEPTION HANDLING
    section.
  The EXCEPTION section, if present, is located
    between the EXCEPTION and END keyword.
 Structure of a procedure creation statement :-

CREATE OR REPLACE PROCEDURE pro_name
[parameter list] AS
  /*Declarative section is here*/

BEGIN
  /*Executable section is here*/

EXCEPTION
  /*Exception section is here*/

END pro_name;
           Creating Function :-
• A function is very similar to the procedure.
• Both take parameters, which can be of any
  mode.
• Bothe are different from of PL/SQL blocks, with
  a declarative, executive and exception section.
• Both can be stored in database or declare
  within a block.
• However a procedure call is a PL/SQL statement
  by it self, while a function call is called as a part
  of an expression.
    SYNTAX :-

CREATE [OR REPLACE] FUNCTION fun_name

[ (argument1 [{IN|OUT|IN OUT}] type ,

    argument2 [{IN|OUT|IN OUT}] type ,
    …..
    argumentN [{IN|OUT|IN OUT}] type ) ]

RETURN return_type {IS|AS}

Function_body
 Where fun_name is the name of the function to be created.
 argument and type are the same as for procedure.

 Return_type is the data type of the value that the function
  return.
 The Function_body is the PL/SQL block containing the code for
  the function.
 The same rule apply for the function body as for a procedure
  body.
 Similar to a procedure argument list is optional.

 Return type of the function is required because the function call
  is part of an expression.
 The type of function is used to determine the type of the
  expression containing the function call.
 The return statement inside the function body is used to return
  control to the calling environment with a value.
 The general syntax of the RETURN statement is :

  RETURN expression;
 Where expression is the value to be return.

 When return is executed, expression will be converted to the
  type specified in the RETURN clause of the function definition, if
  is not already of that type.
 At this point control immediately return to the calling
  environment.
 There can be more then one RETURN statements in a function,
  although only one of them will be executed.
 It is an error for a function to end without executing a RETURN
  statement.
  EXAMPLE :-
CREATE OR REPLACE FUNCTION ClassInfo
(        p_dept CLASS.depertment%TYPE,
         p_course CLASS.course%TYPE             )
RETURN VARCHAR2 IS
   v_currentstud NUMBER;
   v_maxstud     NUMBER;
  v_percent     NUMBER;
BEGIN
   SELECT curr_stu, max_stud INTO v_currentstud, v_maxstud FROM
   classes WHERE department = p_dept AND course = p_course;

    v_percent := v_currentstud / v_maxstud *100;
                                                       Count….
  IF v_percent = 100 THEN
       RTURN ‘FULL’;
  ELSIF v_percent > 80 THEN
       RTURN ‘Some Room’;
  ELSIF v_percent > 60 THEN
       RTURN ‘More Room’;
  ELSIF v_percent > 0 THEN
       RTURN ‘Lots of Room’;
  ELSE
       RETURN ‘EMPTY’;
  END IF;

END ClassInfo;
   EXAMPLE :-

CREATE OR REPLACE FUNCTION RESULT
(p_sid student.SID%type, p_fname student.FNAME%type,
 p_lname student.LNAME%type, p_m1 marks.M1%type,
 p_m2 marks.M2%type, p_m3 marks.M3%type,
 p_m4 marks.M4%type, p_m5 marks.M5%type,
 p_m5 marks.M5%type )

RETURN VARCHAR2 IS
v_grade VARCHAR2(10);
v_total number(3);
v_per number(5,2);
BEGIN

 IF p_m1 < 40 THEN v_grade := ‘FAIL’;
     ELSIF p_m2 < 40 THEN v_grade := ‘FAIL’;
     ELSIF p_m3 < 40 THEN v_grade := ‘FAIL’;
     ELSIF p_m4 < 40 THEN v_grade := ‘FAIL’;
     ELSIF p_m5 < 40 THEN v_grade := ‘FAIL’;
     ELSIF p_m6 < 40 THEN v_grade := ‘FAIL’;
     ELSE v_grade := ‘PASS’;
 END IF;
 IF v_grade := ‘PASS’ THEN
      v_total := p_m1 + p_m2 + p_m3 + p_m4 + p_m5 + p_m6;
      v_per := v_total / 6;
      IF v_per >= 70 THEN v_grade := ‘DIST’;
        ELSIF v_per >= 60 THEN v_grade := ‘FIRST’;
        ELSIF v_per >= 50 THEN v_grade := ‘SECOND’;
        ELSE v_grade := ‘PASS’;
      END IF;
 END IF;

 RETURN v_grade;

END RESULT;
    Actual & Formal Parameters :-
1. Actual Parameters :-

  The variable declare in the BLOCK are passed as
  a argument in this context they are known as a
  Actual Parameters.
  The actual parameters that contain the values
  passed to the procedure when it is called, and
  they received result from the procedure when
  it return.
2. Formal Parameters :-

  The parameters in the declaration section are known
  as a Formal Parameters. The formal parameters are
  the place holder for the value of the actual parameter.
  When the actual parameters are called, the formal
  parameters are assigned the value of the actual
  parameters.
  When the procedure returns the actual parameters
  are assigned the values of the formal parameters.
  Formal parameters can have three modes – IN, OUT,
  or IN OUT.
  If the mode is not specified for the formal parameters,
  it defaults IN.
               IN mode :-
• The value of the actual parameter is passed in to
  the procedure when the procedure is invoked.
  Inside the procedure the formal parameter acts
  like a PL/SQL constant-it consider read only and
  can not be changed.

• When the procedure finishes and control return
  the calling environment, the actual parameter is
  not changed.
              OUT mode :-
• Any value the actual parameter has when the
  procedure is called is ignored. Inside the procedure
  the formal parameters acts like an uninitialized
  PL/QSL variables, and thus has a value of NULL.
• It can be read from and written to.
• When the procedure finishes and control return to
  the calling environment, the content of the formal
  parameter are assigned to the actual parameter
            IN OUT mode :-
• This mode is combination of IN and OUT.
• The value of the actual parameter is passed into the
  procedure when the procedure is invoked.
• Inside the procedure, the formal parameter act like
  an initialized variable, and can be read from and
  written to.
• When the procedure finishes and the control return
  to the calling environment, the content of the formal
  parameter are assigned to the actual parameter.
PACKAGES
                  Introduction :-
• A Package is a PL/SQL construct that allows related
  object to be stored together.
• A package has two separate parts :
   – The specification.
   – Package Body.
• Each of them is stored separately in the data dictionary.
• Unlike procedure and function, which can be contained
  locally in a block or stored in a database, a package can
  only be stored it can not be local.
• It allowing related object to be group together, packages
  are useful because they are less restrictive then stored
  programs with respect to dependency.
• A Package is essentially a named declarative
  section.
• Anything that can go in declarative section of
  the block can also go in a package.
• That include the procedures, functions,
  cursors, types, and variables.
• One advantage of putting these objects into a
  packages is the ability to reference them from
  other PL/SQL block, so packages also provide a
  global variable for PL/SQL.
       1. Package Specification :-
• The package specification is also known as package
  header, contain information about the content of the
  package.
• However, it does not contain the code for any
  subprograms.
• SYNTAX :-

CREATE [OR REPLACE] PACKAGE pkg_name {IS | AS}
Type definition | Procedure specification |
Function specification | Variable declaration |
Exception declaration | Cursor declaration
END pkg_name ;
 Package  element can appear in any order.
 However, as in declarative section, an object must
  be declare before it is referenced.
 If a cursor contain a variable as a part of the
  WHERE clause then it must be declare before the
  cursor declaration.
 Declaration for procedures and functions must be
  forward declaration.
 A forward     declaration simply describe the
  subprogram and its arguments, but does not
  include the code.
              2. Package Body :-
• The package body is a separate data dictionary object
  from the package header.
• It can not be successfully compiled unless the package
  header has already been successfully compiled.
• The body contain the code for forward subprogram
  declarations in the package header.
• It can also contain additional declaration that are global
  to the package body, but are not visible in the package
  specification.
• Package body is optional. If the package header does not
  contain any procedure or function the body does not
  have to be present.
• This technique is valuable for global declaration of
  variables and types.
 EXAMPLE :- (Package Specification)
CREATE OR REPLACE PACKAGE ClassPkg AS

    PROCEDURE AddStudent (
         p_sid STUDENT.sid%TYPE,
         p_dept STUDENT.department%TYPE,
         p_course STUDENT.course%TYPE    );
    PROCEDURE RemStudent (
         p_sid STUDENT.sid%TYPE,
         p_dept STUDENT.department%TYPE,
         p_course STUDENT.course%TYPE    );
    e_studNotReg EXCEPTION;

END ClassPkg;
DATABASE TRIGGER
                 Introduction :-
• Like packages, triggers must be stored as stand-alone
  object in the database and can not be local to a block or to
  a package.
• As we seen procedure is executed implicitly from another
  block via procedure call, which can also pass arguments.
• On other hand, a trigger is also executed explicitly
  whenever the triggering event happens, and a trigger does
  not accept arguments.
• The act of executing a trigger is known as firing trigger.
• The trigger event can be a DML (INSERT, UPDATE or
  DELETE) operations on database table or views.
 Triggers   can e used for many things, including the followings
  :

1. Maintaining the complex integrity constraints not possible
   through declarative constraints enable at table creation.
2. Auditing information in a table by recording the changes
   made and who made them.
3. Automatically signaling other programs that action need
   to take place when changes are made to a table.
4. Publishing information about various events in a publish-
   subscribe environment.
 There arethree main kinds of triggers :-
1. DML triggers.
2. Instead-of triggers.
3. System triggers.

1. DML Triggers :-

• A DML triggers are fired by DML statement, and type of
  statements determine the types of triggers.
• DML triggers can be defined for INSERT, UPDATE or DELETE
  operations.
• They can be fired before or after the operation, and they
  can also fired on row or statement operation.
2. Instead-of Triggers :-
• Instead-of triggers can be defined on views only.
• Unlike a DML trigger, which execute in addition to the DML
   operation, an instead-of trigger will execute instead of the
   DML statement that fired it.
• Instead-of trigger must be row level.
3. System Triggers :-
• A system trigger fires when a system event, such as
   database startup or shutdown, occurs, rather then on a
   DML operation on a table.
• A system trigger can also be fired on DML operation, such
   as table creation. For example, we want to record
   whenever a data dictionary object is created.
              Creating Trigger :-
CREATE [OR REPLACE] TRIGGER <trigger name>
[BEFORE|AFTER|INSTEAD OF] <triggering event>
[WHEN trigger condition]
[FOR EACH ROW]
trigger body;

Where trigger name is the name of the trigger.
triggering event specifies the event that fires the trigger.
And trigger body is the main code for trigger.
The trigger condition in the WHERE clause, if present, is
evaluated first and the body of the trigger is executed only
when this condition is true.
Thank you...

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:22
posted:8/16/2012
language:
pages:251
Description: GTU MCA PPT