Jump to content

Oracle SQL Fundamentals/Other Schema Objects

From Wikiversity

This lesson introduces other schema objects.

Objectives and Skills

[edit | edit source]

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 | edit source]
  1. Read Wikipedia: View (SQL).
  2. Read Wikipedia: Database index.

Multimedia

[edit | edit source]
  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 | edit source]

Schema Diagrams

[edit | edit source]
  1. Review HR and OE schema diagrams.
    1. Review the Oracle: HR and OE schema diagrams.

HR Schema Objects

[edit | edit source]

Views

[edit | edit source]
  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 | edit source]
  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 | edit source]
  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 | edit source]
  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 | edit source]
  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 | edit source]
  • 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 | edit source]

See Also

[edit | edit source]

References

[edit | edit source]