Introduction to Oracle
data access & reporting
© 2007-2016
by Burleson Corporation
* 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.
|
|
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.
 |
|
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
|
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.
|
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
-
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
|
