Oracle Introduction for Experienced SQL Users

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION

• Oracle - a brief overview
• Users and roles
• Object naming rules
• Oracle datatypes
• The data dictionary
• Using SQL*Plus
• Entering and executing SQL statements
• Creating, editing and running SQL scripts
• Using SQL Developer
• Starting SQL Developer
• Configuring a Connection
• Navigation Tabs
• SQL Worksheet

Session 2: QUERYING THE DATABASE

• Overview of the basics
• Traditional Oracle v. ANSI-standard table joins
• Set operators
• Using aggregate functions
• Subqueries and in-line views
• Using the WITH clause
• Hierarchical queries

Session 3: NUMERIC AND CHARACTER FUNCTIONS

• Using the table dual
• Numeric functions
• Formatting numeric output
• Character functions and string concatenation

Session 4: DATE AND TIME FUNCTIONS

• Default date and timestamp formats
• Accessing the current date and time
• Date and time arithmetic
• Date functions
• Formatting date and time output
• Conversion functions

Session 5: MISCELLANEOUS FUNCTIONS

• NVL and NVL2
• DECODE
• The simple CASE expression
• The searched CASE expression

Course Contents - DAY 2

Session 6: ENHANCED GROUPING FEATURES

• The ROLLUP extension
• The CUBE extension
• The GROUPING function
• The GROUPING_ID function
• The GROUPING SETS extension

Session 7: TABLES, INDEXES AND VIEWS

• Creating tables
• Defining datatypes
• Specifying constraints
• Table, column and constraint operations
• Recovering dropped tables
• Temporary tables
• External tables
• Index-organized tables
• Handling B-tree indexes
• Creating and using views
• Creating and using sequences

Session 8: MANAGING DATA

• Inserting rows
• Updating rows
• Deleting rows
• Verifying updates
• Multi-table inserts
• Merging rows
• Basic transaction control

Session 9: ACCESS CONTROL

• System privileges
• User accounts and roles
• Granting and revoking object privileges
• Using synonyms

Session 10: THE DATA DICTIONARY

• Data dictionary tables
• Querying the data dictionary
• Useful SQL statements