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
Determine the Three Views of Data We know that the similar thing, if viewed from dissimilar angles produces difference sights. Similarly, the database that we have formed alrea

Mention various steps required to draw an E-R Diagram. Draw an E-R Diagram for Student Information System. Mention all suitable assumptions to justify your answer.

What are uncommitted modifications? The immediate-modification technique permits database modifications to be output to the database whereas the transaction is still in the act

Define database management system? Database management system (DBMS) is a set of interrelated data and a combination of programs to access those data.

Using a database design approach of your choice, produce a logical design for Pharma. 1) Your answer must consist of ONE the following: An entity-relationship (ER) diagra

Oracle Fusion Middleware: Key Skills: Consulting,Architect, Oracle Fusion Middleware,ECM, UCM, Stellent,Identity Management, SOA Suites, BPEL, ESB,OAM,OIM,J2ee, Java, Livelink

what is cascading? what is rollback cascading? please explain in detail

Update Anomaly : This anomaly is occurred due to data redundancy. Redundant information makes updates more complex since, for example, varying the name of the instructor of MCS-014

1. Explain different addressing modes of 8086 Explanation of all addressing modes 2. Write a sequence of instructions to exchange two register contents using stack

Reflects organisational structure: Many organizations are distributed over various locations. If an organisation has many offices in dissimilar cities, databases used in such an ap