Creating Views

Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Objectives

After completing this lesson, you should be able to do:

• Create simple and complex views
• Retrieve data from views
• Querying the dictionary views for the view information

4- 2 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Lesson Agenda

• Overview of views
• Creating, modifying, and retrieving data from a view
• Data Manipulation Language (DML) operations on a view
• Dropping a view

4- 3 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Database Objects

Object Description

Table Basic unit of storage; composed of rows

View Logically represents subsets of data from one or
more tables

Sequence Generates numeric values

Index Improves the performance of data retrieval
queries

Synonym Gives alternative names to objects

4- 4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 What Is a View?

EMPLOYEES table

4- 5 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Advantages of Views

To restrict To make complex

data access queries easy

To provide data To present different

independence views of the same
data

4- 6 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Simple Views and Complex Views

Feature Simple Views Complex Views

Number of tables One One or more

Contain functions No Yes

Contain groups of data No Yes

DML operations through a Yes Not always

view

4- 7 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Lesson Agenda

• Overview of views
• Creating, modifying, and retrieving data from a view
• Data Manipulation Language (DML) operations on a view
• Dropping a view

4- 8 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Creating a View

• You embed a subquery in the CREATE VIEW statement:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

• The subquery can contain complex SELECT syntax.

4- 9 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Creating a View

• Create the EMPVU80 view, which contains details of the

employees in department 80:
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

• Describe the structure of the view by using the SQL*Plus

DESCRIBE command:
DESCRIBE empvu80;

4- 10 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Creating a View

• Create a view by using column aliases in the subquery:

CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

• Select the columns from this view by the given alias

names.

4- 11 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Retrieving Data from a View

SELECT *
FROM salvu50;

4- 12 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Modifying a View

• Modify the EMPVU80 view by using a CREATE OR REPLACE

VIEW clause. Add an alias for each column name:

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM employees
WHERE department_id = 80;

• Column aliases in the CREATE OR REPLACE VIEW clause

are listed in the same order as the columns in the
subquery.

4- 13 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Creating a Complex View

Create a complex view that contains group functions to display

values from two tables:

CREATE OR REPLACE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN([Link]),
MAX([Link]),AVG([Link])
FROM employees e JOIN departments d
USING (department_id)
GROUP BY d.department_name;

4- 14 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 View Information

1 DESCRIBE user_views

2 SELECT view_name FROM user_views;

3 SELECT text FROM user_views

WHERE view_name = 'EMP_DETAILS_VIEW';

4- 15 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Lesson Agenda

• Overview of views
• Creating, modifying, and retrieving data from a view
• Data Manipulation Language (DML) operations on a view
• Dropping a view

4- 16 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Rules for Performing DML Operations on a View

• You can usually perform DML operations on

simple views.
• You cannot remove a row if the view contains the
following:
– Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword

4- 17 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Rules for Performing DML Operations on a View

You cannot modify data in a view if it contains:

• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Expressions

4- 18 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Rules for Performing DML Operations on a View

You cannot add data through a view if the view includes:

• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions
• NOT NULL columns without default value in the base
tables that are not selected by the view

4- 19 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Using the WITH CHECK OPTION Clause

• You can ensure that DML operations performed on the

view stay in the domain of the view by using the WITH
CHECK OPTION clause:
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;

• Any attempt to INSERT a row with a department_id

other than 20 or to UPDATE the department number for any
row in the view fails because it violates the WITH CHECK
OPTION.

4- 20 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Denying DML Operations

• You can ensure that no DML operations occur by adding

the WITH READ ONLY option to your view definition.
• Any attempt to perform a DML operation on any row in the
view results in an Oracle server error.

4- 21 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Denying DML Operations

CREATE OR REPLACE VIEW empvu10

(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY ;

4- 22 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Lesson Agenda

• Overview of views
• Creating, modifying, and retrieving data from a view
• Data Manipulation Language (DML) operations on a view
• Dropping a view

4- 23 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Removing a View

You can remove a view without losing data because a view is

based on underlying tables in the database.
DROP VIEW view;

DROP VIEW empvu80;

4- 24 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Quiz

You cannot add data through a view if the view includes a

GROUP BY clause.
a. True
b. False

4- 25 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Summary

In this lesson, you should have learned how to:

• Create, use, and remove views
• Query the dictionary views for view information

4- 26 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

 Practice 4: Overview

This practice covers the following topics:

• Creating a simple view
• Creating a complex view
• Creating a view with a check constraint
• Attempting to modify data in the view
• Querying the dictionary views for view information
• Removing views

4- 27 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.