Oracle Database SQL Workshop II

This course is intended for people in the roles of:

• Developer
• Implementer
• Systems Administrator.

Completion of this course will enable particicpants to:

• Grant and revoke privileges and roles.
• Manage schema objects and data dictionary views.
• Write efficient and complex subqueries.
• Use the in-built functions supporting the usage of regular expressions and time zone.

Required prerequisites to undertake this course are:

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

• Expert Oracle instructors will teach you how to write more efficient queries using the functions that support timezone and regular expressions.
• Introduction
• Course Objectives, Course Agenda and Appendixes Used in this Course
• Provide an insight of the development environments
• Tables used in the Course
• Review of the SQL Concepts
• Introduction to Data Dictionary Views
• Introduction to Data Dictionary
• Describe the Data Dictionary Structure
• Using the Data Dictionary Views
• Querying the Data Dictionary Views
• Creating Sequences, Synonyms, Indexes
• Overview of sequences
• Overview of synonyms
• Overview of indexes
• Creating Views
• Overview of views
• Managing Schema Objects
• Managing constraints
• Creating and using temporary tables
• Creating and using external tables
• Retrieving Data by Using Subqueries
• Retrieving Data by Using a Subquery as Source
• Working with Multiple-Column subqueries
• Using Scalar subqueries in SQL
• Correlated Subqueries
• Working with the WITH clause
• Manipulating Data by Using Subqueries
• Using Subqueries to Manipulate Data
• Inserting by Using a Subquery as a Target
• Using the WITH CHECK OPTION Keyword on DML Statements
• Using Correlated Subqueries to Update and Delete rows
• Controlling User Access
• System privileges
• Creating a role
• Object privileges
• Revoking object privileges
• Manipulating Data
• Overview of the Explicit Default Feature
• Using multitable INSERTs
• Using the MERGE statement
• Performing flashback operations
• Tracking Changes in Data
• Managing Data in Different Time Zones
• Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
• Working with INTERVAL data types
• Using Datetime functions in queries

Overview of views Managing Schema Objects