ORACLE9i SQL/PLSQL
 
ORACLE9i SQL

Course duration(in hrs): 28

Course Objectives:

  1. Use iSQL*Plus to format reports

  2. Use basic iSQL*Plus commands

  3. Retrieve, insert, update, and delete data

  4. Describe relational and object relational database concepts

  5. Create and maintain database objects

  6. Control transactions

  7. Control data and user access
 
Course Topics
 
Introduction

  1. Describing the Life Cycle Development Phases

  2. Defining a Database

  3. Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database

  4. Describing How a Relational Database Management System (RDBMS) Is Used to Manage relational database

  5. Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)

  6. Describing How SQL Is Used in the Oracle Product Set
Writing a Basic SQL Statement

  1. Describing the SQL Select Capabilities

  2. Executing a Basic Select Statement with the Mandatory Clauses

  3. Differentiating Between SQL and iSQL*Plus Commands
Restricting and Sorting Data

  1. Limiting the Rows Retrieved by a Query

  2. Sorting the Rows Retrieved by a Query
Single Row Functions

  1. Describing Various Types of Functions Available in SQL

  2. Using a Variety of Character, Number, and Date Functions in SELECT Statements

  3. Explaining What the Conversion Functions Are and How They Are Used

  4. Using Control Statements
Displaying Data from Multiple Tables

  1. Writing SELECT Statements to Access Data from More Than One Table

  2. Describing the Cartesian Product
Describing and Using the Different Types of Joins

  1. Writing Joins Using the Tips Provided

  2. Aggregating Data by Using Group Functions

  3. Identifying the Different Group Functions Available & Explaining the Use of Group Functions

  4. Grouping Data by Using the GROUP BY Clause

  5. Including or Excluding Grouped Rows by Using the HAVING Clause
Writing Subqueries

  1. Describing the Types of Problems That Subqueries Can Solve

  2. Describing Subqueries

  3. Listing the Types of Subqueries

  4. Writing Single-Row and Multi-Row Subqueries

  5. Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved
Producing Readable Output with iSQL*Plus

  1. Producing Queries That Require an Input Variable

  2. Customizing the iSQL*Plus Environment

  3. Producing More Readable Output

  4. Creating and Executing Script Files
Manipulating Data

  1. Describing Each Data Manipulation Language (DML) Command

  2. Inserting Rows into a Table

  3. Updating Rows in a Table

  4. Deleting Rows from a Table

  5. Merging Rows into a Table

  6. Controlling Transactions

  7. Describing Transaction Processing

  8. Describing Read Consistency and Implicit and Explicit Locking
Creating and Managing Tables

  1. Describing the Main Database Objects

  2. Creating Tables

  3. Describing the Oracle Data Types

  4. Altering Table Definitions

  5. Dropping, Renaming, and Truncating Tables
Including Constraints

  1. Describing Constraints

  2. Creating and Maintaining Constraints

  3. Creating Views

  4. Creating a View

  5. Retrieving Data by Means of a View

  6. Inserting, Updating, and Deleting Data Through Views

  7. Dropping Views

  8. Altering the Definition of a View

  9. Inline Views

  10. Top 'N' Analysis
Other Database Objects

  1. Creating, Maintaining, and Using Sequences

  2. Creating and Maintaining Indexes

  3. Creating Private and Public Synonyms

  4. Controlling User Access

  5. Understanding the Concepts of Users, Roles, and Privileges

  6. Granting and Revoking Object Privileges

  7. Creating Roles and Granting Privileges to Roles

  8. Creating Synonyms for Ease of Table Access.
Date Time functions

  1. Time Zones

  2. Oracle Date Time Support

  3. TZ_OFFSET

  4. Local Timestamp

  5. CURRENT_DATE
Enhancements to the Group By Clause

  1. Cube

  2. Rollup
Advanced Subqueries

  1. Pairwise and non Pairwise comparision subquery

  2. Correlated update

  3. Correlated Delete
 
Hierarchical Retrieval
SQL Tuning Basics

  1. Identifying Performance Problems

  2. Describe the basic steps involved in processing a SQL statement

  3. Identifying the AUTOTRACE Syntax

  4. Identifying Rule-Based Optimization

  5. Identifying Cost-Based Optimization

  6. Indexes and Basic Access Methods

  7. Sorting and Joining
 
 
 
 
 
     
 
Training Portfolio
 
 
Courses
Course material designed by experts
Tutorial
Certification Guidance
 
 
—————————————————
  
 
Course Schedule - July-Sept 2008
 
 
July 2008 Bangalore Region
Mumbai Region
August 2008 Bangalore Region
Mumbai Region
September 2008 Bangalore Region
Mumbai Region
 
     
 
 
     
© 2004, qsoftindia.com, All Rights Reserved
`