Oracle Database 12c R2: SQL Workshop II Ed 2

Suggested Prerequisite

• Basic Knowledge of SQL
• Familiarity with SQL Developer, SQL*Plus

Required Prerequisite

• Oracle Database: SQL Workshop I Ed 2

Audience

• Functional Implementer
• PL/SQL Developer
• Application Developers
• Database Administrators
• Database Administrators
• System Analysts
• System Analysts
• System Analysts
• Forms Developer
• Forms Developer

Course outline

1. Introduction

• Course Objectives, Course Agenda and Appendixes Used in this Course
• Review of the SQL Concepts
• Provide an insight of the development environments
• Tables used in the Course

2. Introduction to Data Dictionary Views

• Introduction to Data Dictionary
• Querying the Data Dictionary Views
• Using the Data Dictionary views
• Describe the Data Dictionary Structure

3. Creating Sequences, Synonyms, Indexes

• Overview of sequences
• Overview of indexes
• Overview of synonyms

4. Creating Views

• Overview of views

5. Managing Schema Objects

• Creating and using temporary tables
• Creating and using external tables
• Managing constraints

6. Retrieving Data by Using Subqueries

• Working with the WITH clause
• Retrieving Data by Using a Subquery as Source
• Using Scalar subqueries in SQL
• Working with Multiple-Column subqueries
• Correlated Subqueries

7. Manipulating Data by Using Subqueries

• Inserting by Using a Subquery as a Target
• Using Subqueries to Manipulate Data
• Using Correlated Subqueries to Update and Delete rows
• Using the WITH CHECK OPTION Keyword on DML Statements

8. Controlling User Access

• Object privileges
• Creating a role
• Revoking object privileges
• System privileges

9. Manipulating Data Using Advanced Queries

• Using multitable INSERTs
• Overview of the Explicit Default Feature
• Tracking Changes in Data
• Performing flashback operations
• Using the MERGE statement

10. Managing Data in Different Time Zones

• Using Datetime functions in queries
• Working with INTERVAL data types
• Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP