Oracle SQL Fundamentals/Other Schema Objects
This lesson introduces other schema objects.
Objectives and Skills
editObjectives 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- Read Wikipedia: View (SQL).
- Read Wikipedia: Database index.
Multimedia
editActivities
editSchema Diagrams
edit- Review HR and OE schema diagrams.
- Review the Oracle: HR and OE schema diagrams.
HR Schema Objects
editViews
edit- 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;
- Create a simple view of sales employees by job description using the following query:
Sequences
edit- 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;
- Create a sequence for region IDs using the following query:
Indexes
edit- 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);
- Create an equipment table using the following query:
Synonyms
edit- 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;
- Create a private synonym for the equipment table using the following query:
OE Schema Objects
edit- 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
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- 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
editReferences
edit