COURSE OUTLINE

1.    Introduction to Data Dictionary Views

a.    Data Dictionary 

·      Data Dictionary Structure 

·      How to Use the Dictionary Views 

·      USER_OBJECTS and ALL_OBJECTS Views  

·      USER_OBJECTS View  

b.    Table Information 

·      Column Information  

·      Constraint Information  

·      USER_CONSTRAINTS: Example  

·      Querying USER_CONS_COLUMNS  

c.    Adding Comments to a Table  

2.    Creating Sequences, Synonyms, and Indexes

a.    Database Objects 

·      Referencing Another User’s Tables 

·      Sequences  

·      CREATE SEQUENCE Statement: Syntax 

·      Creating a Sequence 

·      NEXTVAL and CURRVAL Pseudocolumns 

·      Using a Sequence 

·      SQL Column defaulting using a Sequence  

·      Caching Sequence Values  

·      Modifying a Sequence  

·      Guidelines for Modifying a Sequence  

·      Sequence Information  

b.    Synonyms  

·      Creating a Synonym for an Object  

·      Creating and Removing Synonyms  

·      Synonym Information  

c.    Indexes  

·      How Are Indexes Created?  

·      Creating an Index  

·      CREATE INDEX with the CREATE TABLE Statement  

·      Function-Based Indexes  

·      Creating Multiple Indexes on the Same Set of Columns  

·      Example of Creating Multiple Indexes on the Same Set Of Columns  

·      Index Creation Guidelines  

·      Index Information  

·      Querying USER_IND_COLUMNS  

·      Removing an Index  

3.    Creating Views

a.    Database Objects  

·      What Is a View?  

·      Advantages of Views  

·      Simple Views and Complex Views  

·      Creating a View  

·      Retrieving Data from a View  

·      Modifying a View  

·      Creating a Complex View  

·      View Information  

·      Rules for Performing DML Operations on a View 

·      Using the WITH CHECK OPTION Clause  

·      Denying DML Operations  

·      Removing a View  

5.    Managing Schema Objects

a.    Adding a Constraint Syntax 

·      Adding a Constraint  

·      Dropping a Constraint 

·      Dropping a CONSTRAINT ONLINE 

·      ON DELETE Clause 

·      Cascading Constraints

·      Renaming Table Columns and Constraints 

·      Disabling Constraints  

·      Enabling Constraints  

·      Deferring Constraints  

·      Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE  

·      DROP TABLE … PURGE  

b.    Temporary Tables  

·      Creating a Temporary Table  

c.    External Tables  

·      Creating a Directory for the External Table  

·      Creating an External Table  

·      Creating an External Table by Using ORACLE_LOADER  

·      Querying External Tables  

6.    Retrieving Data by Using Subqueries

a.    Retrieving Data by Using a Subquery as a Source  

b.    Multiple-Column Subqueries  

·      Column Comparisons  

·      Pairwise Comparison Subquery  

·      Non-pairwise Comparison Subquery  

c.    Scalar Subquery Expressions  

d.    Correlated Subqueries  

·      Using Correlated Subqueries  

e.    Using the EXISTS Operator  

·      Find All Departments That Do Not Have Any Employees  

f.    WITH Clause  

·      Recursive WITH Clause  

7.    Manipulating Data by Using Subqueries

a.    Using Subqueries to Manipulate Data  

b.    Inserting by Using a Subquery as a Target  

c.    Using the WITH CHECK OPTION Keyword on DML Statements  

d.    Correlated UPDATE  

·      Using Correlated UPDATE  

·      Correlated DELETE  

·      Using Correlated DELETE  

8.    Controlling User Access

a.    Controlling User Access  

·      Privileges  

·      System Privileges  

·      Creating Users  

·      User System Privileges  

·      Granting System Privileges  

b.    What Is a Role?  

·      Creating and Granting Privileges to a Role  

·      Changing Your Password  

c.    Object Privileges  

·      Granting Object Privileges  

·      Passing On Your Privileges  

·      Confirming Granted Privileges  

d.    Revoking Object Privileges  

9.    Manipulating Data

a.    Explicit Default Feature: Overview  

·      Using Explicit Default Values  

·      Copying Rows from Another Table  

b.    Multitable INSERT Statements: Overview  

·      Types of Multitable INSERT Statements  

·      Multitable INSERT Statements  

·      Unconditional INSERT ALL  

·      Conditional INSERT ALL  

·      Conditional INSERT FIRST  

·      Pivoting INSERT  

c.    MERGE Statement  

·      MERGE Statement Syntax  

·      Merging Rows

d.    FLASHBACK TABLE Statement  

·      Using the FLASHBACK TABLE Statement  

e.    Tracking Changes in Data  

·      Flashback Version Query

·      VERSIONS BETWEEN Clauses  

10.    Managing Data in Different Time Zones

a.    Time Zones  

·      TIME_ZONE Session Parameter  

·      CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP  

·      Comparing Date and Time in a Session’s Time Zone  

·      DBTIMEZONE and SESSIONTIMEZONE  

·      TIMESTAMP Data Types  

·      TIMESTAMP Fields  

·      Difference Between DATE and TIMESTAMP  

·      Comparing TIMESTAMP Data Types  

b.    INTERVAL Data Types  

·      INTERVAL Fields  

·      INTERVAL YEAR TO MONTH

·      INTERVAL DAY TO SECOND Data Type

c.    EXTRACT  

·      TZ_OFFSET  

·      FROM_TZ  

·      TO_TIMESTAMP  

·      TO_YMINTERVAL  

·      TO_DSINTERVAL  

·      Daylight Saving Time (DST)