Introduction to Oracle9i: SQL (8)

Creating Views
##CONTINUE##

• 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)
  CREATE [OR REPLACE] [FORCE NOFORCE]
  VIEW [schema.]view [(alias [,alias]...)]
  AS subquery
  [WITH CHECK OPTION [CONSTRAINT constraint]]
  [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’;