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
Strict 2PL :Though, this basic 2PL suffers from the trouble that it can result into loss of isolation / atomic property of transaction as theoretically speaking once a lock is rele

Write short notes on domain relational calculus The domain relational calculus uses domain variables that take on values from an attribute domain rather than values for whole t

what do you mean by consistency in rdbms?

What is vertical fragmentation? Vertical fragmentation divides the relation by decomposing the scheme R of relation r.

compare the features of oracle RDBMS with MySQL and Microsoft SQL server

Explain Superkey Ans: A superkey is described in the relational model of database organization like a set of attributes of a relation variable for which it holds that in all re

for the following business rules . Identify all entities, attributes, relationships, primary keys, cardinalities and constraints where appropriate 3. (9) An art museum owns a larg

MySQL statements for extending the database to incorporate new facilities Deliverables for submission You must submit the following deliverables in the Assignment Dropbox in

List and explain the memory constructs of the Oracle 12c databases?

The issues relating to the Design of the Physical Database Files Physical File is a file as stored on the disk. The major issues relating to physical files are:  •      Cons