Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Oracle SQL Programming and Tuning

Also see our

Oracle SQL Tuning Training Class and Advanced SQL training

An intensive Oracle SQL training course
? 2007-2016 by Burleson Corporation

 

This course is taught at your Company site with up to 20 students. 

Click here for on-site course prices

Optional supplemental mentoring

 

   
Key Features

* Understand Oracle.

* Explore Oracle data access.

* Learn DDL and DML.

* Use PL/SQL.

* Understand Oracle Report writing.
 

 

 

  

Course Description

Introduction to SQL and PL/SQL is an intensive course that is designed to give the student maximum exposure to Oracle SQL.  The student learns by doing, and this class has dozens of in-class exercises and the student will be guided from very simple SQL commands to increasingly complex PL/SQL coding techniques.

The topics start with the basics of SQL and progress into increasingly complex queries, including table joins, subqueries and creating Oracle views.  The PL/SQL section begins with simple concepts and the student gradually masters PL/SQL through increasingly challenging classroom exercises.

Book Required

  Easy Oracle SQL

John Garmany

ISBN 0-9727513-7-8

 

Audience

This course is designed for practicing Oracle professionals who have basic experience with SQL and the use of a relational database. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

Curriculum Design

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle SQL tuning.  Burleson was chosen by Oracle Press to write the authorized edition of Oracle High-Performance SQL tuning.  Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their Oracle secrets in this intense Oracle SQL tuning training

Learning Objectives

By the end of this course the student will be able to formulate advanced SQL queries including correlated subqueries and outer joins. The student will also become familiar with the internals of Oracle SQL and will be able to use the EXPLAIN PLAN utility to tune SQL statements.



Oracle SQL Programming & Tuning
Syllabus

(c)  2007-2016 by Donald K. Burleson

Day 1 – Introduction and overview of Oracle

This is an introduction to Oracle relational database and the Oracle architecture. We discuss Oracle data storage architecture, Oracle table and indexes, and database access methods.


1-1: The Oracle Relational Database

• The relational model
• Oracle overview
• One-to-many relationships
• Many-to-many relationships
• Recursive many-to-many relationships

1-2: Inside the Oracle Architecture

• SGA regions
• Background processes
• Disk architectures

1-3: Data storage

• Disk storage
• Solid state disks
• RAM caches
• Flat-file data storage (BFILE, external tables)
• Spreadsheet file access
1-4: Introduction to Oracle Tables

• Standard tables
• Index organized tables
• External tables

1-5: Introduction to Oracle Indexes

• B-tree indexes
• Bitmap indexes
• Bitmap join indexes
• Star indexes


1-6: Database access methods

• SQL*Plus
• SQL Worksheet
• Procedural programs
• ODBC
• SQL*XL


Day 2 – Introduction to Oracle Data Access

This is an introduction to Oracle SQL and SQL*Plus.

2-1: SQL Processing

• Syntax checking
• Semantic processing
• SQL parsing
• Creating the executable
• Inside the library cache


2-2: Oracle data dictionary

• The X$ fixed tables
• The DBA Views
• The v$ views
• Running dictionary scripts



2-3: SQL*Plus

• Entering SQL*Plus
• Using the desc command
• Using the spool command


2-4: Syntax of SQL

• Select, project and join
• WHERE clause
• ORDER BY clause
• GROUP BY clause
• In-line views
• SQL in SELECT clause (Oracle9i new feature)

2-5: Report generation with SQL*Plus


• Entering SQL*Plus
• Running an SQL command
• Building and formatting an SQL command
• Formatting a SQL report
• Spooling a report


2-6: Views

• Logical encapsulation of complex queries
• Using the DBA_VIEWS view
• Problems combining views



Day 3 – Introduction to DDL and DML

This is an introduction to Oracle schema definition and data updating.


3-1: SQL schema navigation

• Schema entity types
• Relationships between schema entities

3-2: Schema components

• Data files
• Tablespaces
• Oracle objects (tables, indexes, IOT, etc.)

3-3: Referential Integrity

• Implementing Data Integrity
• Types of RI – not null, unique check, foreign key
• Rules for using RI
• System-named constraints (SYSnnn)
• Viewing Constraint Information with dba_constraints

3-4: DDL Introduction

• Create a database
• Create a table
• Create an index
• Altering schema objects


3-5: DML Introduction

• Inserts
• Updates
• Deletes
• RI constraints (restrict, cascade)
• Snapshot too old

3-6: Read consistency, locking & DML

• Why read consistency?
• UNDO segments and read consistency

Day 4 – PL/SQL


4-1: Introduction to the PL/SQL Language

• Interpreted language
• Syntax examples
• Procedures, packages and functions

4-2: PL/SQL Language structures

• Looping within PL/SQL
• If testing (if then else syntax)
• Exception conditions
• Array processing

4-3: SQL with PL/SQL

• Using a cursor
• Fetching from a cursor


4-4: PL/SQL Packages and Procedures



4-5: Extending SQL with a user-defined PL/SQL function



4-6: Oracle supplied DBMS packages




Day 5 – Oracle Report Writing

This is an introduction to Oracle reporting tools and techniques. Your will learn how to use the SQL*Plus formatting commands to create easy reports from Oracle, how to join multiple tables together and how to easily aggregate, summarize and analyze Oracle table data. You also learn how Oracle access the tables and indexes and see how to ensure that your SQL uses the fastest methods for accessing your data.


5-1: Formatting commands in SQL*Plus


5-2: Analytical functions in SQL


5-3: Table joins

• Equi-join
• Outer join
• Hiding joins by creating views
• Using IN, NOT IN, EXISTS and NOT EXISTS
• Subqueries
• Exercise – write a subquery
• Correlated subquery
• Non-correlated subqueries

5-4: Subqueries in SQL

• IN and NOT In operators
• Sub-queries
• EXISTS clause

5-5: Aggregation in SQL

• Between operator
• Using wildcards in queries (LIKE operator)
• Count(*)
• Sum
• Avg
• Min and max
• Using the group by clause


5-6: Materialized Views for reports 


 


This is a BC Oracle SQL training course (c) 2002-2016

We also provide PLSQL optimization and PL/SQL optimization consulting services.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.