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
Ask questApollo Hospitals (AH) is a popular medical service provider in Oman. AH wants to automate its business functions into an information system. The proposed information syst

how to construct labelled precedence graph for view serialiability.please explain insteps with one solved examples

What is the meaning of Recovery? In deferred update methods, if a transaction fails before arriving its commit point, it will not have altered the database in any way, so UNDO

Give the reasons for allowing concurrency? The reasons for allowing concurrency is if the transactions run serially, a short transaction might have to wait for a preceding long

Explain the benefit of OOM. Main benefits of object oriented modeling are development of system become fast, quality of the system get improve. It gives freedom of use of alrea

write an SQL statement to create the database in MYSQL server

Nested Queries Let us see some more complex queries in this section. Sub-queries: Some of the basic concerns of sub-queries are: A sub-query is a SELECT statemen

What are the uses of functional dependencies? To test relations to see whether they are legal under a given set of functional dependencies. To state constraints on the set

Assume that you are responsible for selecting a new DBMS product for Sohar Hospital

What are the difference between clustered and a non-clustered index? A clustered index is a special type of index that reorders the way records in the table are physically stor