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 Radix conversion method  One clever way to transform binary numbers to BCD notation (binary-coded decimal) is the "double dabble algorithm". It can be adapted to transfo

Submit the table creation statements (including constraints) for the Database Model. Submit them all in a single script file. Also submit a document explaining what test data yo

In this exercise you will create ten records, each containing the following fields: student name, class:  Freshman, Sophomore, Junior or Senior, Major:  Liberal Arts or General Sci

A relationship set is a set of relationships of the similar type. For example, let consider the relationship among two entities sets course and student. Collection of all the in

Explain differance between Primary and secondary storage ? Primary and secondary storage Computer storage is classified within primary(main) memory and secondary(per

Ask questiwhat is row indicator in database on #Minimum 100 words accepted#

Data Scrubbing 70,000 records Project Description: We need services of a developer competent in data cleansing/Data scrubbing. Scope: - 70,000 records in CSV format.

Let us now talk about the relational operations: SELECT The select operation is used to select few specific record from the database based on different criteria, This is a un

BCNF Boyce-Codd Normal Form A relation schema R is in BCNF if it is in 3NF and accepts an additional constraint that for every FD X A, X has to be a candidate key. If a relati

Part 1: Security Plan Consider the Case study and identify 3 security requirements. For each of these security requirements: • Discuss the necessity for the feature and how