Submitted by: Submitted by chrlsrcls
Views: 10
Words: 584
Pages: 3
Category: English Composition
Date Submitted: 11/22/2015 08:36 AM
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,...