Oracle SQL Fundamentals/Other Schema Objects

This lesson introduces other schema objects.

Objectives and Skills

edit

Objectives and skills for the other schema objects portion of Oracle SQL Fundamentals I certification include:[1]

  • 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

edit
  1. Read Wikipedia: View (SQL).
  2. Read Wikipedia: Database index.

Multimedia

edit
  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

edit

Schema Diagrams

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

HR Schema Objects

edit

Views

edit
  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

edit
  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

edit
  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

edit
  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

edit
  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

edit
  • 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.[2]
  • Views can provide advantages over tables:[3]
    • 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.[4]
  • 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.[5]
  • A synonym is an alternative name for a database object.[6]
  • CREATE VIEW syntax: CREATE VIEW <name> AS <SELECT ...>;[7]
  • CREATE SEQUENCE syntax: CREATE SEQUENCE <name> START WITH <value>;[8]
  • CREATE INDEX syntax: CREATE UNIQUE INDEX <name> ON <table(column(s)>);[9]
  • CREATE SYNONYM syntax: CREATE SYNONYM <name1> FOR <name2>;[10]

Assessments

edit

See Also

edit

References

edit