Oracle SQLPLUS - (SQL / PLSQL)

By

This course includes classroom labs for live hands-on training

COURSE DURATION : 5 days

COURSE CODE :

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

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

COURSE 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