Creating Views - oracle database
salmanmemon77964
2,076 views
26 slides
Jan 01, 2016
Slide 1 of 26
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
About This Presentation
After completing this lesson, you should be able
to do the following:
Describe a view
Create, alter the definition of, and drop a view
Retrieve data through a view
Insert, update, and delete data through�a view
Create and use an inline view
Perform “Top-N” analysis
http://phpexecutor.com
Slide Content
11
Copyright © Oracle Corporation, 2001. All rights reserved.
Creating Views
Copyright © Oracle Corporation, 2001. All rights reserved.
Creating Views
11-2 Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives
After completing this lesson, you should be able
to do the following:
•Describe a view
•Create, alter the definition of, and drop a view
•Retrieve data through a view
•Insert, update, and delete data through
a view
•Create and use an inline view
•Perform “Top-N” analysis
Objectives
After completing this lesson, you should be able
to do the following:
•Describe a view
•Create, alter the definition of, and drop a view
•Retrieve data through a view
•Insert, update, and delete data through
a view
•Create and use an inline view
•Perform “Top-N” analysis
11-3 Copyright © Oracle Corporation, 2001. All rights reserved.
Database Objects
Description
Basic unit of storage; composed of rows
and columns
Logically represents subsets of data from
one or more tables
Generates primary key values
Improves the performance of some queries
Alternative name for an object
Object
Table
View
Sequence
Index
Synonym
Database Objects
Description
Basic unit of storage; composed of rows
and columns
Logically represents subsets of data from
one or more tables
Generates primary key values
Improves the performance of some queries
Alternative name for an object
Object
Table
View
Sequence
Index
Synonym
11-4 Copyright © Oracle Corporation, 2001. All rights reserved.
What Is a View?
EMPLOYEES Table:
What Is a View?
EMPLOYEES Table:
11-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Why Use Views?
•To restrict data access
•To make complex queries easy
•To provide data independence
•To present different views of the same data
Why Use Views?
•To restrict data access
•To make complex queries easy
•To provide data independence
•To present different views of the same data
11-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Simple Views
and Complex Views
Feature Simple ViewsComplex Views
Number of tables One One or more
Contain functions No Yes
Contain groups of dataNo Yes
DML operations
through a view Yes Not always
Simple Views
and Complex Views
Feature Simple ViewsComplex Views
Number of tables One One or more
Contain functions No Yes
Contain groups of dataNo Yes
DML operations
through a view Yes Not always
11-7 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a View
•You embed a subquery within the CREATE VIEW
statement.
•The subquery can contain complex SELECT syntax.
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
Creating a View
•You embed a subquery within the CREATE VIEW
statement.
•The subquery can contain complex SELECT syntax.
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
11-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a View
•Create a view, EMPVU80, that contains details of
employees in department 80.
•Describe the structure of the view by using the
iSQL*Plus DESCRIBE command.
DESCRIBE empvu80
DESCRIBE empvu80
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
View created.View created.
Creating a View
•Create a view, EMPVU80, that contains details of
employees in department 80.
•Describe the structure of the view by using the
iSQL*Plus DESCRIBE command.
DESCRIBE empvu80
DESCRIBE empvu80
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
View created.View created.
11-9 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a View
•Create a view by using column aliases in the
subquery.
•Select the columns from this view by the given
alias names.
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
View created.View created.
Creating a View
•Create a view by using column aliases in the
subquery.
•Select the columns from this view by the given
alias names.
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
View created.View created.
11-10 Copyright © Oracle Corporation, 2001. All rights reserved.
Retrieving Data from a View
SELECT *
FROMsalvu50;
Retrieving Data from a View
SELECT *
FROMsalvu50;
11-11 Copyright © Oracle Corporation, 2001. All rights reserved.
Querying a View
USER_VIEWSUSER_VIEWS
EMPVU80EMPVU80
SELECT employee_id,
last_name, salary
FROM employees
WHERE department_id=80;
iSQL*Plus
SELECT *
FROM empvu80;
EMPLOYEES
Oracle ServerOracle Server
Querying a View
USER_VIEWSUSER_VIEWS
EMPVU80EMPVU80
SELECT employee_id,
last_name, salary
FROM employees
WHERE department_id=80;
iSQL*Plus
SELECT *
FROM empvu80;
EMPLOYEES
Oracle ServerOracle Server
11-12 Copyright © Oracle Corporation, 2001. All rights reserved.
Modifying a View
•Modify the EMPVU80 view by using CREATE OR
REPLACE VIEW clause. Add an alias for each
column name.
•Column aliases in the CREATE VIEW clause are
listed in the same order as the columns in the
subquery.
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;
View created.View created.
Modifying a View
•Modify the EMPVU80 view by using CREATE OR
REPLACE VIEW clause. Add an alias for each
column name.
•Column aliases in the CREATE VIEW clause are
listed in the same order as the columns in the
subquery.
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;
View created.View created.
11-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Creating a Complex View
Create a complex view that contains group functions
to display values from two tables.
CREATE VIEWdept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
View created.View created.
Creating a Complex View
Create a complex view that contains group functions
to display values from two tables.
CREATE VIEWdept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
View created.View created.
11-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Rules for Performing
DML Operations on a View
•You can 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
Rules for Performing
DML Operations on a View
•You can 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
11-15 Copyright © Oracle Corporation, 2001. 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
•Columns defined by expressions
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
•Columns defined by expressions
11-16 Copyright © Oracle Corporation, 2001. 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 in the base tables that are not
selected by the view
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 in the base tables that are not
selected by the view
11-17 Copyright © Oracle Corporation, 2001. All rights reserved.
•You can ensure that DML operations performed on
the view stay within the domain of the view by
using the WITH CHECK OPTION clause.
•Any attempt to change the department number for
any row in the view fails because it violates the
WITH CHECK OPTION constraint.
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.View created.
Using the WITH CHECK OPTION Clause
•You can ensure that DML operations performed on
the view stay within the domain of the view by
using the WITH CHECK OPTION clause.
•Any attempt to change the department number for
any row in the view fails because it violates the
WITH CHECK OPTION constraint.
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.View created.
Using the WITH CHECK OPTION Clause
11-18 Copyright © Oracle Corporation, 2001. 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 on any row in the
view results in an Oracle server error.
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 on any row in the
view results in an Oracle server error.
11-19 Copyright © Oracle Corporation, 2001. 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;
View created.View created.
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;
View created.View created.
11-20 Copyright © Oracle Corporation, 2001. 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 empvu80;
View dropped.View dropped.
DROP VIEW view;
DROP VIEW view;
Removing a View
You can remove a view without losing data because a
view is based on underlying tables in the database.
DROP VIEW empvu80;
View dropped.View dropped.
DROP VIEW view;
DROP VIEW view;
11-21 Copyright © Oracle Corporation, 2001. All rights reserved.
Inline Views
•An inline view is a subquery with an alias (or
correlation name) that you can use within a SQL
statement.
•A named subquery in the FROM clause of the main
query is an example of an inline view.
•An inline view is not a schema object.
Inline Views
•An inline view is a subquery with an alias (or
correlation name) that you can use within a SQL
statement.
•A named subquery in the FROM clause of the main
query is an example of an inline view.
•An inline view is not a schema object.
11-22 Copyright © Oracle Corporation, 2001. All rights reserved.
Top-N Analysis
•Top-N queries ask for the n largest or smallest
values of a column. For example:
–What are the ten best selling products?
–What are the ten worst selling products?
•Both largest values and smallest values sets are
considered Top-N queries.
Top-N Analysis
•Top-N queries ask for the n largest or smallest
values of a column. For example:
–What are the ten best selling products?
–What are the ten worst selling products?
•Both largest values and smallest values sets are
considered Top-N queries.
11-23 Copyright © Oracle Corporation, 2001. All rights reserved.
Performing Top-N Analysis
The high-level structure of a Top-N analysis
query is:
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
Performing Top-N Analysis
The high-level structure of a Top-N analysis
query is:
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
11-24 Copyright © Oracle Corporation, 2001. All rights reserved.
Example of Top-N Analysis
To display the top three earner names and salaries
from the EMPLOYEES table:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
31 2
1 2 3
Example of Top-N Analysis
To display the top three earner names and salaries
from the EMPLOYEES table:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
31 2
1 2 3
11-25 Copyright © Oracle Corporation, 2001. All rights reserved.
Summary
In this lesson, you should have learned that a view is
derived from data in other tables or views and
provides the following advantages:
•Restricts database access
•Simplifies queries
•Provides data independence
•Provides multiple views of the same data
•Can be dropped without removing the underlying
data
•An inline view is a subquery with an alias name.
•Top-N analysis can be done using subqueries and
outer queries.
Summary
In this lesson, you should have learned that a view is
derived from data in other tables or views and
provides the following advantages:
•Restricts database access
•Simplifies queries
•Provides data independence
•Provides multiple views of the same data
•Can be dropped without removing the underlying
data
•An inline view is a subquery with an alias name.
•Top-N analysis can be done using subqueries and
outer queries.
11-26 Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 11 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
•Displaying view definitions
•Removing views
Practice 11 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
•Displaying view definitions
•Removing views
Categories
GeneralDownload
Download Slideshow Get the original presentation fileQuick Actions
Statistics
- Views 2,076
- Slides 26
- Favorites 1
- Age 3621 days
Related Slideshows
22
Pray For The Peace Of Jerusalem and You Will Prosper
RodolfoMoralesMarcuc
30 views
26
Don_t_Waste_Your_Life_God.....powerpoint
chalobrido8
32 views
31
VILLASUR_FACTORS_TO_CONSIDER_IN_PLATING_SALAD_10-13.pdf
JaiJai148317
30 views
14
Fertility awareness methods for women in the society
Isaiah47
28 views
35
Chapter 5 Arithmetic Functions Computer Organisation and Architecture
RitikSharma297999
26 views
5
syakira bhasa inggris (1) (1).pptx.......
ourcommunity56
28 views