Docstoc

Oracle Practical file

Document Sample
Oracle Practical file Powered By Docstoc
					              INTRODUCTION TO THE ORACLE


     Oracle consists of a comprehensive set of application building &
     end-user products, aimed at providing complete information
     technology solutions .Oracle applications are portable across wide
     range of plateforms & operating systems , from personal computer
     to large parellel processors.
     Oracle provides a flexible Database Management System (DBMS)
     - the Oracle Server - for storage & management of information
     used by applications .


     ORACLE SERVER

     The Oracle server is used to store & manage the information by
     applications. The latest oracle server is Oracle 9i manages a
     dbase with all the advantages of relational structure , plus ability to
     store & execute dbase objects such as procedures & triggers.
     It has following features:-
1.   Storage of data in designated dbase areas.
2.   Retrieval of data for applications , using appropriate optimization
     techniques.
3.   Database security, & the tasks perfmitted for specific users.
4.   Consistency & protection of data.
5.   Communication & integration of data where dbases are disributed
     across a network..

     Oracle applications may run on the same computer as the oracle
     server. Applications and tools supporting them may be run on
     another system.In the client -server computing environment , a
     wide range of computing resource may be used .


     Oracle server options :-
1. procedural option:- It provides a PL/SQL version 2 engine with
in the Oracle server , which includes the ability to store dbase
procedures , functions & triggers for use by application.

2. Distributed option :- It supports transactions which update data
across more than one dbase in a distributed dbase network ,
employing two-phase commit .

3. parallel option :- It supports loosely-coupled systems.

4. Trusted Oracle :- Provides a set of additional high security
features .



                   ORACLE PRODUCTS


1 Oracle text retrieval :- A technlogy that adds fully functional text
retrieval capabilities to an oracle dbase .

2 Pro * oracle :- A series of precompliers that allows oracle dbase
access with in C , Cobol , PL/1 , Pascal , & Ada Programming
Languages .

3 Oracle Card :- An end-user interface for producing easy-to-use
dbase applications incorporating graphics & multimedia features.

4 Oracle Case :- A family of tools to aid the analysis , design &
generation of oracle applications.

5 SQL * Plus :- A tool that allows direct & interactive use of the
SQL language to access the oracle server , using ad-hoc commands or
by runnig command files.
               INTRODUCTION TO SQL * PLUS


 A database management system requires a query language to enable
 users to access data . Structured Query Language(SQL)- pronounced
 ‘Sequel’ is the language was used by the most relational database
 systems.
 The SQL language was developed in a prototype relational database
 management system – system R-by IBM in the mid 1970 ‘ s . In 1979
 , oracle Corp. introduced the first commercially available
 implementation of SQL .
 Features of SQL are described below :-

I. Sql is an english like language .It uses words such as select , update ,
  insert , delete as part of its command set .

II. Sql is the non-procedural language . In other words Sql does not
  requires to specify the method to the data .

III. Sql process sets of records rather than a single record at a time . The
  most common form of records is table .

IV.Sql can be used by a range of users including DBA ‘s , application –
 programmers , management personal .
 QUERYING DATABASE TABLES


I. the most commonly uesd command ; it is used to retrieve the data
  from the database table.
  By using simple Select command : - This is
  Syntax :- select columnname from tablename ;

 For e . g :- select ename , empno , sal , hiredate from emp
 Where ename like ‘s%’;

II. By using condtional retrieval :- The ‘ where ‘ clause is used for
     this purpose.It corresponds to the restriction operator of relational
     algebra.
     It contains the condition that rows must meet in order to be displayed
     .The ‘where ’ clause must follow the from clause .

     Syntax :- select       columns
     From        table
     Where       certain conditions are met .

     For e . g :- If we want to retrieve the information of those employees
     whose department name is ‘ Salesman ‘, then :-

     S :\> select ename , empno , deptno , hiredate
     From emp
     Where job = ‘SALESMAN ‘ ;


     OPERATORS IN ‘ WHERE ‘ CLAUSE


    BETWEEN Clause :- This operator is used to tests value between ,
     & inclusive of , low & high range . for e . g :– If we want to see those
     employee s whose salaries are ranging between 1000 & 2000

     S :\> Select ename , deptno , empno , sal , from emp
     Where sal between 1000 and 2000 ;

 IN Clause :- It test values in a specified list . If the characters & dates
   are used in the list they must be enclosed in a single quotations (‘ ‘) .
   To find out all employees who have one of three mgr numbers .

     S:\> select em[pno , ename , sal , mgr , from emp
     Where mgr IN (7902 , 7566 , 7788) ;


 IS NULL operator :- The IS NULL operator specificallly tests for
     values that are NULL . So to find out all employees who have no
     managers , then .

     S:\> select ename , mgr
     From emp
     Where mgr is NULL ;
     For negating we use :-

   NOT BETWEEN
   IN
   NOT IN
   . LIKE :- Like operator is used to select the rows that match a
     character pattern .The character pattern matching operation may be
     referred to us a ‘ wild – card ‘ search . Two symbols can be used to
     construct the search string .

     Symbol                 Represents
     %                   any sequence of zero or more characters .
     -                    any single character.

     If we want to list the names of those employees whose name starts
     from letter ‘S ’

     S:\> select ename from emp
     Where ename like ‘S%’;

     III By using null values :- A null value is a value which is either
     unavailable ,
     Unassigned , unknown or inapplicable.A null value is not the same as
     zero.
     Zero is a number . Null values taken up by one byte of internal
     storage.
     Null values are handelled correctly by SQL . In order to
     Achieve a result for all employees , it is necessary to convert
     The null values to a number . We uese the ‘ NVL ‘ function to
     Convert the null value to a non – value. For e.g : –
   S:\> select ename , sal*12 +NVL(comm ,0)
   Annual _sal from emp ;

   IV By using DISTINCT Clause : - The ‘ Distinct ‘ Clause is used to eliniminate
   the
   The duplicate values in the result . Multiple columns may be specified
   after
   The ‘ DISTINCT ’ clause & the DISTINCT affects all selected
   columns .
   For e .g :- To display the distinct values of deptno & job enter :
   S:\> select DISTINCT deptno , job from emp ;

   This display a list of all the different combinations
   Of jobs & department numbers .
   V By using ORDER BY clause : - Normally , the order of rows
   returns in a query .
   May be used to sort the rows . ORDER BY must always be the last
   clause in
   The select statement . The default sort order is in ascending order :
 Numeric values lowest first .
 Date values earliest first .
 Character values alphabatically .
   To reverse this order , the command word DESC is specified after the
   column name in the ‘ ORDER BY ‘ clause . For e . g –

   S:\> select ename , job , hiredate
   From emp
   ORDER BY hiredate desc ;



   VI By using GROUP BY Clause : - The GROUP BY clause of
   the select statement is
   Used to divides rows into smaller groups . Group functions can be
   used to return
Information for each group . Rows may be pre – excluded with a ‘
WHERE ’
Clause ,before dividing them into group . For e . g : –

S:\> select job , avg (sal)
From emp
Group by job ;

VII Having Clause :- The ‘ Having ‘ clause is used only with the
expressions and / or
Columns that are specified with the ‘ GROUP BY ’ clause . For e .g –

S:\> select job , sum(sal) , avg (sal) , max (sal) , min (sal) from emp
Where deptno =20
Group by job
Having avg (sal)>1000 ;

VIII By using Roll up operation : - Roll up grouping produces a result set
containing the
The regular grouped rows & the subtotal values .
The Roll up operator delivers aggregate & subaggregates for
Expressions within the group by statements . The roll up operator
Can be used by the report writers to extract statistics & summary
Information from results sets. The Roll Up operator creates
Groupings moved from right to left , along the list of columns
Specified in the GROUP BY clause .

Syntax :-
Select [column ,….] group_function
(column) from table
[where condition]
group by [ROLL UP] [group_expression]
For e .g :-
S:\> select dept_id ,job_id,sum (sal)
From employees
Where dept_id<60
Group by ROLL UP(dept_id , job_id) ;
   IX Cube operation : - The cube operator is an additional switch in
   group by in select statement.The Cube operator can be applied to all
   aggregate functions including Avg , max , min , sum ,Count .
   It is used to produce results set that are typically used for across –
   tabular reports .The cube operator is used with an aggregate function
   to generate additional rows in a result set .

   Syntax :- select [column,…] group_function (column)
   From table
   [where condition]
   group by [CUBE] group_by_expression ;

   for e .g : -
   S:\> select dept_id , job_id , sum (sal)
   From emp
   Where dept_id < 60
   Group by CUBE           (dept_id , job_id) ;




   ORACLE DATATYPES

   The information in a data base is maintained in the form of tables .
   Each table consists of rows & columns to store data . A particular
   column in a table must contain similar data , which is of a particular
   type .
   The different data types available are :-

1. CHAR :- To store character type data.

2. VARCHAR2 :- Similar to char but can store variable number of
   characters .

3. NUMBER :- Stores fixed & floating point numbers .
4. DATE :- stores point- in – time values in a table .

5. RAW :- Used to store binary data such as graphics , sound , etc .

6. LONG :- This type of data is used to store upto 2GB of characters .

7. LONGRAW :- Contains raw binary data otherwise the same as a long column.


                       Data type              Descripti         Column
                                              on                Length
                                                                (in bytes
                                                                )
                   1. CHAR (n)                Fixed             Fixed
                                              length            For
                                              character         every
                                              data of           row in
                                              length (n).       the table
                                                                . Max.
                                                                size 255
                                                                bytes per
                                                                column /
                                                                row .
                   2. VARCHAR2                Varirable         Variable
                      (n)                     length            for each
                                              character         row ,
                                              data .            upto
                                                                2000
                                                                bytes per
                                                                column /
                                                                row .
                   3. NUMBER (                Variable          Variable
                      P , S)                  length            for each
                                              numeric           row .
                                              data .            The Max
                                        Maximum          . space
                                        pre -            required
                                        sicion ( P)      for a
                                        is 38 .          given
                                                         column
                                                         is 21
                                                         bytes .
                 4. DATE                Fixed            Fixed at
                                        length           7 bytes
                                        date &           for each
                                        time data        row in a
                                        ranging          table .
                                        from (Jan
                                        1 , 4712
                                        B.C ) to
                                        (Jan 1,
                                        4712 A.D
                                        . Default
                                        format (
                                        DD: MM
                                        : yy) .
                 5. LONG                Variable         Its range
                                        legth            is upto
                                        character        2GB .
                                        data .




  AGGREGATE FUNCTIONS IN ORACLE


1 COUNT () :- The COUNT() function is used to determine the
  number of rows or non NULL column values .
  For e.g :-
   S:\> Select count (*) from emp ;

2 SUM () :- This function is use to determine the sum of all selected
  columns .
  For e . g :-
  S:\> Select sum ( sal ) from emp ;

3 MAX () :- This function is used to calculate largest value of all
  selected values of a column .
  For e .g :-
  S:\> select max ( sal ) from emp ;

4 MIN () :- This function is used to calculate the smallest value of all
  selected values of a column .
  For e .g :-
  S:\> select min (comm ) from emp ;

5 AVG () :- This function is use to calculate the average of all selected
  values of a column .
  For e . g :-
  S:\> select avg (sal ) from emp ;




   QUERYING MULTIPLE TABLES

   A join is used to when a Sql query requires data from more than one
   table on data base . Rows in one table may be joined to rows of
   another table according to rows common values existing in
   corressponding columns . There types of joins :

1. Equi join :- When two tables are joined together using quality of
   values in one or more than one columns , they make an equi join .
   Table prefixes are utilized to prevent ambiguity & the clause specifies
   the columns being joined .
   For e . g :-
   S:\> select empno , ename , emp . deptno , dname
   from emp , dept
   where emp . deptno = dept . deptno ;
   Here deptno columns exists in column name should be qualified with
   the table name.


2. Cartesian join :- When no ‘ where ‘ clause is specified , each row of
   one table matches every row of other table . This results in a cartesian
   product .
   For e .g :-
   S:\> select empno , ename , dname , loc , from emp , dept ;

3. Outer join :- If there are values in one table that do not have
   corressponding values in other , in an equi join that row will not be
   selected by using the the outer join symbol ( + ) . The corressponding
   columns for that row will have NULLs.
   For e . g :-
   S:\> select empno , ename , emp.deptno , dname
   , loc , dept . deptno , from emp , dept
   where emp .dept no (+) = dept .deptno ;
   If the symbol (+ ) is placed on the other side of the
   Equation then all the employees details with no corressponding
   Department name & location will be displayed with NULL values in
   DNAME & LOC column .
   The symbol ( + ) cannot be on the both sides.

4. Self – join :- To join the table to itself it means that each row of the
   table is combined with itself & with every other row of the table . The
   self join can be viewed as join of two copies of the same table . The
   table is not actually copied , but SQL performs the command .
   The syntax of the command for joining the table to itself is almost
   same as that for joining two different tables . to distinguish the
   column name from one another , aliases for the actual table names are
   used . Table name aliases are defined in the ‘From ‘ clause of the
   query .
   For e.g :-
   S:\> select worker . name , manager .ename from emp WORKER ,
   Emp MANAGER where WORKER . mgr = MANAGER . mgr ;

5. Non – equi join :- The relation ship between EMP & SALGRADE
   table is
   a non-equi join , in that no column in EMP corressponds directly to a
   column in       SALGRADE . The relationship is obtained using an
   operator other than the equal ( = ) . To evaluate employee’s grade , their
   salary must be ‘ BETWEEN ‘ any pair of the low &
   Hisal ranges . for e .g :-
   S:\> select ename , e . sal , s .grade , from emp E , salgrade S
   Where E . sal between s.losal and s.hisal ;




   SET OPERATORS

   Set operators are used to combine two or more queries into one result
   . Data type of corressponding columns are same . There are three
   types of set operators given below :-

11 UNION :- Multiple queries can be merged together & their results are
   combined , using the ‘ UNION ‘ clause . The ‘ UNION ‘ clause
   marges the outputs of two or more queries into a single set of rows &
   columns . The queries are all executed independently , but their output
   is merged . Only a final query is ends with a semicolon ( ; ) . The
   number of columns in the queries must be the same & of the same
   data type . For e .g :-

   S:\> select job from emp
   Where dept no= 20
   UNION
   S:\> select job from emp
   Where dept no = 30 ;
12 INTERSECT :- The ‘ INTERSECT ‘ operator displays the jobs
   which are common to department 10 & 20 . For e . g :-

   S:\> select job from emp where deptno = 10
   INTERSECT
   S:\> select job from emp where deptno= 20 ;

13 MINUS :- The ‘MINUS’ operator display the list of those jobs which
   are unique to department 20 . For e . g :-

   S:\> select job from emp where deptno = 10
   MINUS
   S:\> select job from emp where deptno = 10
   MINUS
   S:\> select job from emp where deptno = 30 ;


   NESTED QUERIES


   When one query is defined in another query is called nesting queries .
   It has following features which are given below :-

   1 The result of one query is dynamically substituted in the condition
     of another .

   2 Sql first evaluates the inner query / or sub query with in the ‘
     where ‘ clause .

   3 The return value of inner query is then substituted in the condition
     of the outer query .

4 There is no limitation to the level of nesting of queries in ORACLE 8
  .

   5 When using relational operators , ensure that the sub – query return
       a single row output

    6 In some cases , the ‘ DISTINCT ‘ clause can be used to ensure
      single valued output .

    For e . g :-

       1 Select ename from emp where dept no = (select deptno from
         emp where ename = ‘ MILLER ‘) ;

      2 Select ename from emp
    Where sal = (select max ( sal) from emp ) ;

3   Select ename from emp where empno IN ( select empno from incr ) ;




                        FUNCTIONS

    Functions make the basic query block more powerful , & are used to
    manipulate data values . Functions are used to manipulate data items .
    They accept one or more than one arguments & return one value .
    Functions can be used to :-

1. Performs calculations on data .
         2 Modify indidvidual data items .
         3 Manipulate output for group of rows .
         4 Alter date formats for display .
         5 Convert column data types .

    Functions are of different types :-
1) Character Functions :- Single row character function accept
   character data as input & can return both characters & number
   values . The following character functions are :-

        a) LOWER ( col | value ):- This function forces alpha
           charcter values which are in uppercase or mixed case
           into lower case . For e . g :-
           S:\> select LOWER ( Dname ) , LOWER( ‘ Sql
           Course ‘) from dept ;

        b) UPPER :- This function is used to force the user input
           to capitals
           For e . g :- S:\> select ename
           From emp where ename = UPPER (& Ename ) ;

        c) INITCAP :- This function is used to captilized the
           first letter of each
           Word of the string . For e . g :-
           S:\> select INITCAP ( Dname ) , INITCAP (Loc)
           From dept ;
        d) CONCAT :- This function is used to concatenate the
           two words .
           For e . g:- S:\> Select CONCAT (ename , job ) “ JOB
           “
           From emp where empno = 7900 ;


        e) SUBSTR :- This function returns the string of n
           characters form the
           Column & literal value , starting at the position
           number pos . For e . g :- S:\> select substr ( ‘
           ORACLE ‘, 2 , 4 ) , substr ( ‘ Dname ‘ , 3 , 5 )
           From dept ;

        f)    LTRIM :- This function removes from left leading
             occurences of
                 Char specified . If char is not specified will trim any
                 blanks from the
                 Left where ‘ CHAR / s ‘ is treated as a set not as a
                 string .For e .g :-
                 S:\> select dname , LTRIM ( dname , ‘ A ‘ ) , LTRIM
                 (dname , ‘ AS’) , LTRIM (dname , ‘ASOP ‘) from dept
                 ;


             g) RTRIM :- This function removes from the right trailing
                occurrences of
                  Character specified . For e .g :-

              S:\> select dname , RTRIM (dname , ‘A’) , RTRIM (dname , ‘
              GHS’ ) , RTRIM ( dname , ‘ N ’) , from dept ;

2)   NUMBER FUNCTIONS :- Number function accept numeric accept
      numeric input & return the no of numric values . The number of numeric
      functions are :-

               1 ROUND () :- Round function is used to round the column
                 expression or value to n decimal places . For e . g :-
                 S:\> select ROUND(2.456 , +2 ) from dual ;

               2 TRUNC () :- The ‘ TRUNC ‘ function is used to
                  truncate the column or values to n decimal places . For e
                  . g :-
                 S:\> select TRUNC(2.678 , +1) from dual ;

               3 CEIL ():- The ‘CEIL ‘ function is used to find smallest
                  integer greater than or equal to the column , expression
                  or value . For e .g :-
                  S:\> select CEIL( sal ) , CEIL (99.9 ) , CEIL ( 101.6 )
                 from emp ;

               4 POWER () :- Raises the column / expression to the nth
                 power . Can also be negative . For e . g :-
                  S:\> select sal , power ( sal , 4) from dual ;

              5 EXP ():- Returns the e raised to the nth power . e =
                2.71828183 for e.g :-        S:\> select EXP (4) from
                dual ;


3. SQRT ():- It finds the square root of the any value specified in
   brackets . For e.g :-

                  S:\> select SQRT(144 ) from dual ;

             7.     MOD () :- The ‘ MOD ‘ function is used to find out the
                  remainder of value 1 divide by value 2 . for e.g :-
                  S:\> select mod ( 12 ,3 ) from dual ;

  3) DATE FUNCTION :- Date functions are operate on oracle
  dbases . All date function returns the value of DATE
  datatype except month_between which returns a
  numeric value .Types of date functions are :-
        1.       SYSDATE :- Sysdate is a pseudo column that returns
           the current date & time . It selects sysdate from a table called
           dual table . For e .g :-
           S:\> select sysdate from sys.dual ;

        2.         MONTHS_BETWEEN :- Finds the number of months
             between date 1 & date 2 . The rows can be +ve & -ve , If
             date 1 is later than date 2 . For e . g :-
             S:\> select months_between ( sysdate , hiredate) ,
             months_between ( ‘ 01-JAN – 84 ‘, ‘ 05 – NOV – 88 ‘) from
             emp
             Where months_between (sysdate , hiredate ) > 59 ;

        3.         ADD_MONTH :- Add n number of calender months to
             date .n must be an integer & can be negative . For e .g :-
             S:\> select hiredate , add_months ( hiredate , 3) ,
        4.         NEXT_DAY :- It displays date of the next specified
             day of the week following date1. For e . g :-
             S:\> select hiredate , next_day (hiredate , ‘  Friday ‘ ) ,
             Next_day (hiredate , 6 ) from emp ;

        5.          ROUND (Date1 , ‘ Month ‘ ) :- This function returns
             the first of the month date 1 if date1 is in the first half of the
             month ; otherwise returns the first of the following month .
             For e . g :-
             S:\> select sysdate , round (sysdate , ‘ month ‘) from sys.dual ;

        6.         TRUNC :- This function finds the date of the first day of the
             month containing in date1 when char = ‘ month ‘. For e . g :-
             S:\> select sysdate , trunc (sysdate , ‘month ‘)
             , trunc ( sysdate , ‘ year ‘) from sys.dual ;

        7.         TO_CHAR :- This function is frequently used to change date
             format from the default to an alternative display format . For e . g :-
             S:\> select to_char (sysdate , ‘ DAY , DDTH ,MONTH
             YYYY ‘ ) from sys .dual ;

        8.          TO_NUMBER :- The to_number function is used to
             transform a number store as a character to number datatypen .For e
             . g :-
             S:\> select empno , ename , job , sal
             From emp
             Where sal >= To_number ( ‘ 1500 ‘) ;




   DATA MANIPULATION & CONTROL -_I

1) DDL COMMANDS :- DDL stands for ‘ Data Definition Language ‘.
   DDL is the subset of sql commands use to create , modify , or remove
   dbase structure , including tables . These commands have an immediate
   effect on the database , & also record in the data dictionary . It include the
   following set of commands :-

CREATE TABLE COMMAND :- The create table command is used to
  create the table . It is the one of the simplest forms of this command is
  where the basic information for each column defined together with their data
  types & sizes .
  Syntax :-
  Create table table name
  ( column_ name         type (size) ,
  column_name           type (size ) ,
  );

   For e . g :-
   Create table item
   ( item_no         number ( 5 ) ,
   item_code       varchar2 (4 ) ,
   item_name       varchar2 (20 ) ,
   item_price       number (5 , 2) ) ;

      1 ALTER TABLE COMMAND :- Alter table command is used to add
      a column in a database table

   Syntax :-
   Alter table table name add ( column_name        type (size ) ) ;

   For e . g :-
   S:\>Alter table emp add ( spouses_name         varchar2 ( 20 )) ;


      2         DROP TABLE / COLUMN COMMAND :- We can delete
                entire datatbase table using drop table command . We can
                delete column by using the drop table command .
   For e . g :-
   S:\> drop table emp ;
   S:\> drop emp empno ;
15 DML COMMANDS :- DML stands for ‘ Data Manipulation Language ‘
   commands . The SQL DML includes a query language base on both the
   relational algebra & the tuple of the relational calculus . It includes
   following DML commands :-

            3 INSERT COMMAND :- We can insert values into the dbase
              table via ‘INSERT ‘ command . It is possible to a new row
              with values in an each column , in which case the column list is
              not require .

   Syntax :- S:\.> insert into table name (column name 1 ,
   Column name 2 , Column name 3 , …)
   Values (value 1,value 2 , value 3 , … ) ;

   For e . g :- S:\> insert into emp ( empno , ename , sal )
   Values ( 7902 , ‘ pankaj singh ‘ , 7000 ) ;


   When values are provided        to the insert into command it is
   Called a single row insert .
   For e. g :- S:\> insert into emp                          Values ( 7311 , ‘ TOM ‘ , ‘
   CLERK ‘ , 7001 ,
   ‘ 18 – Apr – 95 ‘, 3000 , NULL , 20 ) ;

            4 UPDATE Command :- Columns in a table are updated using
              the update command . Values of a single column or group of
              columns can be updated . Updation can be carried out for all the
              rows in a table or selected rows .Expressions can also be with
              update .

   Syntax :-
   Update table (alias )
   Set column = expression
   Where condition
   For e . g :-
S:\> update emp
Set job = ‘SALESMAN ‘
hiredate = sysdate
where ename = ‘ SCOTT ‘ ;

         5 DELETE COMMAND :- Rows can be deleted from the table
           using the delete command . The entire row is deleted from the
           table . A set of rows can be deleted from the table .

Syntax :-
DELETE from table
[ Where condition ]

For e . g :-
S:\> delete from emp
Where ename = ‘ SCOTT ‘ ;




VIEWS

A view is a virtual table & is like a window to a table . It doesnot have any
data type of its own , but derives from the table it is associated with . The
advantages of the view are :-

         6 Any updation of rows in the table will automatically reflect in
           the view .

         7 As a view does not store any data , the redundancy problem
           does not arise .

         8 Critical data in the database table is safeguarded as access to
           such data can be controlled by using views .
             9 Complicated queries are simplified using views


   Views can be created using ‘ CREATE VIEW ‘ command .


   Syntax :-
   Create view [or replace ] [force ] view
   View name [column 1 , column 2 , …] ;

   For e.g :-
   S:\> create view D10 emp
   As select empno , ename , sal
   From emp
   Where deptno = 10 ;



   MANIPULATING BASETABLES THROUGH VIEWS


   We can manipulate the basetable through views from the following ways :-

1 By using insert , update , & delete commands can also be used with views .

2 While manipulating tables , certain rules are applicable .

3 The crieteria that determines whether the operations are througha view are :-

a) The view must be based on the single table .

b) The view must not have columns that are aggregate function .

c) The view must not specify ‘ Distinct ‘ in its Definition .

d) The view must not use group by or having in its definition .
e) The view must not use subqueries .

f) The views must not be read only view .




   Droping a view

   We can drop an existing view using drop view command . This command
   removes the view definition from the database . Rows & columns are not
   affected since they are stored on the tables from which view was derived .

   Syntax :-

   Drop view viewname ;

   For e . g :-
   S:\> drop view dept30 ;



   DATA MANIPULATION & CONTROL - II

   DATA BASE SECURITY & PRIVILEGES


   Oraclae provides extensive security Features in order to safeguard the
   user ‘s information from both unauthorized accesss & internal damage .
   This security is provided by granting or revoking privileges on a user –
   by – user & privilege – by – privilege basis .
   Every oracle user has a username , & password & own tables , views &
   other objects That he creates . An oracle role is set of privileges , which
   determine the type of access that each user needs , depending upon his /
   her status & responsibilities . Specific privilegs can be grant to roles &
   roles are assigned to appropriate users . Database system privileges let
   the user execute a specific set of commands . Database object privileges
           give the user the ability to perform the certain operation on certain
           specified classes of objects .




           APPLICATIONS PRIVILEGES MANAGEMENT


        The management of the application privileges are done by the following :-

1 By roles that collects the privileges .

2 Oracle 8 . 0 provides three standard roles : connect , resource , DBA .

3 Connect role only allows to use oracle .

4 Resource role allows to create oracle objects such as tables , views , etc .

5 DBA role has the system privileges such as creating users , creating databases , &
  system administration .

6 Role can be created by
                                   Create role <role – name >
                                   [Identified by <password> ] .

                  Once the role has been created , the privileges can be granted .
                  GRANT < priv_list > TO < role_name >
                  A role is then granted to user .

           When granted to the users , Roles are can be thought of as a set of
           privileges . Instead of granting individual privileges to the each user , the
           privileges can be granted to the role & the role is granted to the each user
           . This simplifies the administrative tasks involved in the management of
           pivileges .
ENHANCING PERFORMANCE


We can enhance the system performance by following ways :-

             1 Indexing & clustering are two common way of
               enhancing system performance in retrieving
               information from the table .
             2 SQL syntax does not change with these methods , as
               these database objects are transparent to the user .
             3 INDEXES
                 a ) Indexes provies a fast access path to columns
               that are indexed .
                 b ) Index can also be used to ensure that no
               duplicate values are     entered into a column .
               c ) Oracle analyzes each query to find out the
               fastest paths to the data .
                 d ) Indexes are used to reference records , in all
               the SQL statements .                            e)
               Indexes are store seperately from the actual data .
                                                        f ) Indexes
               do not have to be activated or deactivated .
                                           g ) With every data
               manipulation the appropriate index is automatically
               updated .


  SEQUENCES:- A Sequence is a database object used to generate unique
integers for use as a primary keys . A sequence is created through the ‘
CREATE SEQUENCE ‘ command .
Syntax : -
Create sequence seq_name
[ Increment by < n > ]
[ start with < m >] ;
The default increment value is 1 . ‘START WITH ‘ is the number
with which the sequence will begin . < seq_name > URRVAL
                 returns the current value of sequnce .< seq_name
                 >         NEXT_VAL returns the next value of the
sequnce          . It also increments the value .

For e . g :-
S:\> Create sequence empno INCREMENT                             by 1
start with 8890
Insert into em ( empno , ename , sal )
Values ( empno . nextval , ‘ HANS ‘ ,                            6000
);




MAINTAINING DATABASE OBJECTS

Data base objects are created & stored in a dbase . Tables , clusters , views
indexes , etc are the examples of a database objects . Maintainence of
database objects involves creating , altering & deleting them from the dbase
. Modifications are done using ‘ Alter Table ‘ command . Deleting is done
by using ‘ Delete Table ‘ command .

Syntax :-
ALTER TABLE < table _ name >
{ ADD ( < col – element > | < constraints > ……
[ MODIFY < col _ elements > ]
}

ALTER cluster < cluster_name >
GRANT :- The ‘ GRANT’ command is used to craete user &
grant access to the database . It requires DBA privilege , except
that user can change his / her passsword . A user can grant
access to hi database object to other user .

Syntax :-
GRANT < database _ priv [ , database_ priv ]
To < user_name >
Indentified by < password >

For e . g :-
S:\> GRANT CONNECT , RESOURCE
To thomas
Identified by enquist ;

S:\> GRANT ALL
On emp
To public ;



REVOKE :- The ‘ REVOKE ‘ command is used to revoke the
database privileges from the user by DBA . The user can be
revoke the grants to other on his objects .

Syntax :-
REVOKE < dbase priv >
FROM < user [ , user] > ;

For e.g :-      S:\> REVOKE CONNECT , RESOURCE , FROM
thomas ;

S :\> Revoke update on emp from public ;



ROLLBACK :- The ‘ ROLLBACK ’ command is used to undo
the changes performed through insert , ROLLBACK ‘ command
                                              ROLLBACK
is used to discard path or all the work the user has done in
transaction .
For e . g :-
S:\> insert into dept values ( GO , ‘ PURCHASE ‘ , ‘ DELHI ‘ ) ;
S:\> savepoint s1 ;
S :\> Update emp set deptno = 60
Where ename = ‘ SMITH ‘ ;
S :\> ROLLBACK to savepoint s1 ;
Save point identifies a point in a transcation to which one can later rollback via
‘ROLLBACK ‘ command .


COMMIT :- The ‘ COMMIT ‘ command is used to make the
changes permanent . When this command is executed . The ‘ ROLL
BACK ‘ is not use after the commit command .

For e . g :-
S:\> insert into dept values ( 50 , ‘ CORRICULUM ‘ , ‘ DELHI ‘ ) ;
S:\> select * from dept ;
S:\> commit ;




END



PL / SQL ( PROCEDURAL LANGUAGE SQL )



PL / SQL (PROCEDURAL LANGUAGE / SQL is an extension to
SQL , incorporating many of the design features of the programminng
languages . It allows the data manipulation & query statements of
SQL to be included with in the the block structure & procedural
units of code , making PL/ SQL Powerful transcation query
language .
The advantages of the PL / SQL are as following :-

              1 BLOCK STRUCTURE :- PL / SQL is a block
                structured language , meaninig that programs can
                be divided into logical blocks , each block
                containing the the language resources that are
                logically required in that unit . Variables are
                declared locally to the block in which they will be
                used .
              2 FLOW OF CONTROL :- Conditional statements ,
                loops , & branches may be employed to control the
                procedural flow of the program . This can avoid the
                need to place commands into separate trigger –
                steps .
              3 PORTABILITY :- Since PL / SQL is native to
                oracle , programs may be transported across any
                host environment in which ORACLE & PL / SQL
                are supported .
              4 INTEGRATION :- PL / SQL is playing an
                increasingly central role in both the RDBMS & in
                ORACLE tools . The variables & dataypes of PL /
                SQL are compatible with those of SQL . Therefore ,
                PL / SQL bridges , the gap between convenient
                access to dbase technology .
              5 PERFORMANCE :- The use of PL / SQL can help
                to improve the perfromance of an application .


PL / SQL BLOCK STRUCTURE


As we know that the PL / SQL is a block - structured language .
Every unit of PL / SQL compromises one or more blocks . These
blocks may be entirely separate , or nested one within another . One
block may represent just a small part of another , which in turn may
be just a part of the whole unit of code .
The PL / SQL block structure is given below :

DECLARE
< declaration >
BEGIN
< Executable Statements >
EXCEPTION
<Exception – handlers >
END ;
The BEGIN & END statements are mandatory , & enclosed the
body of the of actions to be performed . The DECLARE section is
optional & used to declare PL / SQL objects such as variables that are
to be referenced in the block , or a nested block with in it . Finally ,
the EXECPTION section is used to trap the predefined error
condtions .



DECLARING PL / SQL VARIABLES & CONSTANTS

PL / SQL supports a variety of data types which may optionally be
assigned a value when declare , and are allowed to change their
further assignments . Constants are identifiers which had a fixed value
that must be assigned when a constant is declared .
Variables are declared in the DECLARE section of the PL / SQL
block . Declaration must involved name of the variables followed by
its datatype . All statements must end with a semicolon ( ; ) .
Constants are declared by specifying the keyword CONSTANT
before the data type . The general syntax :-

Var_name type [ : = < value > ] ;

For e . g :-
Mname char (20 ) ;
Age      number ( 3 ) ;
Dept no number ( 4 ) = 30 ;
To avoid the type & size conflict between a variable & the column of
the table , the attributes % TYPE is used . Advantage of this
method of defining a variable that , whenever the type & size of a
column in the table is changed , it is automatically reflected in the
variable declaration . For e . g :-
Temp_name           emp.ename %TYPE ;



SCOPE & VISIBILTY OF A VARIABLE

The ‘ scope ‘ of an object is the region of the program over which
that object may potentially be used . These rules are apply to all
declared objects , including variables , cursors , user – defined
exceptions & contants .
The scope of an object is the complete block in which it is declared ,
including any sub blocks nested within it . Objects declared in
subblock are only available until that subblock has ended . For e . g :-

DECLARE
X     number ;
BEGIN
...
DECLARE
Y      number ;
BEGIN
...
END ;
END ;

In the example above , variable ‘ X ‘ of the subblock disable the use
of variable ‘ x ‘              from the main block until the
subblock’s variable from the main block until the subblock’s
variable reaches the end of its scope . During the subblock , the
variable ‘ x ‘ from the main      block still it exists , but has no
visibilty .
ASSIGNMENTS & EXPRESSIONS

The PL / SQL assignment statements allows a value to be assigned or
reassigned to a variable after the declare section of the block . The
variable to receive the new value must be explicitly named on the left
of the assignment operator .
Syntax :-
Identifier := expression ;
Where ‘ identifier ‘ is the name of the target variable , or field , to
receive the value of ‘ expression ’.
The expression may be literal , the name of another existing variable ,
or any complexity of the expression necessary to determine the value
to be assigned . Generally , most of the expressions available in select
clause of an SQL query are available here . The functions supported in
PL / SQL expression are discuss later in this unit . For e . g :-
V_count : = v_count + 1 ;

Ann_sal : = month sal*12 + NVL ( COMM , 0 ) ;
Lev : = 6 ;
V_name : = ‘ KING ‘ ;

The assignments to boolean variables may be ‘ TRUE ‘ or ‘ FAlSE ‘
, or the result of a boolean expression .

Overpaid : = ann_sal > 35000 ;

Male : = upper ( title ) ;

Salary_ok : = not over paid ;

Female : =    not male ;

Switch 1 : = TRUE ;
ORACLE           PRACTICAL   FILE




CREATED




BY




Varun bhardwaj

SUBMITTED BY :-

NAME :- KAMALJIT SINGH.

CLASS :- B.C.A – II

Rollno= 10739
              1
              2

Using commit:
A commit ends the current transaction and makes permanent any changes
made during the transaction . All transactional locks acquired on tables are
released .
Syntax:
COMMIT;
Using:
ROLLBACK
A rollback does exactly the opposite of commit . it ends the transaction but
undoes any changes made during the transaction .All transactional locks
acquired on tables are released .
syntax :
ROLLBACK[work][to[savepoint]savepoint]
Where:
Work:is optional and is provided for ansi compatibility.
Savepoint: is optional and is used to rollback a partial transaction as far as
the specified save point :
Creating save point :
Save point marks and saves the current point int the processing of a
transaction. When a save point is used with a rollback statement parts of a
transaction can be undone .an active save point is one that is specified since
the last commit or rollback .
SYNTAX :
Savepoint savepointname;
WHAT IS A CURSOR ?
The oracle engine uses awork area for its internal processing in order to
execute an sql statement. This work area is private to sql’s operations and is
called a cursor .
Example:
Select empno,ename ,job ,salary from employee where deptno=20;
                 :
Cursors are classified in two ways .
1.Implicit cursors.
2.explicit cursors.

When the sql statement is not associated with an explicitly declared curscr
then oracle implicitly opens a cursor to process sql statements .
2 . explicit cursor is declared within the declare section to specify the query
to be executed for the cursor .then the open statement executes the query
associated with the curs   or to form the active set.

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:20
posted:11/23/2012
language:
pages:36