ISBN 0-9727513-7-8 ISBN 13 978-0972751377 Library of Congress Number:2005901262 256 pages: Perfect bind - 9x7 PD 605 Shelving: Databases/Oracle Easy Oracle Series # 5

Easy Oracle SQL Get Started Fast Writing SQL Reports with SQL*Plus John Garmany Retail Price $16.95 / £11.95 Order now and get immediate online access to the code depot! Only $9.95 (30% off) Key Features About the Author Table of Contents Index Reader Comments Errata

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.

* Get detailed explanations of using Oracle’s SQL*Plus and SQL Worksheet. * Comes with an online code depot with working examples of Oracle reports. * Understand the Oracle database including tables, rows and columns. * Learn all basic SQL Clauses, SELECTs, JOINs, WHEREs, and Grouping. * Learn to modify data using INSERTs, UPDATEs and DELETEs. * Create and manage Tables, Views and Materialized Views. * Use Oracle’s Data Dictionary to find information. * Learn to increase SQL performance using Indexes. * Use SQL*Plus to create detailed reports. * Learn by example as each section contains exercises to test your understanding. * Shows how to incorporate Oracle8i analytic functions in Oracle reports. * Contains working SQL*Plus scripts to get you started fast. * Presents tricks for writing complex SQL in incremental stages.

About the Author:

John Garmany

John Garmany has been working as an Oracle DBA since 2002.

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

Index Topics:

A add_months American National Standards Institute ANSI AUTOTRACE avg B b_tree index basic data type BFILE Binary LOB bit mapped index BLOB BREAK buffer cache built-in data type C CACHE Cartesian product case char data type Character LOB check constraint Chen diagram CLOB COLUMN COMMIT concat consistent view correlated subquery count CTAS CYCLE D data definition language data dictionary data manipulation language date data type Dates day_of_week dba_indexes dba_objects dba_views dbms_lob DDL decode DEFERRED DEFINE DELETE denormalization dirty read DISTINCT DML DROP DROP COLUMN DROP UNUSED COLUMNS DROP USER E Entities Entity Relation Diagram equality join ERD

F feedback First Normal Form Foreign Key foreign key constraint FORMAT G granularity Grouping H HAVING HEAP HOST I IMMEDIATE INCREMENT initcap initSID.ora INSERT instr L Large Object last_day library cache line linesize LOB log buffer Log Buffer long data type longraw data type lpad ltrim M materialized view max MAXVALUE metadata min MINVALUE months_between Multi Row Functions multi row subquery multi-column subquery Multi-table joins Mview N National Language CLOB NCLOB next_day NOCACHE NOCYCLE normalization number data type nvl

O ON DELETE ORDER BY P pages pagesize PL/SQL primary key Q QUERY REWRITE R raw data type ROLLBACK round rowid rpad rtrim S SAVEPOINT Second Normal Form self join shared pool SPOOL SQL*Plus START WITH STATSPACK stdev Structured Query Language Subqueries substr sum T Third Normal Form timestamp data type to_char to_date to_number transaction transitive dependency trim trims trimspool trunc TRUNCATE TTITLE U undo log unique constraint universal rowid UPDATE user_tables user_unused_col_tabs user_users USING V varchar variable character data type variance VERIFY W weak entity

Reader Comments:

 

A great jumpstart for SQL, September 18, 2005 Reviewer: Charles Seale (Tuscaloosa, AL USA) - See all my reviews To people that are new to SQL, John Garmany proves that SQL does NOT stand for Scary Query Language. It is an excellent step-by-step primer to take the uninitiated from clueless to competent. What I think is most impressive, is Mr. Garmany's ability to quickly take the student from creating very simple queries to very complex queries without causing the user severe mental and emotional stress. In fact, the examples included in the book, while challenging, are interesting, and even fun. There are lots of SQL books on the market, but this one doesn't require you to be the office geek to get a LOT out of it. Get this book, work through the examples, and pretty soon you to can impress your co-workers, as well as your boss. Highly recommended!

Page 69 "test" should be "text"

Before: People are putting more and more non-test stuff in their databases. After: People are putting more and more non-text stuff in their databases.