Wednesday, September 5, 2007

Working with Oracle 10g indexes, views, and sequences

Working with Oracle 10g indexes, views, and sequences

Learning objective

After completing this topic, you should be able to work with indexes, views, and sequences in an Oracle 10g database.

Exercise overview

In this exercise, you're required to create an index and a view on different tables in the HR schema.

This involves the following tasks:

  • creating an index
  • creating a view

Task 1: Creating an index

Create an index called DEPT_LOCATION_IX on the LOCATION_ID column of the DEPARTMENTS table in the HR schema. Sort the LOCATION_ID column into descending order, ensuring that it will be processed first.

Steps list
Instructions
1. Click the Indexes link
2. Click Create
3. Type DEPT_LOCATION_IX in the Name field
4. Type HR in the Schema field
5. Type HR.DEPARTMENTS in the Table Name field
6. Click Populate Columns
7. Select DESC from the Sorting Order drop-down list of the location_ID column
8. Type 1 in the Order field of the LOCATION_ID column, and click OK

Task 2: Creating a view

Create a view called EMP_SALARY_VIEW in the HR schema using the EMPLOYEES table. The view should display, in order, the employee's ID (employee_id) and salary (salary).

Steps list
Instructions
1. Click the Views link
2. Click Create
3. Type EMP_SALARY_VIEW in the Name field
4. Type HR in the Schema field
5. Type SELECT employee_id, salary FROM employees in the Query Text field

6. Click OK

No comments: