Easy Oracle SQL: Get Started Fast Writing SQL Reports with SQL*Plus

Description

Written by a graduate of West Point, John Garmany leverages his 20+ years of experience into an indispensable guide for any Oracle professional who must quickly implement Oracle reporting. A noted instructor, author and lecturer, John Garmany leverages his ability to explain complex issues in Plain English into a one-of-a-kind book. John Garmany targets his decades of SQL*Plus experience into this must-have book. Intended for anyone who needs to extract Oracle data and format reports, John reveals the secrets of quickly and easily producing stunning reports from Oracle.

Unknown to most Oracle professionals, special SQL extraction techniques and SQL*Plus commands can be used to quickly create complex reports, without buying expensive third-party reporting tools. Best of all, John Garmany shares dozens of working samples in his online code depot. Your time savings from a single script is worth the price of this great book.

Easy Oracle SQL is your introduction to using SQL and SQL*Plus with an Oracle database. Whether you are just learning SQL or just need a handy reference, this book will provide you with proven methods to building queries and reports using SQL.

Each concept is explained in detail with multiple examples and exercises at the end of each section. Also included is a section detailing how to effectively use SQL*Plus to not only create and run queries but also to create quality reports and scripts. This book will have you up and using SQL quickly and efficiently.

 

Table of Contents

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 how to


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


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

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

 

4 – PL/SQL Programming

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



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