Tuesday, February 27, 2007

Introduction to Oracle9i: SQL (8)

Creating Views

  • Percentile_Cont is used for obtaining the median value of an ordered group. Regr_Avgx takes in a pair of list of values and then calculates the average of the second list after eliminating all the Nulls.
  • Minimum, Count and Variance are valid summary operations.
  • A view can be created as a join on two or more tables. This type of view is called complex view.
  • (Syntax)
    VIEW [schema.]view [(alias [,alias]...)]
    AS subquery
    [WITH READ ONLY [CONSTRAINT constraint]] ;
  • You cannot remove a row if the view contains the following:
  • You cannot modify data in a view if it contains:
  • You cannot add data through a view if the view includes:
    - Grouping functions
    - A GROUP BY clause
    - The DISTINCT keyword
    - The pseudocolumn ROWNUM keyword

    - Columns defined by expressions
    - NOT NULL columns in the base tables that are not selected by the view.
  • The name and definition of the view is stored in the data dictionary ‘user_view’
  • A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.
  • DROP VIEW trans_view;
  • A view can be created if the base table does not exist but constraints cannot be defined on the view without the DISABLE NOVALIDATE clause.
  • TABLE PRIVILEGES data dictionary view gives the information of the all OBJECT privileges granted to the user.
  • USER_OBJECTS : Information of all the objects created by the user.
  • USER_COL_PRIVS_RECD : Privileges granted to the users on the specific columns of the table.
  • USER_TAB_PRIVS : Privileges granted to the users on the specific tables.
  • We cannot index a view
  • (Syntax)
    INSERT INTO managerid(id, name, salary, hiredate)
    SELECT empno, ename, sal, hiredate
    FROM emp WHERE job=’MANAGER’;

Read more!

No comments: