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

 * 1) Read  View (SQL).
 * 2) Read  Database index.

Multimedia

 * 1) YouTube: Oracle SQL Tutorial - Creating a view
 * 2) YouTube: Oracle SQL Tutorial Creating a sequence
 * 3) YouTube: Oracle SQL Tutorial - Creating an Index
 * 4) YouTube: Oracle SQL Tutorial Creating a synonym

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

Views

 * 1) Create simple and complex views and retrieve data from views.
 * 2) Create a simple view of sales employees by job description using the following query:
 * 3) Select all records from sales employees to test the view.
 * 4) Create a simple view of sales employees by department using the following query:
 * 5) Select all records from sales departments to test the view.
 * 6) Use the following query to identify employees with a sales job description who aren't in a sales department:

Sequences

 * 1) Create, maintain, and use sequences.
 * 2) Create a sequence for region IDs using the following query:
 * 3) Test the region sequence using the following query:
 * 4) Select all records from regions to verify the results.
 * 5) Delete all regions with an ID greater than 10 using the following query:
 * 6) Run the insert query again and test the results.
 * 7) Delete all regions with an ID greater than 10.
 * 8) Remove the sequence using the following query:

Indexes

 * 1) Create and maintain indexes.
 * 2) Create an equipment table using the following query:
 * 3) Add unique indexes to the equipment table using the following queries:
 * 4) Add constraints to the equipment table using the following queries:

Synonyms

 * 1) Create private and public synonyms.
 * 2) Create a private synonym for the equipment table using the following query:
 * 3) Describe the EQUIP table using the following query:
 * 4) Remove the private synonym using the following query:

OE Schema Objects

 * 1) Create simple and complex views and retrieve data from views.
 * 2) Create, maintain, and use sequences.
 * 3) Create and maintain indexes.
 * 4) 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 AS ;
 * CREATE SEQUENCE syntax: CREATE SEQUENCE START WITH ;
 * CREATE INDEX syntax: CREATE UNIQUE INDEX ON );
 * CREATE SYNONYM syntax: CREATE SYNONYM FOR ;

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