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

Garmany, John

Brossura

ISBN 10: 0972751378 ISBN 13: 9780972751377

Casa editrice: Rampant Techpress, 2005

Riassunto

Targeted at Oracle professionals who need fast and accurate working examples of complex issues, Oracle In-focus books target specific areas of Oracle technology in a concise manner. Plenty of working code is provided without a lot of theory, allowing database managers to solve their problems quickly without reviewing data that they already know. All code scripts are available for instant download from a companion web site.

Su questo libro

L'autore:

John Garmany is a Senior Oracle DBA with Burleson Consulting. A graduate of West Point, an Airborne Ranger and a retired Lt. Colonel with 20+ years of IT experience. John is an OCP Certified Oracle DBA with a Master Degree in Information Systems, a Graduate Certificate in Software Engineering, and a BS degree (Electrical Engineering) from West Point. A respected Oracle expert and author, John serves as a writer for Oracle Internals, DBAZine and SearchOracle and SELECT Magazine. John is also the author of four bestselling Oracle books by Rampant TechPress, Oracle Press and CRC Press and hosts a popular Oracle Application Server Newsletter.

Contenuti:

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

Su questa edizione

• Editore: Rampant Techpress
• Data di pubblicazione: 2005
• ISBN 10: 0972751378
• ISBN 13: 9780972751377
• Rilegatura: Copertina flessibile
• Numero di pagine: 249