Search Courses
Dates for this class:
No dates are available for this class.

Course Details    [choose new course]     [printable version]
Introduction to Oracle 9i: SQL, PL/SQL, and SQL*Plus
Course Number: ET79169
Category: Database & Collaboration Tools
Duration: 5.00 days
Description

This course introduces students to data server technology and explores the concepts of relational databases, the SQL*Plus interface tool, and the powerful SQL and PL/SQL programming languages. Students will learn and practice how to create and maintain database objects. The students will also learn to store, retrieve, and manipulate data. Finally, the students will learn how to create PL/SQL blocks of application code that can be used in Oracle® forms, reports, and other Oracle applications. This course will help the student to prepare for Oracle's exam, Introduction to Oracle9i SQL (1ZO-007).

Prerequisites
  • Introduction to Oracle9i
Objectives

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

  • Use SQL, PL/SQL, and SQL*Plus.
  • Use SQL*Plus commands.
  • Filter and sort data.
  • Use built-in SQL functions.
  • Work with multiple tables.
  • Create and modify tables and constraints.
  • Create and use other database objects.
  • Build a PL/SQL block.
  • Use PL/SQL control structures.
  • Process cursors in PL/SQL.
  • Handle runtime exceptions.
  • Develop procedures and functions.
  • Develop Oracle packages.
  • Create Oracle triggers.
  • Work with collections of data.
Outline
  • Introduction to Oracle9i: SQL, PL/SQL, and SQL*Plus
    • Introduction to SQL*Plus and iSQL*Plus
    • Introduction to SQL
    • Introduction to PL/SQL
  • SQL*Plus Commands
    • Using SQL*Plus for Editing
    • Using SQL*Plus to Work with Files
    • Using SQL*Plus for Formatting Output
    • Using SQL*Plus Variables
  • Filtering and Sorting Data
    • WHERE Clause
    • ORDER BY Clause
  • SQL Functions
    • Expressions in a SELECT Statement
    • Single Row Functions
    • Group Functions
  • Working with Multiple Tables
    • Joins
    • Subqueries
    • Set Operators
  • Tables and Constraints
    • Creating and Modifying Tables
    • Integrity Constraints
    • Managing Data in Tables
    • Transaction Control Language Statements
  • Other Database Schema Objects
    • Sequences, Synonyms, and Indexes
    • Views
    • Database Object Security
  • Building PL/SQL Blocks
    • Creating PL/SQL Blocks
    • Manipulating Data in PL/SQL
  • PL/SQL Control Structures
    • Conditional Control Structures
    • Iterative Control Structures
  • Cursors
    • Introduction to Cursors
    • Implicit Cursors
    • Explicit Cursors
    • Cursor Loops
  • Exception Handling
    • Introduction to Exception Handling
    • Types of Exceptions
  • Procedures and Functions
    • Developing Procedures and Functions
    • Passing Parameters
  • Oracle Packages
    • Developing Packages
    • Oracle-supplied Packages
  • Oracle Triggers
    • Building a Trigger
    • Trigger Restrictions
  • PL/SQL Collections
    • Defining and Using Collections
    • Collection Methods
  • Appendix A: Tables Used in the Course