Focus Thread Consulting

We are glad to announce our new online course on Oracle SQL-PL/SQL & Advanced SQL Tuning.

Our instructor lead trainings are interactive with various demos / Q&A Sessions. We use industry leading Conferencing Software [with integrated VOIP] to provide trainings.

This is a hands-on lab-intensive course conducted on weekends, in addition to which there will be doubt removal sessions organized during weekdays. 

Batch Commencing on: 13 March 2010

Course Fee: USD 299/- [Introductory Price]

Training Duration: 4 Days

Training Schedule: 13, 14, 20 & 21 March 2010

See the Detailed Course Breakdown Below By Scrolling Down

Day 1

Retrieving Data Using the SQL SELECT Statement
• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select All Columns
• Select Specific Columns
• Use Column Heading Defaults
• Use Arithmetic Operators
• Understand Operator Precedence
• Learn the DESCRIBE command to display the table structure

Restricting and Sorting Data
• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause sort the output of a SELECT statement
• Sort output in descending and ascending order

Using Single-Row Functions to Customize Output
• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions
• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions
• Use the aggregation functions in SELECT statements to produce meaningful reports
• Create queries that divide the data in groups by using the GROUP BY clause
• Create queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables
• Write SELECT statements to access data from more than one table
• View data that generally does not meet a join condition by using outer joins
• Join a table by using a self join

Using Sub-queries to Solve Queries
• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

Day 2

Using the SET Operators
• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned when using the SET operators

Manipulating Data
• Describe each DML statement
• Insert rows into a table with the INSERT statement
• Use the UPDATE statement to change rows in a table
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

Using DDL Statements to Create and Manage Tables
• Categorize the main database objects
• Review the table structure
• List the data types available for columns
• Create a simple table
• Decipher how constraints can be created at table creation
• Describe how schema objects work

Creating Other Schema Objects
• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Controlling User Access
• Differentiate system privileges from object privileges
• Grant privileges on tables
• Grant roles
• Distinguish between privileges and roles

Managing Schema Objects
• Add constraints
• Create indexes
• Create indexes using the CREATE TABLE statement
• Create function-based indexes
• Drop columns and set column UNUSED
• Create and use external tables

Retrieving Data Using Sub-queries
• Write a multiple-column sub-query
• Solve problems with correlated sub-queries
• Update and delete rows using correlated sub-queries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause

Day 3

Introduction to PL/SQL
• PL/SQL Overview
• Benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL blocks
• Creating and Executing a Simple Anonymous Block
• Generating Output from a PL/SQL Block

Declaring PL/SQL Identifiers
• Different Types of Identifiers in a PL/SQL subprogram
• Using the Declarative Section to Define Identifiers
• Storing Data in Variables
• Scalar Data Types
• %TYPE Attribute
• Bind Variables
• Using Sequences in PL/SQL Expressions

Writing Executable Statements
• Describing Basic PL/SQL Block Syntax Guidelines
• Commenting Code
• SQL Functions in PL/SQL
• Data Type Conversion
• Nested Blocks
• Operators in PL/SQL

Interacting with the Oracle Server
• Including SELECT Statements in PL/SQL to Retrieve data
• Retrieving Data in PL/SQL with the SELECT Statement
• The SQL Cursor concept
• Avoiding Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulating Data in the Server Using PL/SQL
• Understanding the SQL Cursor concept
• Using SQL Cursor Attributes to Obtain Feedback on DML
• Saving and Discarding Transactions

Writing Control Structures
• Conditional processing Using IF Statements
• Conditional processing Using CASE Statements
• Simple Loop Statement
• While Loop Statement
• For Loop Statement
• The Continue Statement

Working with Composite Data Types
• Using PL/SQL Records to Hold Multiple Values of Different Types
• Using the %ROWTYPE Attribute
• Inserting and Updating with PL/SQL Records
• INDEX BY Tables to Hold Multiple Values of the Same Data Type
• INDEX BY Table Methods
• INDEX BY Table of Records

Using Explicit Cursors
• Understanding Explicit Cursors
• Declaring the Cursor
• Opening the Cursor
• Fetching data from the Cursor
• Closing the Cursor
• Cursor FOR loop
• The %NOTFOUND and %ROWCOUNT Attributes
• FOR UPDATE Clause and WHERE CURRENT Clause

Day 4

Handling Exceptions
• Understanding Exceptions
• Handling Exceptions with PL/SQL
• Trapping Predefined Oracle Server Errors
• Trapping Non-Predefined Oracle Server Errors
• Trapping User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

Creating Stored Procedures
• Creating a Modularize and Layered Subprogram Design
• Modularizing Development With PL/SQL Blocks
• Understanding the PL/SQL Execution Environment
• The Benefits of Using PL/SQL Subprograms
• The Differences Between Anonymous Blocks and Subprograms
• Creating, Calling, and Removing Stored Procedures Using the CREATE Command and SQL Developer
• Using Procedures Parameters and Parameters Modes
• Viewing Procedures Information Using the Data Dictionary Views and SQL Developer

Creating Stored Functions and Debugging Subprograms
• Creating, Calling, and Removing a Stored Function Using the CREATE Command and SQL Developer
• Identifying the Advantages of Using Stored Functions in SQL Statements
• Identify the steps to create a stored function
• Using User-Defined Functions in SQL Statements
• Restrictions When Calling Functions from SQL statements
• Controlling Side Effects When Calling Functions from SQL Expressions
• Viewing Functions Information
• Debugging Functions and Procedures

Creating Packages
• Listing the Advantages of Packages
• Describing Packages
• The Components of a Package
• Developing a Package
• The Visibility of a Package’s Components
• Creating the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
• Invoking the Package Constructs
• Viewing the PL/SQL Source Code Using the Data Dictionary

Working With Packages
• Overloading Subprograms in PL/SQL
• Using the STANDARD Package
• Using Forward Declarations to Solve Illegal Procedure Reference
• Using Package Functions in SQL and Restrictions
• Persistent State of Packages
• Persistent State of a Package Cursor
• Controlling Side Effects of PL/SQL Subprograms
• Using PL/SQL Tables of Records in Packages

Creating Triggers
• Working With Triggers
• Identifying the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Creating DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
• Identifying the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Creating Instead of and Disabled Triggers
• Managing, Testing, and Removing Triggers

SQL Tuning Fundamentals