Oracle Fundamentals

Prerequisites:

An understanding of databases and exposure to information technology in general would be useful

Course Description:

This course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions, SQL*Plus parameters and the basic table and view handling statements are introduced.

The course also provides practical experience in using Oracle's PL/SQL programming language to implement conditional execution, loop control, cursor handling and exception handling.

The delegate will practise:

• Using SQL*Plus and SQL Developer
• Creating SQL statements to query database tables
• Using standard aggregate functions and related SELECT statement clauses
• Implementing extended SQL functions
• Using SQL*Plus run time parameters
• Creating and altering tables and views
• Inserting, updating and deleting rows in database tables
• Retrieving information from the data dictionary
• Writing PL/SQL programs
• Declaring and using variables and constants
• Using the IF... THEN ... ELSE statement
• Implementing CASE expressions
• Implementing basic, WHILE and FOR loop constructs
• Handling implicit and explicit cursors
• Implementing the cursor FOR loop
• Handling system raised exceptions
• Defining and raising user defined exceptions
• Declaring and using record types and PL/SQL tables

This course includes the following modules:

Relational Database Concepts

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

Using SQL*Plus

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

Using SQL Developer

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

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

Aggregate Functions

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

Joining Tables

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

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

Conversion and Miscellaneous Functions

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

SQL*Plus Parameters

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

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

Managing Data

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

Managing Tables

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

Managing Indexes and Views

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

Managing Sequences and Synonyms

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

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

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

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

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

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