Creating, Replacing, and Dropping a View

View All Scripts Login to Run Script

Script Name Creating, Replacing, and Dropping a View
Description A view is a logical representation of a table or combination of tables. In essence, a view is a stored query. This script creates a simple view, replaces the view with a modified view, and drops the view. It also queries the view after it is created and replaced.
Area SQL General
Referenced In Database Administrator's Guide
Contributor Oracle
Created Friday December 09, 2016

Statement 1
Create a view on a subset of data in the hr.departments table. The view shows the departments at headquarters (department_id=1700). The CHECK OPTION creates the view with the constraint (named departments_hq_cnst) so that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select.
Create a View
```
CREATE VIEW departments_hq AS 
      SELECT department_id, department_name, location_id 
      FROM hr.departments 
      WHERE location_id = 1700 
    WITH CHECK OPTION CONSTRAINT departments_hq_cnst
```
```
View created.
```

Statement 2

Show the rows in the view with a SELECT statement.

Query the View

SELECT * FROM departments_hq


DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
10 Administration 1700
30 Purchasing 1700
90 Executive 1700
100 Finance 1700
110 Accounting 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700

21 rows selected.

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
10	Administration	1700
30	Purchasing	1700
90	Executive	1700
100	Finance	1700
110	Accounting	1700
120	Treasury	1700
130	Corporate Tax	1700
140	Control And Credit	1700
150	Shareholder Services	1700
160	Benefits	1700
170	Manufacturing	1700
180	Construction	1700
190	Contracting	1700
200	Operations	1700
210	IT Support	1700
220	NOC	1700
230	IT Helpdesk	1700
240	Government Sales	1700
250	Retail Sales	1700
260	Recruiting	1700
270	Payroll	1700

Statement 3

Replace the view to show the manager_id values instead of the location_id values.

Replace the View with an Modified Query

CREATE OR REPLACE VIEW departments_hq AS 
      SELECT department_id, department_name, manager_id 
      FROM hr.departments 
      WHERE location_id = 1700 
    WITH CHECK OPTION CONSTRAINT departments_hq_cnst

View created.

Statement 4

Show the rows in the view after it was replaced. Note the differences in the output.

Query the View

SELECT * FROM departments_hq


DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
10 Administration 200
30 Purchasing 114
90 Executive 100
100 Finance 108
110 Accounting 205
120 Treasury  - 
130 Corporate Tax  - 
140 Control And Credit  - 
150 Shareholder Services  - 
160 Benefits  - 
170 Manufacturing  - 
180 Construction  - 
190 Contracting  - 
200 Operations  - 
210 IT Support  - 
220 NOC  - 
230 IT Helpdesk  - 
240 Government Sales  - 
250 Retail Sales  - 
260 Recruiting  - 
270 Payroll  - 

21 rows selected.

Statement 5
Drop the view.
Drop the View
```
DROP VIEW departments_hq
```
```
View dropped.
```

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID
10	Administration	200
30	Purchasing	114
90	Executive	100
100	Finance	108
110	Accounting	205
120	Treasury	-
130	Corporate Tax	-
140	Control And Credit	-
150	Shareholder Services	-
160	Benefits	-
170	Manufacturing	-
180	Construction	-
190	Contracting	-
200	Operations	-
210	IT Support	-
220	NOC	-
230	IT Helpdesk	-
240	Government Sales	-
250	Retail Sales	-
260	Recruiting	-
270	Payroll	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database