Pl/Sql

Database Management Systems 2

Lesson 1

Creating Other Schema Objects

Objectives

• After completing this lesson, you should

be able to do the following:

– Create simple and complex views

– Retrieve data from views

– Create, maintain, and use sequences

– Create and maintain indexes

– Create private and public synonyms

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

What Is a View?

EMPLOYEES table

Advantages of Views

To restrict

data access

To make complex

queries easy

To provide

data

independence

To present

different views of

the same data

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

view

Yes

Not always

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.

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 iSQL*Plus DESCRIBE command:

DESCRIBE empvu80

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...