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
Explain the EXEC statement in SQL  Ans: All statements which start with EXEC SQL are embedded SQL database statements. High level languages such as C can be employed to write a

What is Structured Query Langauge (SQL)? SQL is a powerful set-oriented language which was formed by IBM research for the databases that adhere to the relational model. It havi

Database design Database tables will be created for you on the Technology web server. The schema will be as follows. Please note that this is a simplified database structure, d

Use again the GE Flight Quest Data and build on the work done for homework 1 and 2. The goal is to create a classification model which classifies flights into "on-time" (you can de

Create role named role_table that allows a user to create tables. Using role_table allow users kripa and reena to create tables. CREATE ROLE role_table; GRANT CREATE ANY TABLE

external and internal mapping with examples

Define the terms i) Entity type ii) Entity set Entity type: An entity type describes a collection of entities that have the similar attributes. Entity set: The set of all en

1. Use the Invoice table structure shown below to answer the following questions: a)  Draw the dependency diagram and identify the primary key and all dependencies, including al

Consider the following relations:  S (S#, SNAME, STATUS, CITY)  SP (S#, P#, QTY)  P (P#, PNAME, COLOR, WEIGHT, CITY) Give an expression within SQL for each of queries b

Consider the E/R schema with weak entity sets shown below. Come up with the SQL CREATE TABLE statements (with appropriate primary key, unique and foreign key constraints). Include