2525

INFORMATION TECHNOLOGY EDUCATION DEPARTMENT

ITED233A

(DATABASE MANAGEMENT SYSTEMS 2)

EXERCISE

1

Creating Other Schema Objects

(LESSON 1)

IRASUSTA, JOSHUA P.

B21

NOVEMBER 21, 2015

I. OBJECTIVES

At the end of this exercise, students must be able to:

Cognitive

a. understand the topics they have learned from lesson 1.

.

Psychomotor:

a. create simple and complex views.

b. apply with check option constraint to a view.

c. create and use sequences.

d. create non-unique indexes.

e. create synonyms.

Affective

a. appreciate the concept behind this exercise.

II. BACKGROUND INFORMATION

In order to accomplish this exercise, the student must have a clear understanding of the following topics:

• Create simple and complex views.

• Retrieve data from views.

• Create, maintain, and use sequences.

• Create and maintain indexes.

• Create private and public synonyms.

III. LABORATORY PROCEDURE

Write SQL statement to do the following tasks:

1. The staff in the HR department wants to hide some of the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table. They want the heading for the employee name to be EMPLOYEE.

CREATE OR REPLACE VIEW EMPLOYEE_VU

AS

SELECT EMPLOYEE_ID, FIRST_NAME || ',' || LAST_NAME EMPLOYEE,

DEPARTMENT_ID

FROM EMPLOYEES;

2. Confirm that the view works. Display the contents of the EMPLOYEES_VU view.

SELECT * FROM EMPLOYEE_VU;

[pic] [pic]

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

3. Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers.

SELECT EMPLOYEE,...