Creating a view-data control, Database Management System

Assignment Help:

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

 


Related Discussions:- Creating a view-data control

Logical entity relationship diagrams, How can i turn a conceptual entity re...

How can i turn a conceptual entity relationship diagram into a logical entity relationship diagram? what do i have to do with "mandetory or" and "optional or" relationships?

What are the objectives of object design, What are the Objectives of object...

What are the Objectives of object design After going through this unit, you could be able to: explain steps of object design; discuss algorithms that reduce costs;

Define the normalisation form, Define in which normalisation form if an att...

Define in which normalisation form if an attribute of a composite key is dependent on an attribute of other composite key. Ans: 3NF A relation is in 3 NF if an attribute of

What does conceptual design work, What does conceptual design work? A c...

What does conceptual design work? A conceptual design involves modelling independent of the DBMS.

How many levels can be viewed architecture of the database, How many levels...

How many levels can be viewed in Architecture of the database ? Architecture of the database can be viewed as three levels.

System error-database errors, System error :These contain errors in databas...

System error :These contain errors in database system or the OS, example, deadlocks.Such errors are fairly hard to detect and need reprogramming the erroneous components of the sys

Explain olap and the benefits of olap, Data Mining 1. What is data mini...

Data Mining 1. What is data mining? Write Data Mining applications. 2. Explain OLAP? Write the benefits of OLAP. 3. What are the key features of a Data Warehouse? 4. W

Distributed query and transaction processing, a.  Construct a query around ...

a.  Construct a query around any one of the functional divisions you made in 4a such that if executed in the distributed design of 4 would require reconstruction of at least two ho

What do you understand by data ware house, Question 1: Define the term ...

Question 1: Define the term business intelligence tools? Briefly explain how the data from one end gets transformed into information at the other end? Definition Exp

Stock market, need requirement analysis for database management of stock ma...

need requirement analysis for database management of stock market

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd