Introduction to Oracle 11g R2 SQL Programming

ORC-230 (5 Days)

Oracle SQL Training Overview

Accelebrate's Introduction to Oracle 11g R2 SQL Programming training teaches students how to create, retrieve, and manipulate objects in Oracle 11g R2 Structured Query Language (SQL). Students will also be introduced to Oracle^® 11g R2 database features and tools.

Location and Pricing

Most Accelebrate courses are taught as private, customized training for 3 or more attendees at our clients' sites worldwide. In addition, we offer live, private online classes for teams who may be in multiple locations or wish to save on travel costs. Please visit our client list for organizations for whom we have delivered onsite training. To receive a customized proposal and price quote for private on-site or online training, please contact us.

Oracle SQL Training Objectives

Use SQL Developer and other available SQL interfaces.
Write simple SQL queries and format & sort the data as needed.
Write moderately complex SQL queries using various join techniques.
Supplement SQL code with references to pseudo columns and system functions.
Summarize, group, and combine data to obtain more meaningful query results.
Draw conclusions and make business decisions based upon the data processed.
Learn advanced query techniques such as set operations, sub-queries, and summary functions.
Create and maintain database tables using the SQL Data Definition Language (DDL).
Manage data within tables using the SQL Data Manipulation Language (DML).
Take advantage of regular expressions and support for international data and time zones.
Learn to use the SQL99 join syntax.
Perform advanced SQL queries using techniques e.g. CASE structures, rollup, and cube operations.
Use advanced table definitions and the creation of indexes, sequences, and views.
Use the powerful set of built-in SQL functions.
Handle DML errors without application interruption.
Obtain database object metadata from the data dictionary.
Understand basic database security and object privileges.
Protect database integrity and extend the conceptual data model using declarative constraints.
Use SQL Developer and other available SQL interfaces.

Oracle SQL Training Outline

Expand All | Collapse All | Printer-Friendly

Relational Databases & SQL

About Relational Databases
Elements Of SQL

Choosing A SQL & PL/SQL Interface

About Database Connections
About Bind Variables
Using SQL Developer
Using SQL*Plus
Using Application Express

Building A Select Statement

About the Select Statement
Using Alias Names

Restricting Data With the Where Clause

About Logical Operators
Equality Operator
Boolean Operators
Null & Between Operators
Is [Not] Null Operator
[Not] Between Operator
Finding Text Strings
[Not] Like Operator
Regexp_Like()
In Operator

Sorting Data With the Order By Clause

About the Order By Clause
Multiple Column Sorts
Specifying the Sort Sequence
About Null Values Within Sorts
Using Column Aliases
Pseudo Columns & Functions
Joining Tables

Using the Set Operators

About the Set Operators
Set Operator Examples

Summary Functions

About Summary Functions
Summary Functions With Distinct

Using Sub-Queries

About Sub-Queries
Standard Sub-Queries
Correlated Sub-Queries

Aggregating Data Within Groups

About Summary Groups
Finding Groups Within the Base Tables
Selecting Data From the Base Tables
Selecting Groups From the Result Table

Build Simple SQL*Plus Reports

Format Column Output With Column
Defining Report Breaks With Break
Produce Subtotals With Compute

Use Data Definition Language To Create & Manage Tables

Create Table Statement
Not Null
Default
Alter Table Statement
Drop Table Statement
Supportive Statements
Describe
Rename

Use Data Manipulation Language To Manipulate Data

About the Insert Statement
About the Delete Statement
About the Update Statement
About Transactions
Rollback
Commit
Savepoint
Set Transaction
Truncate Table
Complex Table References

SQL99 Join Techniques

Cross Joins
Natural Joins
Inner Joins
Outer Joins
Anti Joins
Using Named Subqueries

Enhancing Groups With Rollup & Cube

Using Rollup
Using Cube

Using the Case Expression SQL Functions

Character
String Formatting Functions
ASCII Codes Functions
Pad & Trim Functions
String Manipulation Functions
String Comparison Functions
Phonetic Search Functions

SQL Functions: Numeric

About the Numeric Functions
Null Value Functions

SQL Functions: Date

Date Format Functions
Date Arithmetic Functions

Database Objects: Relational Views

About Database Objects
About Relational Views
Updating View Data
Maintaining View Definitions
Alter View
Drop View

Database Objects: Data Dictionary Storage

About the Data Dictionary
Object-Specific Dictionary Views
User_Updatable_Columns
Understanding the Data Dictionary Structure

Database Objects: Indexes

About Indexes
Use B-Tree Indexes

Database Objects: Other Objects

More About Creating Tables
About Sequences
Alter Sequence & Drop Sequence
Alter Sequence
Drop Sequence
About Synonyms
Drop Synonym
Create Schema Authorization

Database Objects: Security

User Passwords
Object Security

Data Integrity Using Constraints

About Constraints
Not Null Constraint
Check Constraint
Unique Constraint
Primary Key Constraint
References Constraint
Defining Constraints On Existing Tables

Managing Constraint Definitions

Renaming & Dropping Constraints
Enabling & Disabling Constraints
Deferred Enforcement
Set Constraints
Handling Constraint Exceptions
Constraints With Views
Data Dictionary Storage

Advanced SQL*Plus Reports

Break
Compute
Column
Ttitle & Btitle

Building SQL*Plus Scripts

Using SQL*Plus Variables
Define & Undefine
Accept
Prompt
Pause

SQL*Plus Session Control

Controlling System Variables
Set
Show

Conclusion

40%

Lecture/Demo

60%

Lab

Course Number:

ORC-230

Duration:

5 Days

Prerequisites:

No prior Oracle SQL experience is presumed.

Training Materials:

All students receive comprehensive courseware and a related textbook.

Software Requirements:

A complete installation of Oracle Express Edition 11g R2
Oracle SQL Developer

Alabama

Huntsville

Montgomery

Birmingham

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

San Francisco

Oakland

San Jose

Orange County

Los Angeles

Sacramento

San Diego

Colorado

Denver

Boulder

Colorado Springs

Connecticut

Hartford

Washington

Florida

Fort Lauderdale

Miami

Jacksonville

Orlando

Saint Petersburg

Tampa

Georgia

Atlanta

Augusta

Savannah

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Ceder Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

Baton Rouge

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Hagerstown

Frederick

Massachusetts

Springfield

Boston

Cambridge

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Saint Paul

Minneapolis

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Reno

Las Vegas

New Jersey

Princeton

New Mexico

Albuquerque

New York

Buffalo

Albany

White Plains

New York City

North Carolina

Charlotte

Durham

Raleigh

Ohio

Canton

Akron

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Tulsa

Oklahoma City

Oregon

Portland

Pennsylvania

Pittsburgh

Philadelphia

Rhode Island

Providence

South Carolina

Columbia

Charleston

Spartanburg

Greenville

Tennessee

Memphis

Nashville

Knoxville

Texas

Dallas

El Paso

Houston

San Antonio

Austin

Utah

Salt Lake City

Virginia

Richmond

Alexandria

Arlington

Washington

Tacoma

Seattle

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Edmonton

Calgary

British Columbia

Vancouver

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan