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 


 

 

 


 

 

 

Introduction to Oracle data access & reporting

© 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

 

 

Key Features

* Understand architecture of the Oracle9i database.

* Learn to Run an SQL command.

* Join multiple tables.

* Use IN, NOT IN, EXISTS and NOT EXISTS.

* Interpret EXPLAIN PLAN Output.

  

Course Description

Introduction to Oracle is a five-day course designed to provide non-technical professionals with an in-depth understanding of relational databases, SQL, and advanced data extraction and data formatting.

 This five-day course will describe all of the features of a relational database including tables, indexes and high-level database management.  In addition this course will explain the Oracle architecture and will include detailed coverage of Oracle SQL queries.

Book Recommended

  Oracle9i: A Beginner's Guide

Michael Abbey, Michael J Corey, Ian Abramson

Osborne ORACLE Press Series
McGraw-Hill Professional Publishing

ISBN: 0072192798

  OCA/OCP: Introduction to Oracle9i SQL Study Guide

Chip Dawes & Biju Thomas

Sybex

ISBN: 0782140629

Audience

This course is designed for the working Oracle professional and the amount of previous experience with Oracle is incidental.
Previous experience with relational database management and SQL is helpful, but this class is self-contained and has no formal prerequisites.

Curriculum Design

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle database administration.  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 secrets in this intense Oracle training.

Learning Objectives

 By the end of this course the student will be able to describe the basic features of a relational database and will be able to understand how Oracle manages and stores information. The student will also be familiar with the commands used to create tables, and indexes, and the SQL required to extract complex data sets from Oracle and format SQL output.




Introduction to Oracle9i course on data access

Syllabus

© 2007-2016 by Donald K. Burleson

 
Day 1 – Introduction and overview of Oracle9i
 
Day 1 will be a basic overview of the Oracle9i architecture.  It will describe the basic functions of a database and describe how a database stores and delivers information.
Introduction to the course
Introduction to relational databases
  • Architecture of the Oracle9i database
  • Tables & Indexes
  • Referential Integrity – enforcing business rules        
  • Ways to access Oracle Information  
  • Designing tables – normalization
  • Oracle and Data Warehouses                      
 Setup and configuration of PCs for class exercises
 
DAY 2 – Introduction to Oracle SQL queries
 
This day we will introduce the Oracle SQL access method and see how Oracle SQL scripts can be stored you’re your PC and executed against the Oracle database.
In-class exercise – Entering SQL*Plus
In-class exercise – Running an SQL command
 We will then cover running a query against the demonstration database and see how SQL can be used to compute sums and averages.
          In-class exercise – Building and formatting an SQL command
 We will then cover the column SQL*Plus parameter and see how data from an Oracle database can be formatted for readable display
In-class exercise – Formatting a SQL report
 We will also see how to use the spool command to redirect the output of a SQL command into a file on your PC.
             In-class exercise – Spooling a report
 
DAY 3 – Standard Oracle SQL coding and DDL techniques
 
 Day 3 will introduce the basic concepts of coding joins between Oracle tables.
 Introduction to Oracle SQL joins
         In-class exercise – Joining two tables
 Introduction to table navigation
          In-class exercise – Joining multiple tables
 Introduction to outer joins
           In-class exercise – Joining multiple tables with outer joins
 Introduction to database design
             In-class exercise – Design a simple database
 DDL and creation of Oracle entities
              In-class exercise – Create tables and indexes
 Introduction to DML
              In-class exercise – Insert and Update and delete from Oracle tables
 
DAY 4 – Oracle Administration
 
 Day 4 will focus on the Oracle GUI tools show how the tools can be used to report on entities within the Oracle data dictionary.
 Introduction to the Oracle GUI tools, including SQL*Plus worksheet and DBA Suite
             In-class exercise – Review table definitions and index definitions
 Using the GUI to generate DDL statements
             In-class exercise – Use DBA suite to create tables, indexes, and
referential integrity constraints.
 Advanced SQL*Plus concepts - Allocating breaks and summaries
 Designing a report from the ground-up
              In-class exercise – Design a complex report from scratch
 
DAY 5 - Advanced Oracle SQL data extraction & formatting
 
Joining Oracle tables
 
  • 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
     
Advanced SQL operators
 
  • Between operator
  • IN and NOT In operators
  • Sub-queries
  • EXISTS clause
  • Using wildcards in queries (LIKE operator)

Aggregation in SQL
 
  • Count(*)
  • Sum
  • Avg
  • Min and max
  • Using the group by clause

SQL access methods
 
  • Review of Basic joining methods
  • Merge join
  • Hash Join
  • Nested Loop join
  • Advanced SQL operators
  • Between operator

SQL Tuning
 
  • Introduction to rule-based optimization
  • Introduction to cost-based optimization
  • Collecting table and index statistics
  • Using column histograms
  • Changing the default optimizer modes
  • Using TKPROF
  • Using SQL*Trace
  • SQL reusability within the library cache
  • Table high-water mark
  • Table striping and table partitions
  • Using EXPLAIN PLAN
  • Interpreting EXPLAIN PLAN Output
  • Using indexes to improve performance
  • Identifying full-table scans
  • Re-writing SQL queries
  • Using hints to improve SQL performance
  • Using parallel query to improve performance
  • Tuning sub-queries

 

 

 

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.