INFORMIT logo
InformIT Home About InformIT Oracle Workforce Development Program Cisco Networking Academy Program Microsoft IT Training & Certification Other Intensive Training International IT Certifications
ABOUT
 
 ORACLE 10g
 
  10g DBA Track  
  10g iDS Track  
  10g Java Develop. Track  
  10g App. Server Track  
 
ORACLE 9i
 
  9i DBA Track  
  9i iDS Track  
  9i J2EE Java Develop. Track  
  9i AS Web Adm. Track  
 
Oracle Data Warehouse
 
   
   
     
  ORACLE CERTIFICATION  
  ORACLE UNIVERSITY  
     
   
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
 
Introduction to Oracle 9i : SQL

This course offers participants an extensive introduction to data server technology. The course covers the concepts of both relational and object relational databases and the powerful SQL language.

Participants are taught to create and maintain database objects and to store, retrieve, and manipulate data. Students learn to retrieve data by using advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. They also learn to write SQL and SQL*Plus script files using the iSQL*Plus tool to generate report-like output. Demonstrations and hands-on practice reinforce the fundamental concepts.

This course helps Participants prepare to successfully pass the Oracle certification exam #1z0-007 to become an Oracle Certified Associte (OCA).

 

Course schedule

Duration: 30 hours
To view the schedule for this class, click here.

Who should attend

Everyone who are, or will be, working with Oracle 9i

Prerequisites

None, an Introduction to Database will be provided.

Course Outline

Introduction
  • Describing the Life Cycle Development Phases
  • Defining a Relational Database
  • Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
  • Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
  • Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
  • Describing How SQL Is Used in the Oracle Product Set

Writing a Basic SQL Statement

  • Describing the SQL Select Capabilities
  • Executing a Basic Select Statement with the Mandatory Clauses
  • Differentiating Between SQL and iSQL*Plus Commands

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 Joins
  • Writing Joins Using the Tips Provided

Aggregating Data by Using Group Functions

  • Identifying the Different Group Functions Available
  • Explaining the Use of Group Functions
  • Grouping Data by Using the GROUP BY Clause

Writing Subqueries

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

Producing Readable Output with iSQL*Plus

  • Producing Queries That Require an Input Variable
  • Customizing the iSQL*Plus Environment
  • Producing More Readable Output
  • Creating and Executing Script Files

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
  • Describing the Oracle Data Types
  • Altering Table Definitions
  • Dropping, Renaming, and Truncating Tables

Including Constraints

  • Describing Constraints
  • Creating and Maintaining Constraints

Creating Views

  • Describing Views and Their Uses
  • Creating a View
  • Retrieving Data by Means of 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

Controlling User Access

  • Understanding the Concepts of Users, Roles, and Privileges
  • Granting and Revoking Object Privileges
  • Creating Roles and Granting Privileges to Roles
  • Creating Synonyms for Ease of Table Access

SQL Workshop

  • Applying Techniques Learned in This Course
  • Preparing for Future Oracle Courses

Using Set Operators

  • Describing the Set Operators
  • Obeying the Set Operators Rules and Guidelines
  • Using a Set Operator to Combine Multiple Queries into a Single Subquery
  • Controlling the Order of Rows Returned

Oracle 9i Datetime Functions

  • Using DATETIME Functions
  • Using the NVL2 Function to Handle NULL Values

Enhancements to the GROUP BY Clause

  • Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
  • Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
  • Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
  • Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
  • Using the WITH Clause

Advanced Subqueries

  • Multiple-Column Subqueries
  • Writing a Subquery in the FROM Clause
  • Writing and describing Correlated Subquery
  • Using EXISTS and NOT EXISTS Operators
  • Updating and Deleting Rows Using Correlated Subqueries
  • Using Scalar Subqueries in SQL

Hierarchical Retrieval

  • Discussing the Benefits of the Hierarchical Query
  • Ordering the Rows Retrieved by a Query in a Hierarchical Manner
  • Formatting Hierarchical Data so That It Is Easy to Read
  • Excluding Branches from the Tree Structure

Oracle 9i Extensions to DML and DDL Statements

  • Discussing Multitable Inserts
  • Creating and Using External Tables
  • Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint
 

 

 

 
 


INFORMIT - Skill Development Center
Jl. Purnawarman 9c, Bandung 40117- Phone (022) 4224 168 / 4223 168
Fax. (022) 4231 168 - E-mail: info@informit.tc

 
  Home | About Us | Oracle | Cisco | Microsoft | Other Training | Certification | Training Calendar  
© 2006, InformIT Ganesa Pratama. All Right Reserved