Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts BK&CD-ROM

Concise and practical, this indispensable volume brings the world's most popular RDBMS, its query language SQL, and its programming language PL/SQL into clear focus for today's busy database professional or business analyst. Covering the most commonly used Oracle features, Oracle SQL and PL/SQL Handbook is ideal for the developer or business user charged with implementing, maintaining, and retrieving mission-critical data within the Oracle database environment.

Broadly designed as both a basic reference and how-to, this book offers extensive coverage of Oracle's SQL language and database concepts, providing an excellent review for the Oracle certification exams. The initial chapters offer a complete introduction to the relational database environment, including a discussion of how to use the logical data model to understand the database. Oracle database objects are explored, along with uses of the Data Definition Language (DDL), the Data Control Language (DCL), and the Data Manipulation Language (DML). Later chapters offer complete coverage of the Select command used to retrieve information from the database and also include Oracle's new analytic functions. Also included are a chapter on performance-tuning techniques needed for complex SQL and a detailed overview of Oracle's PL/SQL language.In addition, many chapters provide practice questions designed to reinforce newly introduced concepts, as well as numerous examples of SQL techniques.

The broad-based and comprehensive coverage also includes:

• Understanding the database using entity relationship diagrams and database schema diagrams
• Applying the Data Definition, Data Control, and DataManipulation languages to create and maintain the database
• Using the SQL language to retrieve information from the database
• Creating special business information using Oracle's new analytic functions
• Making your SQL perform better with common troubleshooting techniques
• The importance of Business objects to produce business information
• Using Java with PL/SQL

The accompanying CD-ROM provides a practice database along with the sample scripts referenced in this book.

Authoritative and practical, Oracle SQL and PL/SQL Handbook provides today's administrator and business analyst alike with the most comprehensive sourcebook of solutions and techniques for improving your use of Oracle.

Introduction.
What is Oracle?
Who Should Use This Book?
How is this Book Organized?
Conventions.
Other Sources of Information.
Installing the Practice Database and Tools.
What Software to Get?
How to Get the Software?
Setting Up Personal Oracle.
Installing the Practice Tables.
What's Next.
1. Understanding Relational Databases—The Entity Relationship Diagram, Database Schema Diagram, and Normalization.
Entities and Attributes.
Entity Relationship Diagram Concepts.
Ordinality and Cardinality.
Keys and Joins.
Creating an Entity Relationship Drawing.
Identifying the Major Entities.
Defining the Identifiers.
Defining the Relationships, Cardinality, and Ordinality.
Defining Descriptive Attributes.
Creating the Entity Relationship Diagram.
Converting the Entity Relationship Diagram to a Relational Model.
Conversion to the Relational Model.
Normalization.
The Data Schema Diagram.
Table Relationship Drawing.
Employee Database Table Relationship Drawing.
What's Next.
Practice.
2. Building the Database with the Data Definition Language.
Logging On to SQL*PLUS.
Entering A Command.
Using The SQL*PLUS Editor.
Menu Commands.
Database Objects That Store Data.
The Desk Analogy.
Tablespaces.
Creating and Modifying Tables.
Indexes.
Maintaining the Integrity of the Database.
Synonyms and Other Nice Things.
Oracle Object-Oriented Features.
User-defined Data Types (ADTs).
Collection Types.
Toad.
What's Next.
Practice.
3. The Data Control and Data Manipulation Languages and the Data Dictionary.
Creating User Accounts.
Data Control Language.
Roles.
Revoking Privileges.
Privileges.
Granting Access to Your Tables and Database Objects.
The Data Manipulation Language (DML).
Using the INSERT Command.
Inserting Records from Another Table.
Using the UPDATE Command.
Deleting Records.
The COMMIT and ROLLBACK Commands.
The TRUNCATE Command.
Oracle's Data Dictionary.
The User_objects and All_objects Views.
Reviewing PL/SQL Code Blocks with User_source.
What's Next.
Practice.
4. Retrieving Database Records Using SQL—The Select, Where, and Order By Clauses.
Identifying Table and Column Names.
Making a Simple Database Query.
Components of a Simple Select.
Examples of Simple Select Statements.
End listing.
Computing numeric values in the Select clause.
Limiting the Database Records Retrieved.
Evaluation Operators Used In The WHERE Clause.
Using the Equal (=) and Is Operators.
Using the Greater Than (>) and Less Than (
Ordering Records.
Ascending/Descending Order.
Using Expression Numbers as the Names of Sort Columns.
Printing the Results of Your Query.
What's Next.
Practice.
5. Retrieving Records Using SQL—The From, Group By, and Having Clauses.
The From Clause.
Retrieving Records From Multiple Tables.
How to Combine or Join Tables.
What to do with Join Columns with the Same Name.
Join Types.
Joining Tables When the Value Only Exists in One Table-The Outer Join.
Non-equijoins.
Self Joins.
Cartesian Joins.
Computing Group Values.
Group Functions.
Counting the Group's Record Instances.
Summing the Values in a Group.
Averaging the Values in a Group.
Determining the Minimum Value.
Determining the Maximum Value.
Using the Group Functions on Smaller Groups.
Limiting Selected Records Using a Group Function.
What's Next.
Practice.
6. Creating New Values with Character Functions.
Character Functions.
The Initcap Function.
The Instr Function.
The Length Function.
The Lower Function.
The LPAD Function.
The LTRIM Function.
The REPLACE Function.
The RPAD Function.
The RTRIM Function.
The SOUNDEX Function.
The SUBSTR Function.
The TO_CHAR Function.
The TRANSLATE Function.
Trim.
The UPPER Function.
The DECODE Function.
Case Expressions.
Using Functions in the Where Clause.
What's Next.
Practice.
7. Creating New Values with Numeric and Date Functions.
Dates and Date Functions.
Date Functions.
What's Next?
Practice.
8. Using Set Operators, Subqueries, and Variables.
Set Operator Rules.
The Union and Union All operators.
The MINUS operator.
The Intersect operator.
Subqueries.
Multiple-row subqueries.
Multiple column queries.
Correlated subqueries.
Variables.
Using the Single ampersand substitution variable.
Using the Double ampersand variable.
Defining user variables.
What's next.
Practice.
9. Analytical Processing with SQL.
Rollup.
Cube.
The Grouping function.
Ranking Functions.
The Rank Function.
The Dense_rank Function.
Top-N and Bottom-N queries.
The Percent_rank Function.
The Cume_dist Function.
The Ntile Function.
The Row Number Function.
Windowing.
The Cumulative Aggregate Function.
Moving Averages.
The Ratio_to_report Function.
The Lag and Lead Functions.
Statistic Functions.
What's Next.
Practice.
10. Using Database and Materialized Views.
The With Check Option.
The Read Only Option.
Materialized Views.
What's Next.
Practice.
11. Using SQL*PLUS as a Report Writing Tool.
Formatting Columns.
Breaks.
Subtotals.
Sending the Output to the Printer.
Set Commands.
The Environment Dialog Box.
What's Next.
12. What Can Your Do if Your SQL Does Not Perform.
Indexes.
Which Columns Should be Indexed.
Multi-column Indexes.
Index Types.
Bitmap Indexes.
Reverse Key Indexes.
Index Organized Tables.
Function Based Index.
Optimization.
The Rule Based Optimizer.
The Cost Based Optimizer.
Setting the Optimizer.
Another Word or Two on Access Paths.
Join Operations.
Nested Loop Join.
Sort Merge Join.
Hash Join.
Explain Plans.
Plan_table.
Populating Plan_table.
Reading the Explain Plan.
Dynamic Performance Views.
Trace.
Running and Viewing the Trace Statistics.
Hints.
Odds and Ends.
What's Next.
13. Using Business Objects.
Data Warehouses.
Business Objects.
Drilling Down and Exceptions.
Oracle Discoverer 4.0-The OLAP tool of Choice.
What's Next.
14. The Basics of PL/SQL.
15. Handling Exceptions, Using Named Procedures.
16. Advanced PL/SQL Topics.
Appendix A. Glossary.
Appendix B. Answers. 0201752948T03282002