|
Easy Oracle SQL: Get Started Fast Writing SQL Reports with SQL* Plus
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.
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
|
