Oracle Database 11g: SQL Fundamentals II Release 2 Training  

Introduction

• Course Prerequisites
• Course Agenda
• The Human Resources (HR) Schema
• Review of Using Oracle SQL Developer
• SQL Statements in SQL*Plus
• Review of some basic concepts of SQL

Controlling User Access

• System versus Object Privileges
• Creating Users
• Granting System Privileges
• Creating and Granting Privileges to a Role
• Changing Your Password
• Granting Object Privileges
• Passing On Your Privileges
• Revoking Object Privileges

Managing Schema Objects

• Adding, Modifying and Dropping a Column
• Adding, Dropping and Deferring a Constraint
• Enabling and Disabling a Constraint
• Creating and Removing Indexes
• Creating a Function-Based Index
• Performing Flashback Operations
• Creating an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Querying External Tables

Managing Objects with Data Dictionary Views

• The Data Dictionary
• How to Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Querying the dictionary views for constraint information
• Querying the dictionary views for view, sequence, index and synonym information
• Adding a comment to a table
• Querying the dictionary views for comment information

Manipulating Large Data Sets

• Using Subqueries to Manipulate Data
• Retrieving Data Using a Subquery as Source
• Inserting Using a Subquery as a Target
• Using the WITH CHECK OPTION Keyword on DML Statements
• Types of Multitable INSERT Statements
• Using Multitable INSERT Statements
• Merging rows in a table
• Tracking Changes in Data over a period of time

Managing Data in Different Time Zones

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Comparing Date and Time in a Session’s Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Using EXTRACT, TZ_OFFSET and FROM_TZ
• Using TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieving Data Using Subqueries

• Multiple-Column Subqueries
• Pairwise and Nonpairwise Comparison
• Using Scalar Subquery Expressions
• Solving problems with Correlated Subqueries
• Updating and Deleting Rows Using Correlated Subqueries
• Using the EXISTS and NOT EXISTS operators
• Using the WITH clause
• Using the Recursive WITH clause

Regular Expression Support

• Using the Regular Expressions Functions and Conditions in SQL
• Using Meta Characters with Regular Expressions
• Performing a Basic Search using the REGEXP_LIKE function
• Finding patterns using the REGEXP_INSTR function
• Extracting Substrings using the REGEXP_SUBSTR function
• Replacing Patterns Using the REGEXP_REPLACE function
• Using Sub-Expressions with Regular Expression Support
• Using the REGEXP_COUNT function