# Oracle SQL Fundamentals/Other Schema Objects

This lesson introduces other schema objects.

## Objectives and Skills

Objectives and skills for the other schema objects portion of Oracle SQL Fundamentals I certification include:
* Creating Other Schema Objects
** Create simple and complex views
** Retrieve data from views
** Create, maintain, and use sequences
** Create and maintain indexes
** Create private and public synonyms

## Readings

* Read Wikipedia: View (SQL).
* Read Wikipedia: Database index.

## Multimedia

* YouTube: Oracle SQL Tutorial - Creating a view
* YouTube: Oracle SQL Tutorial Creating a sequence
* YouTube: Oracle SQL Tutorial - Creating an Index
* YouTube: Oracle SQL Tutorial Creating a synonym

## Activities
### Schema Diagrams

* Review HR and OE schema diagrams.
** Review the Oracle: HR and OE schema diagrams.

### HR Schema Objects
#### Views

* Create simple and complex views and retrieve data from views.
** Create a simple view of sales employees by job description using the following query: CREATE VIEW SALES_EMPLOYEES AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER FROM EMPLOYEES WHERE JOB_ID LIKE 'SA_%';
** Select all records from sales employees to test the view.
** Create a simple view of sales employees by department using the following query: CREATE VIEW SALES_DEPARTMENTS AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID WHERE LOWER(DEPARTMENT_NAME) LIKE '%sales%';
** Select all records from sales departments to test the view.
** Use the following query to identify employees with a sales job description who aren't in a sales department: SELECT E.EMPLOYEE_ID AS SALES_EMPLOYEES_ID, D.EMPLOYEE_ID AS SALES_DEPARTMENTS_ID FROM SALES_EMPLOYEES E FULL OUTER JOIN SALES_DEPARTMENTS D ON E.EMPLOYEE_ID = D.EMPLOYEE_ID WHERE E.EMPLOYEE_ID IS NULL OR D.EMPLOYEE_ID IS NULL;

#### Sequences

* Create, maintain, and use sequences.
** Create a sequence for region IDs using the following query: CREATE SEQUENCE REGION_ID START WITH 11;
** Test the region sequence using the following query: INSERT INTO REGIONS (REGION_ID, REGION_NAME) (SELECT REGION_ID.NEXTVAL, COUNTRY_NAME FROM COUNTRIES);
** Select all records from regions to verify the results.
** Delete all regions with an ID greater than 10 using the following query: DELETE FROM REGIONS WHERE REGION_ID > 10;
** Run the insert query again and test the results.
** Delete all regions with an ID greater than 10.
** Remove the sequence using the following query: DROP SEQUENCE REGION_ID;

#### Indexes

* Create and maintain indexes.
** Create an equipment table using the following query: CREATE TABLE EQUIPMENT( EQUIPMENT_ID NUMBER NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL, EMPLOYEE_ID NUMBER(6), PURCHASE_DATE DATE, PURCHASE_PRICE NUMBER(7,2) );
** Add unique indexes to the equipment table using the following queries: CREATE UNIQUE INDEX EQUIPMENT_PK ON EQUIPMENT(EQUIPMENT_ID); CREATE UNIQUE INDEX EQUIPMENT_IX ON EQUIPMENT(DESCRIPTION);
** Add constraints to the equipment table using the following queries: ALTER TABLE EQUIPMENT ADD CONSTRAINT EQUIPMENT_PK PRIMARY KEY (EQUIPMENT_ID); ALTER TABLE EQUIPMENT ADD CONSTRAINT EQUIPMENT_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);

#### Synonyms

* Create private and public synonyms.
** Create a private synonym for the equipment table using the following query: CREATE SYNONYM EQUIP FOR EQUIPMENT;
** Describe the EQUIP table using the following query: DESCRIBE EQUIP;
** Remove the private synonym using the following query: DROP SYNONYM EQUIP;

### OE Schema Objects

* Create simple and complex views and retrieve data from views.
* Create, maintain, and use sequences.
* Create and maintain indexes.
* Create private and public synonyms.

## Lesson Summary

* A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.
* Views can provide advantages over tables:
** Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables.
** Views can join and simplify multiple tables into a single virtual table.
** Views can act as aggregated tables, where the database engine aggregates data and presents the calculated results as part of the data.
** Views can hide the complexity of data.
** Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
* A sequence is a database object from which multiple users may generate unique integers.
* A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
* A synonym is an alternative name for a database object.
* CREATE VIEW syntax: CREATE VIEW <name> AS <SELECT ...>;
* CREATE SEQUENCE syntax: CREATE SEQUENCE <name> START WITH <value>;
* CREATE INDEX syntax: CREATE UNIQUE INDEX <name> ON <table(column(s)>);
* CREATE SYNONYM syntax: CREATE SYNONYM <name1> FOR <name2>;

## Assessments

* Flashcards: Quizlet: Oracle 1Z0-051 Exam - View Objects
* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Synonym Objects
* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Sequence Objects
* Flashcards: Quizlet: Oracle 1Z0-051 Exam - PL/SQL

## See Also

* Oracle: Database 2 Day Developers Guide - Creating and Managing Schema Objects