ORACLE - SQL & PL/SQL

Duration :  5 Days

Introduction

Defining a Database

  • Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database      (Normalization & ER – Diagrams inclusive)
  • Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database

Writing a Basic SQL Statement

  • Describing How SQL Is Used.
  • Describing the SQL Select Capabilities
  • Executing a Basic Select Statement with the Mandatory Clauses

Restricting and Sorting Data

  • Limiting the Rows Retrieved by a Query
  • Sorting the Rows Retrieved by a Query

Single Row Functions

  • Describing Various Types of Functions Available in SQL
  • Using a Variety of Character, Number, and Date Functions in SELECT Statements
  • Explaining What the Conversion Functions Are and How They Are Used
  • Using Control Statements

Displaying Data from Multiple Tables

  • Writing SELECT Statements to Access Data from More Than One Table
  • Describing the Cartesian Product
  • Describing and Using the Different Types of Join
  • Writing Joins Using the Tips Provide

Aggregating Data Using Group Functions

  • Identifying the Different Group Functions Available
  • Explaining the Use of Group Functions
  • Grouping Data Using the GROUP BY Clause
  • Including or Excluding Grouped Rows Using the HAVING Clause

Writing Subqueries

  • Describing the Types of Problems That Subqueries Can Solve
  • Describing What Subqueries Are
  • Listing the Types of Subqueries
  • Writing Single-Row and Multi-Row Subqueries
  • Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved .

Manipulating Data

  • Describing Each Data Manipulation Language (DML) Command
  • Inserting Rows into a Table
  • Updating Rows in a Table
  • Deleting Rows from a Table
  • Merging Rows into a Table
  • Controlling Transactions
  • Describing Transaction Processing
  • Describing Read Consistency, and Implicit and Explicit Locking

Creating and Managing Tables

  • Describing the Main Database Objects
  • Creating Tables
  • Altering Table Definitions
  • Dropping, Renaming, and Truncating Tables

Including Constraints

  • Describing Constraints
  • Creating and Maintaining Constraint

Creating Views

  • Describing Views and Their Uses
  • Creating a View
  • Retrieving Data Via a View
  • Inserting, Updating, and Deleting Data Through Views
  • Dropping Views
  • Altering the Definition of a View
  • Inline Views
  • Top N Analysis

Other Database Objects

  • Creating, Maintaining, and Using Sequences
  • Creating and Maintaining Indexes
  • Creating Private and Public Synonyms

PL/SQL

Introduction

  • Describing PL/SQL
  • Describing the Use of PL/SQL for the Developer and the 

Database Administrator

  • Explaining the Benefits of PL/SQL
  • PL/SQL program constructs
  • PL/SQL anonymous block structure
  • Subprogram block structure
  • Course objectives and overview

Declaring Variables

  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block

Writing Executable Statements

  • Recognizing the Significance of the Executable Section
  • Writing Statements Within the Executable Section
  • Describing the Rules of Nested Blocks
  • Executing and Testing a PL/SQL Block
  • Using Coding Conventions

Interacting with the Oracle Server

  • Writing a Successful SELECT Statement in PL/SQL
  • Declaring the Data type and Size of a PL/SQL Variable Dynamically
  • Writing Data Manipulation Language (DML) Statements in PL/SQL
  • Controlling Transactions in PL/SQL
  • Determining the Outcome of SQL DML Statements

Writing Control Structures

  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables

Working with Composite Data types

  • Creating User-Defined PL/SQL Records
  • Creating a PL/SQL Table
  • Creating a PL/SQL Table of Records
  • Differentiating Among Records, Tables, and Tables of Records

Writing Explicit Cursors

  • Using a PL/SQL Record Variable
  • Distinguishing Between the Implicit and Explicit Cursor
  • Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts

  • Writing a Cursor that Uses Parameters
  • Determining When a FOR UPDATE Clause in a Cursor Is Required

Handling Exceptions

  • Defining PL/SQL Exceptions
  • Recognizing Unhandled Exceptions
  • Listing and Using Different Types of PL/SQL Exception Handlers
  • Trapping Unanticipated Errors
  • Customizing PL/SQL Exception Messages

Creating Procedures

  • Describe the uses of procedures
  • Create procedures
  • Create procedures with arguments
  • Invoke a procedure
  • Remove a procedure

Creating Functions

  • Describe the uses of functions
  • Create a function
  • Invoke a function
  • Remove a function
  • Differentiate between a procedure and a function

Creating Packages

  • Describe packages and list their possible components
  • Create packages that include public and private subprograms as well as global and local variables
  • Invoke objects in a package
  • Remove packages

Creating Database Triggers

  • Describe different types of triggers
  • Describe database triggers and their use
  • Create database triggers
  • Describe database trigger firing rules
  • Drop database trigger