Creating Views
Objectives
This lesson covers the following objectives:
- List three uses for views from the standpoint of a database administrator
- Explain, from a business perspective, why it is important to be able to create and use logical subsets of data derived from one or more tables
- Create a view with and without column aliases in the subquery using a single base table
- Create a complex view that contains group functions to display values from two tables
- Retrieve data from a view
Purpose
- Take a minute to look back at what you’ve learned so far as an Oracle Academy student.
- How easy would it be to explain what you know to someone who hasn’t taken this class?
- You should pat yourself on the back!
- The level of knowledge you have acquired is understood by only a select few.
- Now, imagine yourself as the Database Administrator of a business.
- What do you do when a manager asks you to make it possible for him to be able to retrieve and input data using the company’s database?
- “Don’t make it too complicated. I just want to be able to prepare reports about all our operations.”
- Should these employees have access to all of the company’s data?
- How will they execute commands that require join conditions?
- Is it wise to allow data input from anyone?
- These are questions that you, as DBA, need to know how to answer.
- In this section, you will learn how to create “views” — virtual representations of tables customized to meet specific user requirements.
View
- A view, like a table, is a database object.
- However, views are not “real” tables.
- They are logical representations of existing tables or of another view.
- Views contain no data of their own.
- They function as a window through which data from tables can be viewed or changed.
- The tables on which a view is based are called “base” tables.
- The view is a query stored as a SELECT statement in the data dictionary.
CREATE VIEW view_employees
AS SELECT employee_id,first_name, last_name, email
FROM employees
WHERE employee_id BETWEEN 100 and 124;
SELECT *
FROM view_employees;

Why Use Views?
- Views restrict access to base table data because the view can display selective columns from the table.
- Views can be used to reduce the complexity of executing queries based on more complicated SELECT statements.
- For example, the creator of the view can construct join statements that retrieve data from multiple tables.
- The user of the view neither sees the underlying code nor how to create it.
- The user, through the view, interacts with the database using simple queries.
- Views can be used to retrieve data from several tables, providing data independence for users.
- Users can view the same data in different ways.
- Views provide groups of users with access to data according to their particular permissions or criteria.
Creating a View
- To create a view, embed a subquery within the CREATE VIEW statement.
- The syntax of a view statement is as follows:
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view [(alias [,
alias]...)] AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];


- Example:
CREATE OR REPLACE VIEW view_euro_countries
AS SELECT country_id, region_id, country_name, capitol
FROM wf_countries
WHERE location LIKE '%Europe';
SELECT * FROM view_euro_countries
ORDER BY country_name;

Guidelines for Creating a View
- The subquery that defines the view can contain complex SELECT syntax.
- For performance reasons, the subquery that defines the view should not contain an ORDER BY clause. The ORDER BY clause is best specified when you retrieve data from the view.
- You can use the OR REPLACE option to change the definition of the view without having to drop it or regrant object privileges previously granted on it.
- Aliases can be used for the column names in the subquery.
CREATE VIEW Features
- Two classifications of views are used: simple and complex.
- The table summarizes the features of each view.

Simple View
- The view shown below is an example of a simple view.
- The subquery derives data from only one table and it does not contain a join function or any group functions.
- Because it is a simple view, INSERT, UPDATE, DELETE, and MERGE operations affecting the base table could possibly be performed through the view.
CREATE OR REPLACE VIEW view_euro_countries
AS SELECT country_id, country_name, capitol
FROM wf_countries
WHERE location LIKE '%Europe';
- Column names in the SELECT statement can have aliases as shown below.
- Note that aliases can also be listed after the CREATE VIEW statement and before the SELECT subquery.

- It is possible to create a view whether or not the base tables exist.
- Adding the word FORCE to the CREATE VIEW statement creates the view.
- As a DBA, this option could be useful during the development of a database, especially if you are waiting for the necessary privileges to the referenced object to be granted shortly.
- The FORCE option will create the view despite it being invalid.
- The NOFORCE option is the default when creating a view.
Complex View
- Complex views are views that can contain group functions and joins.
- The following example creates a view that derives data from two tables.
CREATE OR REPLACE VIEW view_euro_countries
("ID", "Country", "Capitol City", "Region")
AS SELECT c.country_id, c.country_name, c.capitol, r.region_name
FROM wf_countries c JOIN wf_world_regions r
USING (region_id)
WHERE location LIKE '%Europe';
SELECT *
FROM view_euro_countries;

- Group functions can also be added to complex-view statements.
CREATE OR REPLACE VIEW view_high_pop
("Region ID", "Highest population")
AS SELECT region_id, MAX(population)
FROM wf_countries
GROUP BY region_id;
SELECT * FROM view_high_pop;

Modifying a View
- To modify an existing view without having to drop then re-create it, use the OR REPLACE option in the CREATE VIEW statement.
- The old view is replaced by the new version.
- For example:

Terminology
Key terms used in this lesson included:
- Alias
- Complex view
- CREATE VIEW
- FORCE
- NOFORCE
- REPLACE
- Simple view
- Subquery
- View
- VIEW_NAME
Summary
In this lesson, you should have learned how to:
- List three uses for views from the standpoint of a database administrator
- Explain, from a business perspective, why it is important to be able to create and use logical subsets of data derived from one or more tables
- Create a view with and without column aliases in the subquery using a single base table
- Create a complex view that contains group functions to display values from two tables
- Retrieve data from a view
0 Comments