Oracle SQL & PL/SQL Fundamentals Training Course

Course Contents - DAY 1

Course Introduction

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

Session 1: RELATIONAL DATABASE CONCEPTS

• • What is an Oracle Database
• • Relational Database Structures
• • Tables,Rows and Columns
• • Indexes,Primary Keys and Foreign Keys
• • Data Types
• • The Data Dictionary

Session 2: USING SQL*PLUS

• • What is SQL*Plus
• • Getting Started
• • Entering and Executing SQL Statements
• • Editing SQL Statements
• • Creating,Editing and Executing SQL Files

Session 3: USING SQL DEVELOPER

• • What is Oracle SQL Developer
• • Starting SQL Developer
• • Configure a Connection
• • Navigation Tabs
• • SQL Worksheet

Session 4: RETRIEVING DATA WITH THE SELECT STATEMENT

• • The SELECT Statement
• • The SELECT and FROM Clauses
• • Conditions and the WHERE Clause
• • Other Conditional Operators
• • Logical Operators
• • The ORDER BY Clause
• • Column Aliases
• • Arithmetic Expressions
• • Precedence of Operators

Session 5: AGGREGATE FUNCTIONS

• • Overview of Built In Aggregate Functions
• • The GROUP BY Clause
• • The HAVING Clause

Oracle SQL & PL/SQL Fundamentals Training Course

Course Contents - DAY 2

Session 6: JOINING TABLES

• • Overview of Table Joins
• • Inner Joins
• • Table Aliases
• • Outer Joins
• • Self Joins
• • ANSI Standard Joins

Session 7: NUMERIC,CHARACTER AND DATE FUNCTIONS

• • Function Types
• • Using the Table dual to try out Functions
• • Numeric Functions
• • Character Functions
• • String Concatenation
• • Date Arithmetic and Date Functions

Session 8: CONVERSION AND MISCELLANEOUS FUNCTIONS

• • Conversion Functions
• • The NVL and NVL2 Functions
• • The DECODE Function
• • CASE Expressions
• • The COALESCE and NULLIF Functions

Session 9: SQL*PLUS PARAMETERS

• • Command Line Substitution Parameters
• • The Accept Command
• • The Define and Undefine Commands

Session 10: USING SUBQUERIES

• • Overview of Subqueries
• • Use a Subquery as an Alternative to Join
• • Handle Multiple Records in Subqueries
• • Subquery in a Having Clause
• • Anti-Join
• • In-Line Views
• • Top-N Queries
• • Complex Subqueries
• • Multi Column Subqueries
• • Correlated Subqueries
• • Subquery Rules
• • Combining Unrelated Aggregates
• • Using the ANY,ALL and SOME Operators

Oracle SQL & PL/SQL Fundamentals Training Course

Course Contents - DAY 3

Session 11: MANAGING DATA

• • Inserting Rows
• • Updating Rows
• • Deleting Rows
• • Verifying Updates
• • Transaction Control
• • Commit and Rollback
• • Savepoints
• • Commits and Constraints
• • Amending Data in SQL Developer

Session 12: MANAGING TABLES

• • Creating Tables
• • Specifying Constraints
• • Altering Tables,Columns and Constraints
• • Dropping Tables,Columns and Constraints
• • Copying Tables

Session 13: MANAGING INDEXES AND VIEWS

• • Creating Indexes
• • Dropping Indexes
• • Listing Indexes
• • Creating and Using Views
• • Dropping Views
• • Listing Views

Session 14: MANAGING SEQUENCES AND SYNONYMS

• • Create a Sequence
• • View Sequence Details
• • Create a Synonym
• • List Synonyms

Oracle SQL & PL/SQL Fundamentals Training Course

Course Contents - DAY 4

Session 15: PL/SQL FUNDAMENTALS

• • What is PL/SQL?
• • Basic Elements
• • Variables and Constants
• • Data Types
• • Initialising Variables and Assigning Values
• • Using SQL Statements in Code
• • Generating Output to SQL or SQL Developer

Session 16: PROGRAM LOGIC

• • IF THEN ELSIF ELSE Statements
• • CASE Statements
• • The Basic Loop Construct
• • WHILE and FOR Loops
• • Nested and Labelled Loops
• • The GOTO Statement
• • The CONTINUE Statement

Session 17: USING CURSORS

• • What is a Cursor?
• • Implicit and Explicit Cursors
• • Cursor Operations
• • Declaring,Opening and Closing Cursors
• • Fetching Rows
• • Status Checking
• • Using Cursors FOR UPDATE
• • The Cursor FOR Loop
• • Parameterised Cursors

Oracle SQL & PL/SQL Fundamentals Training Course

Course Contents - DAY 5

Session 18: EXCEPTIONS AND NESTED BLOCKS

• • The EXCEPTION Section
• • Types of Exception
• • Handling Named System-Raised Exceptions
• • Handling Un-named System-Raised Exceptions
• • User-Declared Exceptions and Application Errors
• • WHEN OTHERS THEN NULL
• • Nested and Labelled Blocks
• • Propagation of Exceptions
• • Scope of Variables and Cursors
• • Scope of Goto Statements

Session 19: PL/SQL RECORDS AND INDEX-BY TABLES

• • Declaring Record Types
• • Handling PL/SQL Records
• • Nested Records
• • Declaring PL/SQL Index-By Tables or Associative Arrays
• • PL/SQL Table Built-in Functions
• • Manipulating PL/SQL Tables or Associative Arrays