Creating a view-data control, Database Management System

Creating a view:

  • A query can be embedded within the CREATE VIEW STATEMENT
  • A query can have complex select statements containing join, groups and sub- queries
  • A query that describes the view cannot contain an order by clause.
  • DML operation (delete/ add / modify) cannot be applied if the view have any of the following:

 

Delete (You can't delete if view contains following)

Modify (you cannot modify if view contains following)

Insert (you cannot insert if view contains following)

  • Group functions
  • A group by clause
  • A distinct keyword
  • Group functions
  • A group by clause
  • A distinct keyword
  • Columns defined by

Expressions

  • Group functions
  • A group by clause
  • A distinct keyword
  • Columns defined by

Expressions

  • There are Not Null Columns in the base tables that are not selected by view.

Example:  Create a view named employee salary having minimum, maximum and average salary for every department.

CREATE VIEW EMPSAL (NAME, MINSAL, MAXSAL, AVGSAL) AS

SELECT D.DNAME, MIN(E.SAL),MAX(E.SAL),AVG(E.SAL)

 FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;

To view the result of the command above you can give the following command:

SELECT * FROM EMPSAL;

You may get some sample output like:

NAME   MINSAL          MAXSA                 AVG                    SAL

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

ACCOUNTING            1300                      5000           2916.6667

RESEARCH                 800                      3000               2175

SALES                      950                      2850           1566.6667

To view the structure of the view so created, the command is given below:

DESCRIBE EMPSAL;

Name                                                 Null?                   Type

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

NAME                                                VARCHAR2           (14)

MINSAL                                               NUMBER

MAXSAL                                              NUMBER

AVGSAL                                              NUMBER

 

Posted Date: 3/11/2013 1:24:45 AM | Location : United States







Related Discussions:- Creating a view-data control, Assignment Help, Ask Question on Creating a view-data control, Get Answer, Expert's Help, Creating a view-data control Discussions

Write discussion on Creating a view-data control
Your posts are moderated
Related Questions
What is OLAP? OnLine Analytical Processing (OLAP) is a Business Intelligence (BI) reporting system. OLAP gives the user with the capability to sum, count, average and do other

Describe the term- State Diagram State Diagram: An object could receive a sequence of the input instructions. The state of the object can differ depending upon sequence of in


What is known as sequential file organization? In the sequential file organization, the records are kept in sequential order, according to the value of a "search key" of every

How nulls are treated in comparison operator? Along with comparison (relational) operators, the null values are ignored as we cannot derive the relation along with the given op

i have a homework assignment identical to one you have listed

By using the delete operation some consisting records can be deleted from a relation. To delete some definite records from the database a condition is also specified based on which

Designing the fields in the data base The following are the considerations one has to remain in mind while designing the fields in the data base. Coding, compression, en

Let us first look into some of the important features of ER model. Entity relationship model is a high-level conceptual data model. It permits us to define the data invo

What is self-transition? If there is a transition which goes back to same state, it is known as "self- transition." With a trigger action exit action would be executed f