|

Addison-Wesley / Prentice Hall

Computer Science

My Instructor Resource Center :  Log in or request access

Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts
John Adolph Palinski

ISBN-10: 0201752948
ISBN-13:  9780201752946

Publisher:  Addison-Wesley Professional
Copyright:  2003
Format:  Paper; 528 pp
Published:  08/08/2002
Status: Instock



Oracle SQL and PL/SQL Handbook is designed as both a basic reference as

well as a how-to guide. It offers thorough coverage of Oracle's SQL query

language and PL/SQL programming language. It focuses on the practical

aspects of using SQL and PL/SQL to most effectively extract usable

information from Oracle systems. Thus it is of use not only to database

administrators, but also to business managers, developers, and power users.The

book's 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 databse. From there, Oracle database objects are covered, along

with uses of the Data Definition Language (DDL), the Data Control Language

(DCL), and the Data Manipulation Language (DML). Subsequent chapters

offer complete coverage of the Select command used to retrieve information

from the database. Throughout the book frequently used commands and

content are stressed, and numerous examples of SQL techniques are provided.

In addition, most of the book's chapters provide practice questions designed to

reinforce concepts covered.

The most comprehensive sourcebook of solutions and techniques for improving your use of Oracle SQL and PL/SQL!

° Authoritative and practical coverage of Oracle, its query language SQL, and its programming language SQL

° Covers SQL performance tuning and troubleshooting, using Java with PL/SQL, and using Oracle's new analytic functions to create special business information

° The practice questions at the end of each chapter provide an excellent review for the Oracle Certification exams



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 (<) Operators.

Using the In Operator.

Any, Some, or All Keywords.

Using the Between Operator.

Using the Like Operator.

Using And/ Or To Document Multiple Conditions.

Mixing AND/OR in the Where Clause.

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

View a Sample Chapter PDF:/samplechapter/0201752948.pdf

John Adolph Palinski is a Supervisor of Computer Applications for the Omaha Public Power District where he has developed and implemented numerous Oracle systems. He is also an instructor at the University of Nebraska at Omaha. A Certified Oracle Professional Developer, he has taught SQL, PL/SQL, and Developer 2000 courses and is the author of Oracle Database Construction Kit (Que, 1997). He is a frequent contributor to Oracle periodicals such as Oracle Internals and Oracle Professional. He also provides on-site training through his consulting company, Realistic Software Training.

0201752948AB07152002

Concise and practical, this indispensable volume brings the world’s most popular Relational Database Management System (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 challenged 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. Also included are coverage of Oracle’s new analytic functions, a chapter on performance-tuning techniqes 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 Data Manipulation 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
  • Producing business information with business objects
  • Using Java with PL/SQL

    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 their use of Oracle.



    0201752948B07152002

    • PowerPoint
    Palinski
    ©2003 | Addison-Wesley Professional | On-line Supplement | Not Yet Published
    ISBN-10: 0321356101 | ISBN-13: 9780321356109
        View Downloadable Files

    Pearson Higher Education offers special pricing when you choose to package your text with other student resources. If you're interested in creating a cost-saving package for your students contact your Pearson Higher Education representative.

    Copyright © Pearson Education, All rights reserved. Legal Notice | Privacy Policy | Permissions