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 Wikipedia: View (SQL).
2. Read Wikipedia: 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

Activities

Schema Diagrams

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

HR Schema Objects

Views

1. Create simple and complex views and retrieve data from views.
   1. 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_%';
   2. Select all records from sales employees to test the view.
   3. 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%';
   4. Select all records from sales departments to test the view.
   5. 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

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

Indexes

1. Create and maintain indexes.
   1. 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)
);
   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);
   3. 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

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

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 <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

References

← Data Definition Language

Oracle SQL Fundamentals