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
Define shadow paging. An alternative to log-based crash recovery method is shadow paging. This technique requires fewer disk accesses than do the log-based methods.

What is a Database? To know what database is, we have to start from data, which is the basic building block of any DBMS. Data: Facts, figures, statistics etc. having no pa

What is the main difference between read-only and update methods? A read-only method does not affect the values of a variable in an object, whereas an update method may alter t

Draw an EER diagram that captures the "is-a" hierarchy of students in another simple database. The database captures both the "basic" information (say, student ID and student name)

Database Manager It is the interface among low-level data, queries and application programs. Databases typically need a large amount of storage space. It is kept on disks, as

Define single valued and multivalued attributes.  Single valued attributes: attributes with a single value for a particular entity are known as single valued attributes.   M

Need for Multiple Access Paths In practice, most of the online information systems need the support of multi-key files. For example, consider a banking database application hav

For the relations R and S given below: R                      S A B C 1 4 7 2 5 8 3 6 9

Question 1 Describe the following- Clustering Indexing Question 2 Explain the following with suitable real time examples- Implementation of Integrity rules

Describe the host language? Host Language: You could write applications along with SQL statements embedded inside a host language. The SQL statements give the database inter