Institute for Advanced Professional Studies

Database Development with Oracle9i and SQL

Oracle9i and Course Overview:

Oracle9i Database features full XML database functionality with Oracle XML DB, enhancements to the groundbreaking Oracle Real Application Clusters, and self-tuning and self-management capabilities to help improve DBA productivity and efficiency. In addition, the built-in OLAP functionality has been expanded and significant enhancements and optimizations have been made for the Windows and Linux operating systems.

This five day, hands-on course provides a comprehensive introduction to the following Oracle 9i key features and concepts:

  • The Oracle relational database engine management system (RDBMS), a powerful means for organizing, extracting, manipulating, and reporting information in corporate environments
  • Structured Query Language (SQL), Oracle's language for adding, deleting, and modifying information stored within the database
  • Oracle's SQL*Plus interaction environment, used to accept, validate and execute requests against the Oracle RDBMS
  • Programming Lanaguage for Structured Query Language (PL/SQL), Oracle's developer-level programming language for automating, scheduling, and executing data extraction, transformation and reporting tasks.
  • Oracle's Enterprise Manager, offering developers, end-users and administrators a graphical user interface (GUI) for querying and manipulating information within Oracle databases.

To utilize Oracle's capabilities fully, you need to know how it works with Oracle's Programming Language for Structured Query Language, better known as PL/SQL. The course explores interactions between SQL and PL/SQL. It includes a succinct introduction to creating GUI programs using Windows Forms. The course concludes with an overview of data storage management within Oracle, including techniques for creating and using referential integrity to enforce relationships among tables in a database.

Numerous programming examples and exercises are provided, including a case study. Participants will receive a comprehensive set of materials, including course notes and all the programming examples.

Workshop Objectives:

Upon successful completion of this course, participants will be able to:

  • Create and maintain database objects and to store, retrieve and manipulate data
  • Retrieve data by using advanced techniques such as ROLLUP, CUBE, set operators and hierarchical retrieval
  • Write SQL and SQL*Plus script files using the iSQL*Plus tool to generate report-like output.

IAPS can customize Oracle training to achieve specific organizational objectives.

Topic Outline:

  • Introduction to Oracle9i with SQL
    • Life Cycle Development Phases
    • Defining a Database
    • Theoretical, Conceptual and Physical Aspects of a Relational Database
    • How a Relational Database Management System (RDBMS) is Used to Manage a Relational Database
    • Oracle Implementation of Both RDBMS and Object Relational Database Management System (ORDBMS)

  • Writing a Basic SQL Statement
    • 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
    • Various Types of Functions Available in SQL
    • Using a Variety of Character, Number, and Date Functions in Select Statements
    • Conversion Functions and How They are Used

  • 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
    • Types of Problems Subqueries Can Solve
    • Describing Subqueries
    • Types of Subqueries
    • Writing Single-Row and Multi-Row Subqueries
    • 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 Object
    • 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

  • 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

  • Oracle9i Single Row 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 Creating by ROLLUP or CUBE Operators
    • Using GROUPING SETS to Produce a Single Result Set Equivalent to a UNION ALL Approach
    • Using the WITH Clause

  • Advanced Subqueries
    • Updating and Deleting Rows by Using Correlated Subqueries
    • Writing a Multicolumn Subquery
    • Describing and Explaining the Behavior of Subqueries when Null Values are Retrieved
    • Writing a Subquery in a FROM Clause
    • Types of Problems that can be Solved with a Correlated Subquery
    • Describing a Correlated Subquery
    • Writing Correlated Subqueries
    • Using the EXISTS and NOT EXISTS Operators

  • Extensions to DDL and DML Statements
    • Multitable Inserts
    • Creating and Using External Tables
    • Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint

  • Writing Scripts to Generate Scripts
    • Types of Problems that are Solved by Writing SQL Scripts that Generate Other SQL Scripts
    • Writing and Executing Scripts that Generate Scripts with Commands to Create and Drop Tables
    • Writing and Executing a Script that Generates a Script of INSERT INTO Commands


5 days

Intended Audience:

This course is for programmers with little or no prior knowledge of Oracle or of Structured Query Language, who desire a comprehensive introduction to Oracle's power and capabilities.

Technical Prerequisites:

Participants should have a working knowledge of a structured programming language, such as C, C++, C#, Java or Visual BASIC. Familiarity with relational database management system (RDBMS) concepts and structured query language (SQL) is helpful, but not necessary.

Course Format:

Hands-on lecture/workshop

© Copyright 2004-2015 Institute for Advanced Professional Studies (IAPS)