Course 267:
Oracle Database: A Comprehensive Hands-On Introduction

(5 days)

 

Course Description

The Structured Query Language (SQL) is the standard data access language used by Oracle and every relational database management product. This course deals with the basics of SQL needed by application developers for the retrieval of sets of data. The structure of the SELECT statement and more advanced clauses are discussed along with Oracle’s standard functions. Various types of subqueries are constructed to support complex data extraction. The maintenance of table data with standard SQL Data Manipulation Language commands: INSERT, UPDATE, MERGE, and DELETE are covered, showing the various constructions of each statement. Also shown are basic Data Definition Language commands for building tables, indexes, integrity constraints, etc.

The participant also learns the fundamentals of database design and how to create tables and build views upon them. The implementation of business rules, with declarative constraints, is also covered.

In addition, the student is also exposed to Oracle’s database language, PL/SQL (Procedural Language extensions for SQL). Application developers use it for stored procedures, functions, triggers, and front-end code environments such as the Oracle Developer Suite.

This course lays the foundation for implementing applications that properly utilize the capabilities of the Oracle database.

Learning Objectives

  • Understanding how to design a logical data model and translate it into a physical design
  • Retrieving data with the SELECT command
  • Accessing more than one table using JOIN syntax
  • Issuing functions to format and structure data
  • Aggregating data with Group Functions
  • Using subqueries to implement complex requirements
  • Maintaining table data with INSERT, DELETE, and UPDATE SQL statements
  • Creating tables and adding constraints
  • Simplifying data retrieval with Views
  • Maintaining data using transactional control
  • Using indexes to improve performance
  • PL/SQL: The basic block—embedded statement construct of the language
  • How to define variables and other memory structures
  • Calling functions and other code
  • Debugging and testing techniques
  • Using EXCEPTION processing to handle errors and manage the application process flow

Who Should Attend

Audience includes all application developers, both front-end coders and those specializing in server-side programs. Typical job functions include: programmers, database administrators, system analysts, support personnel, application designers, and quality assurance.

Prerequisites

Knowledge of relational theory is a plus.


Course Outline

Introduction and Overview

  • What Is SQL?
  • ANSI Definitions and Oracle Enhancements

Unit 1: Presenting Statements to Oracle

  • The Command Line Processor
  • SQL*Plus
  • SQL Developer
  • Third Party Products
    • TOAD
    • SQL Navigator

Unit 2: The Basic SELECT Statement

  • The Select List
  • The From Clause

Unit 3: Restricting and Sorting the Rows

  • The Where Clause
  • The Order By Clause

Unit 4: Single Row Functions

  • Concept of a Function
  • Basic Server Datatypes
  • Virtual Columns
  • SecureFiles
  • The NULL Value
  • Numeric Functions
  • Character Functions
  • Date Functions
  • Other Functions

Unit 5: Accessing Multiple Tables

  • Concept of a Join
    • Basic Oracle Syntax
    • ANSI Syntax
  • Outer Joins
    • Oracle Syntax
    • ANSI Syntax
  • Other Joins

Unit 6: Set Operators

  • UNION
  • INTERSECT
  • MINUS

Unit 7: Aggregating Information with Group Functions

  • Definition
  • Types
  • The GROUP BY Clause
  • The HAVING Clause

Unit 8: Subqueries

  • Definition and Structure
  • Simple Subqueries
  • Correlated Subqueries

Unit 9: Data Manipulation Language (DML) Commands

  • INSERTING
  • UPDATING
  • DELETING
  • MERGING

Unit 10: Creating and Managing Tables and Views

  • The CREATE TABLE Statement
  • ALTERing Tables
  • Creating and Managing Views
  • Using Views

Unit 11: Creating Constraints

  • Business Rules: The State of the Data
  • Mandatory Columns
  • Primary Keys
  • Unique Constraint
  • NOT NULL
  • Foreign Keys
  • CHECK Constraint

Unit 12: Improving Performance with Indexes

  • How Oracle Processes SQL
  • B-Tree Indexes
  • Bitmap Indexes
  • Indexing Guidelines

Unit 13: PL/SQL Overview

  • The Role of PL/SQL in the Oracle Environment
  • The Block Structure
  • Forms of Execution
    • Anonymous Blocks
    • Stored Code
  • Development Environments
    • Oracle’s SQL*Plus Products
    • Third-Party Products

Unit 14: PL/SQL Basics

  • Declaring Variables
  • Datatypes
    • Storable Datatypes
    • Programmatic Datatypes
    • Anchoring
  • Record Definitions
    • Table Records

Unit 15: Executable Statements

  • Built-In Functions
  • Process Flow Control
  • Nesting Blocks
  • Scope and Visibility
  • Naming Conventions

Unit 16: Debugging and Testing

  • Complier Processing and Messages
  • Creating the Test Environment

Unit 17: Interacting with the Oracle Server

  • Reading Data
    • SELECT INTO
    • Explicit Cursors
    • The Cursor FOR LOOP Statement
  • Issuing DML Statements
    • Implicit Cursors
    • Using records
  • Transactional control
    • Locking
    • SELECT FOR UPDATE
    • COMMIT, ROLLBACK

Unit 18: Course Summary

 

Please Contact Your ROI Representative to Discuss Course Tailoring!