• Note: To purchase the Instructor-Led Training class only, please call 1800 025 855
• Note: For experienced users, please see our Introduction to Oracle for Experienced Users class.
• Note: Applicable sales tax and shipping charges will be added to the purchase price or Education Unit totals for each Technology-Based Training product.

• Application Developers
• Database Administrators
• System Analysts
• Designer/Developers

Required Prerequisites:

• Familiarity with data processing concepts and techniques

Suggested Prerequisites:

• Experience using a graphical user interface (GUI)

Course Objectives:

• Handle runtime errors
• Use basic SQL*Plus commands
• Control data/user access
• Describe relational database concepts
• Retrieve, insert, update, and delete data
• Create and maintain database objects
• Describe the features and syntax of PL/SQL
• Control transactions
• Conditionally control code flow (loops, control structures, and explicit cursors)

Introduction to Oracle SQL and PL/SQL

• Describing the Lifecycle Development Phases
• Defining a 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
• Describing PL/SQL
• Describing the Use of PL/SQL for the Developer and the DBA

Writing a Basic SQL Statement

• Describing the SQL Select Capabilities
• Executing a Basic Select Statement with the Mandatory Clauses
• Differentiating Between SQL and SQL*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

Displaying Data from Multiple Tables

• Writing SELECT Statements to Access Data from More Than One Table
• Describing the Cartesian Product
• Describing and Using the Four Different Types of Join
• Writing Joins Using the Tips Provided

Aggregating Data using Group Functions

• Identifying the Different Group Functions Available
• Explaining the Use of Group Functions
• Grouping Data Using the GROUP BY Clause
• Including or Excluding Grouped Rows Using the HAVING Clause

Writing Subqueries

• Describing the Types of Problems That Subqueries Can Solve
• Describing What Subqueries Are
• Listing the Types of Subqueries
• Writing Single-Row and Multi-Row Subqueries

Multi-Column Subqueries

• Writing a Multi-Column Subquery
• Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved
• Writing a Subquery in a FROM Clause

Producing Readable Output with SQL*Plus

• Producing Queries that Require an Input Variable
• Customising the SQL*Plus Environment
• Producing More Readable Output
• Creating and Executing Script Files
• Saving Customisations

Manipulating Data

• Describing Each Data Manipulation Language (DML) Command
• Inserting Rows into a Table
• Updating Rows in a Table
• Deleting Rows from 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 Oracle7 Data types and the Oracle8 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 Via 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

Declaring Variables

• Recognizing the Basic PL/SQL Block and Its Sections
• Describing the Significance of Variables in PL/SQL
• Distinguishing Between PL/SQL and Non-PL/SQL Variables
• Declaring Variables and Constants
• Executing a PL/SQL Block

Writing Executable Statements

• Recognizing the Significance of the Executable Section
• Writing Statements Within the Executable Section
• Describing the Rules of Nested Blocks
• Executing and Testing a PL/SQL Block
• Using Coding Conventions

Interacting with the Oracle Server

• Writing a Successful SELECT Statement in PL/SQL
• Declaring the Data type and Size of a PL/SQL Variable Dynamically
• Writing DML Statements in PL/SQL
• Controlling Transactions in PL/SQL
• Determining the Outcome of SQL DML Statements

Writing Control Structures

• Identifying the Uses and Types of Control Structures
• Constructing an IF Statement
• Constructing and Identifying Different Loop Statements
• Controlling Block Flow Using Nested Loops and Labels
• Using Logic Tables

• Develop PL/SQL Program Units Complete Course Package
• Oracle8 Database Administration Complete Course Package
• Extended Data Retrieval with SQL

• Oracle SQL: Basic SELECT Statements
• Oracle SQL: Data Retrieval Techniques
• Oracle SQL: DML and DDL
• Oracle PL/SQL: Basics