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

Paperback (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $1.99
Usually ships in 1-2 business days
(Save 96%)
Other sellers (Paperback)
  • All (11) from $1.99   
  • New (6) from $21.82   
  • Used (5) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$21.82
Seller since 2008

Feedback rating:

(71)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
2002 Paperback New Ships Fast! Satisfaction Guaranteed!

Ships from: Skokie, IL

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$29.70
Seller since 2009

Feedback rating:

(9531)

Condition: New
New Book. Shipped from US within 4 to 14 business days. Established seller since 2000

Ships from: Secaucus, NJ

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$31.70
Seller since 2014

Feedback rating:

(29)

Condition: New
All orders ship same business day via standard shipping (USPS Media Mail) if received by 4 PM CST. We do not ship to APO/FPO addresses.

Ships from: Richardson, TX

Usually ships in 1-2 business days

  • Standard, 48 States
$32.50
Seller since 2006

Feedback rating:

(330)

Condition: New
Boston, MA 2002 Trade paperback New. NEW. Audience: General/trade.

Ships from: Chicago, IL

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$35.05
Seller since 2007

Feedback rating:

(23160)

Condition: New
BRAND NEW

Ships from: Avenel, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$61.27
Seller since 2013

Feedback rating:

(2)

Condition: New
New

Ships from: San Diego, CA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 1
Showing All
Close
Sort by

Overview

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

Read More Show Less

Editorial Reviews

From The Critics
Introduces the basic relational database terminology and techniques for extracting information from Oracle databases. Written for beginners, the book focuses on the SQL*Plus query language and the SELECT command for querying a database. Business objects and data warehouses are briefly covered. The final three chapters overview the PL/SQL programming language. The CD-ROM contains a practice database and sample scripts. Annotation c. Book News, Inc., Portland, OR
Read More Show Less

Product Details

  • ISBN-13: 9780201752946
  • Publisher: Addison-Wesley
  • Publication date: 8/28/2002
  • Edition description: BK&CD-ROM
  • Pages: 488
  • Product dimensions: 7.00 (w) x 9.05 (h) x 1.02 (d)

Meet the Author

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

Read More Show Less

Read an Excerpt

Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts is a book whose purpose is to teach you techniques that you can use to extract information from complex modern Oracle relational databases. The business world has constructed numerous online transaction processing (OLTP) systems, databases, and data warehouses over the past twenty years. Information from these databases is very important to the successful operation of businesses. Corporations have also discovered that it is important to have personnel who can understand and efficiently extract information from these databases. This is why developers, data administrators, and business analysts who can get information from complex databases are so valuable to their companies.

Structured Query Language (SQL), which is an ANSI standard language for interacting with relational databases, is the main tool for extracting the information. SQL is somewhat standard across most relational database products; however, this book covers only Oracle’s version. Oracle is the largest database manufacturer in the world and has the most product installations. So this is a good place to start your education.

There are other tools you will need to know about in order to produce business information. This includes the ability to read and understand the database blueprint. This blueprint is the entity relationship diagram (ERD) or my own favorite tool, the table relationship diagram. You will also need to be aware of database objects such as views, synonyms, and indexes. After you learn how to extract the information, you will want to know how to extract the information quickly. This bookcontains a chapter that has some common techniques that can be used to enhance the performance of your SQL. This book also covers PL/SQL, which is Oracle’s programming language and is an extremely useful language for accessing object attributes and performing special calculations.

This book contains numerous examples of various SQL techniques. It also has practice questions at the end of most chapters. The questions will allow you to practice the skills immediately after studying them. Appendix B contains answers to the practice questions and provides you with another set of examples to study, copy, and adapt to your work.

The book is meant to be a basic reference book and a how-to manual that covers the most important and common Oracle database topics. It is not the ultimate reference book. It is very difficult to learn SQL from these types of books. This book can be used as a reference book, but it does not totally eliminate the need for true reference books that cover the mundane, once-used-in-five-years topics. The purpose of this book is to help you get the skills to analyze, understand, and efficiently extract information from an Oracle database. Developers, database administrators (DBAs), data administrators (DAs), and business analysts normally have a score of books in their work area. No single book can contain everything about all topics. I want this book to be the first book you go to for answers about the Oracle database because it contains the most frequently used information.

This book is based on courses I have taught at Iowa Western Community College and the University of Nebraska at Omaha and on-site seminars I teach at many major companies. During the past four years, I have helped many students understand the basics of Oracle SQL and the techniques used to extract information. The techniques I cover are the result of 15 years of experience producing business information from relational databases. Students say my books and seminar workbooks are “very practical.” I believe in studying and identifying good design, copying it, modifying it, and calling it my own. Much of this good design is included in this book. I truly hope and believe that you will find the information in this book practical, and I hope you steal it and call it your own.What Is Oracle?

Oracle is the largest database manufacturer and the second-largest software manufacturer in the world. The company began as a relational database manufacturer. In the beginning, Oracle touted its software as “being able to run on any platform.” This openness has been most attractive to companies, and Oracle has tried to maintain its image as an open product. Oracle was at a good place when industry became extremely interested in moving away from network databases and the mainframe.

By allowing companies to use the client/server paradigm, Oracle had a competitive advantage. It also identified the Internet as the future paradigm. Oracle remains the premier database manufacturer due to its foresight. Oracle continues to increase the power of its database. The current version is called Oracle9i. It’s an object-relational database, which has features that allow developers to model objects within the database. The i in the name means that its database can also support the Internet. To this end, Java programming functions can be placed within the database. Oracle can understand applications using the Java functions. Oracle has recognized that Java is an open product and is one of the more important languages of the Web.

Oracle also has several other database products. Personal Oracle is a smaller version of the Oracle Enterprise edition. It resides on the client (your personal computer) and is designed primarily as a stand-alone database. Oracle also has a small database called Oracle Lite9i, which is designed for use on portable computers and handheld devices.

Oracle has very powerful application-development, report-writing, and database-analysis software. The application-development package is currently called the Internet Developer Suite. It consists of a number of products, two of which are Developer6i Form Builder and Report Builder. Developer6i Form Builder is Oracle’s rapid application development (RAD) software. Report Builder is Oracle’s main report-writing software.

Designer6i is Oracle’s computer-aided software engineering (CASE) product. It is one of the best-selling CASE products in the country. In addition to performing database documentation and object creation, it can be used to generate forms (screens). It is integrated with the other Developer6i products. Even though it will not be covered in this book, Designer is used in many shops as a repository of database information. It is an important tool that can be used to develop and obtain ERDs and other database documentation.

The last remaining development software is JDeveloper. This is a Java-based application-development product somewhat similar to Developer6i Form Builder. It can be used to create Java-based applications, both client/server- and Web-based. Borland supplied the core technology for this product, which is why JDeveloper has a strong resemblance to Borland’s Jbuilder. Oracle hopes that JDeveloper will someday be the dominant Web development tool.

Discoverer is Oracle’s database-analysis product. It is an online analytical processing (OLAP) tool. It has a very easy user interface and is an extremely powerful tool for developing and analyzing business information. The success of this product is due to the ease of creating business objects for analysis, and to the end users’ easy use of the product. I have actually had novice students using the product with less than two hours of training. It is a great tool for empowering your users and reducing the report-writing load of the programmers, DAs, and business analysts.

Finally, Oracle has an array of packaged products for businesses. One such product is Oracle Financials, an entity resource planning (ERP) application that companies use to document work requests, purchase the materials, maintain inventory levels, and manage their fixed assets, accounts payable, and other financial concerns. Oracle also has an array of other entity packages. As you can see, Oracle has a large number of tools and products.Who Should Use This Book?

At Iowa Western and in my seminars, I allow anyone with Windows (or UNIX) experience to enroll in the courses. I would expect that this is the minimum technical criterion to use this book properly. This is an intro book and will cover most of the commonly used areas of Oracle SQL and PL/SQL. It will also have some elementary coverage of relational and object database components and terminology. This book will be of interest to the following people:

  • Mainframe programmers wanting to upgrade or develop SQL skills
  • Systems analysts, developers, or business personnel interested in data administration
  • Students desiring the skills needed to enter the Oracle market
  • Oracle developers looking for new techniques
  • Developers interested in implementing business objects for analysis
  • Business analysts interested in gaining the skills to analyze corporate databases

You would expect a book such as this to be of interest to readers who desire a technical career. Increasingly, however, it is of importance for accountants, financial analysts, and other nontechnical people to have SQL, database, and online analytical processing (OLAP) skills.

A case in point is the job description for accountants at the company at which I am employed. Accountant job descriptions request Oracle knowledge as a needed skill. With the proliferation of ERP-type databases such as Oracle Financials, SAP, or PeopleSoft, nontechnical personnel are having to derive information from these databases. Knowledge of SQL, PL/SQL, and Discoverer will greatly aid these people, giving them an edge over their coworkers. The worker who can furnish information to management is always a valuable asset. The worker who cannot is not as valuable.

Another class of developers who may reap benefits from this book are Microsoft Access developers. Access is a low-level database product that is highly automated. Over the years, I have received many comments that my courses on SQL and database-object creation help students understand what Access is actually doing.How Is This Book Organized?

This book contains 16 chapters, a glossary appendix, and an answer appendix. It begins with a discussion of the logical data model, which is used to determine what the database represents, to identify data elements, and to identify the data linkages. This information is needed to extract business information effectively using SQL.

The book then discusses the various Oracle database objects. It is important for you to understand these objects, as many of them will affect the SQL that is written. However, it may not be necessary for you to read the latter portions of Chapter 2 until after the SQL chapters. Chapter 2 covers the use of the Data Definition Language (DDL), which is used to create and maintain database objects such as tables or views. The chapter also discusses how to log on to the Oracle database and enter commands. This section is important for the reader new to Oracle. I place the DDL section at the beginning of the book for the readers who want to understand the database engine components before running the engine.

Chapters 4-9 cover the SELECT command, the language used to extract information from the database. Chapter 10 discusses the use of views and se-quences. Views are a really important tool for creating runtime virtual records. Chapter 11 discusses commands that are contained within the database and that can be used to change the presentation of your information. The Oracle database has limited report-writing tools. There are more powerful tools available on the market, including some fine Oracle tools. However, if your company does not have any of these tools, you will always have the tools discussed in Chapter 11 available.

Chapter 12 discusses performance-tuning techniques, those that are common and often used. Chapter 13 discusses business objects, which are database objects that can be used for analysis or to increase the performance of reports. The final three chapters cover Oracle’s PL/SQL language. This language is a must for the data administrator and can be used to create business objects and entity attributes of interest to the user. The language is also used in Oracle’s Report Builder and Form Builder products.

Appendix A of the book is a glossary, providing you with definitions of important database words. Appendix B contains the answers to exercises that reside at the end of many of the chapters. These questions will allow you to practice the discussed topics. I strongly encourage you to perform the practice questions before checking the answers.Other Sources of InformationDespite the best attempts by the technical editors, copy editors, and me, errors and misunderstandings will exist in this book. It is extremely humbling for an author/teacher to have his students/readers interpret his writing differently from what he expected. I have tried to be as skilled a technical writer as possible; however, I am sure that I fail occasionally. To remedy this, I intend to maintain a Web site that you can use to raise questions and to view the answers to previous inquiries. The site will contain corrections, explanations, and clarifications. I believe this will be a valuable aid to you. The following is my home Web site: www.oracle-trainer.com.

Another valuable site is the Oracle Developer Tools User Group (ODTUG). This is an organization to which I have belonged for several years. The group has an annual conference, a quarterly newsletter, and a special site where members can post enhancement requests to Oracle. Of special importance are the organization’s list servers, the access to which at the present time is free to everyone. The list servers allow you to post, answer, and receive advice about a variety of Oracle topics, including SQL.

I belong to the SQL, Java, Developer6i, and Discoverer list servers. I monitor the questions and answers all day, and so do a number of other highly skilled professionals (and authors). It is an excellent place to get the latest Oracle information or help with technical issues. The ODTUG site is at www.odtug.com.

If you are truly interested in Oracle, you must visit their Web site frequently. The home page (www.oracle.com) can be very complex and appears to change daily, but it is the source of new Oracle information. Following are some of the Oracle pages I recommend. Before trying to access the pages, you should know that many of them are available only to members of the Oracle Technology Network. This is a free membership, and you may join using Oracle’s Web site. It will enable you to access a great deal of information.

  • Oracle Store. This page is the site for purchasing CD packs of Oracle databases—Personal Oracle, one of these databases, can be used on your personal computer for course work—and for the Internet Development Suite. These tools include Developer6i, JDeveloper, Discoverer, and Designer. I strongly encourage you to purchase the database pack and install Personal Oracle—which runs on only Windows NT/2000 at the present time. You can practice all of the techniques discussed in this book. It is one thing to read about Oracle and another actually to employ the techniques. The current site for the CD packs is https://oraclestore.oracle.com/OA_HTML/ibeCZzdMinisites.jsp?a=b. The database package is called Oracle(R) 9i Release 1 (9.0.1) CD Pack for Microsoft Windows.

  • Oracle Certified Professional Programs. Oracle has a variety of certifications, which are a series of tests about Oracle topics. Two of the more popular are the DBA and Developer, which consist of five tests. The initial test for the DBA and Developer certifications concerns SQL and PL/SQL. This book is an excellent primer for this exam. To learn more about Oracle certifications, visit www.oracle.com/education/certification/.

  • Oracle Certified Professional Assessment Test Download. It is possible to download sample certification exams. You might be interested in how you would perform on a certification exam before and after reading this book. Visit www.oracle.com/education/certification/index.html?sts.html to obtain the sample exams.Installing the Practice Database and Tools

Oracle offers evaluation copies of their products for free (or at a nominal price). Many of my students would like to practice their skills at home. I recommend that they visit the Oracle site and obtain the latest evaluation production.

The students sometimes find that the most difficult part of learning Oracle is installing the Oracle products and the practice database. This is usually because the student is not familiar with the Oracle products, has ordered the wrong products—Oracle can change the names of products—or has problems with versions, or there may be a variety of other reasons. I encounter this problem every semester. The installation process can seem like a daunting task, but I think these instructions will help you get the products loaded and will get you on your way to learning Oracle.What Software to Get

First of all, you need to acquire the following product:Personal Oracle
-This is the personal-computer version of Oracle. It has all of the features you need for this book. It is a much smaller version than the Oracle9i Enterprise edition. The Enterprise Edition may also be used with this book, but it requires Windows NT, 2000, or XP.

If you work for a company that has Oracle installed, you might want to use your company’s installation to practice. Your company will likely have a version of the Enterprise edition of Oracle. This product can also be used, and everything within this book applies to the Enterprise edition.

Versions have differences that might be of interest to the experienced Oracle user. The differences in the SQL basics are not generally substantial, and the version differences are not as important for the new student. If you have access to older versions of Oracle, they can be used with this book. However, I would try to acquire the latest database and product versions.How to Get the Software

At the time of this writing, Oracle offers several options, the best of which is to purchase the Oracle Database CD Pack or the Oracle Tools CD Pack (which is not covered in this book). Each of the packs currently costs $39.95 and contains the full range of Oracle databases and tools.

Individual Oracle products may also be downloaded from the Oracle Tech Network (http://technet.oracle.com/). You must register, but the registration is free. The only disadvantage of the download process is that files are very large, and you will need a high-speed connection. If you do not have access to such a connection, the CDs are your only option. The products on the CDs and from the Oracle Tech Network are evaluation copies. They are licensed for a 30-day evaluation. However, this small period of time will help you develop skills that can be used in the workplace. In my opinion, the evaluation products are an excellent tool for the student.

In my opinion, the easiest method to obtain the software is to purchase the CD packs. However, several other sources are available:

  • I have had students contact the local Oracle sales representative. This can be an effective mechanism when you have trouble ordering the CD packs.
  • Oracle has a program known as the Oracle Academic Initiative, which allows any qualified academic institution to purchase all Oracle products for $500 per year. It also allows the institution to copy the software for its students. If you are attending an institution enrolled in this program, you should be able to get the software through the school’s IS department.Setting Up Personal Oracle

The first product to install is the Oracle database. This is an automated pro-cess. Simply place the proper CD into the CD-ROM and follow the instructions. I have seen student installations go relatively easily, and I have seen their installations be very difficult. The difficulty has varied with the different releases of the product. The students did not cause the problems. Incorrect load scripts from Oracle caused the problems. If you encounter difficulties, don’t get frustrated; join the large crowd of new users who have had problems and overcame them. One thing you can do is check my Web site (www.oracle-trainer.com), where I will attempt to have additional instructions or advice available. Another good source is the list server at the Oracle Developer Tools Users Group (www.odtug.com) site. Of course, the best source is Oracle itself.

After installing the database, you must check to see if the database is operational. The following instructions pertain to Oracle 9i.

  1. Before the database can be used, it must be started. When the database is installed, settings are made that start the Oracle database whenever Windows is launched. To determine if the database has been started, check the OracleserviceORCL service. You can view this service by using the Services menu option on the Control Panel, which will display a Windows dialog box. Starting Oracle causes some of your computer’s memory to be used. If you are not using Oracle all the time, you may want to stop the database. This can be done on the Service dialog box.
  2. Launch SQL*Plus. Instructions are contained in Chapter 2. There are two default user IDs and passwords:
    – scott/tiger—This is a default Oracle training ID. It contains the normal privileges allowed for a developer. Your practice database will be installed on this user ID.
    – system/manager—This is the default Oracle master ID. It has the ability to perform all database functions.

If you cannot log on to SQL*Plus, you might not have started the database, you might have entered an incorrect user ID/password, or you might not have installed the Oracle database properly.What’s Next?

The next chapter will introduce you to relational database terminology and theory. The chapter will discuss entity relationship diagrams, table relationship diagrams, and the principles of normalization. The chapter is important in that it will help you understand how to identify needed data items within a relational database. If you cannot locate the database components you need, you will certainly not be an effective user of SQL.

Read More Show Less

Table of Contents

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.

Read More Show Less

Preface

Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts is a book whose purpose is to teach you techniques that you can use to extract information from complex modern Oracle relational databases. The business world has constructed numerous online transaction processing (OLTP) systems, databases, and data warehouses over the past twenty years. Information from these databases is very important to the successful operation of businesses. Corporations have also discovered that it is important to have personnel who can understand and efficiently extract information from these databases. This is why developers, data administrators, and business analysts who can get information from complex databases are so valuable to their companies.

Structured Query Language (SQL), which is an ANSI standard language for interacting with relational databases, is the main tool for extracting the information. SQL is somewhat standard across most relational database products; however, this book covers only Oracle’s version. Oracle is the largest database manufacturer in the world and has the most product installations. So this is a good place to start your education.

There are other tools you will need to know about in order to produce business information. This includes the ability to read and understand the database blueprint. This blueprint is the entity relationship diagram (ERD) or my own favorite tool, the table relationship diagram. You will also need to be aware of database objects such as views, synonyms, and indexes. After you learn how to extract the information, you will want to know how to extract the information quickly. This book contains a chapter that has some common techniques that can be used to enhance the performance of your SQL. This book also covers PL/SQL, which is Oracle’s programming language and is an extremely useful language for accessing object attributes and performing special calculations.

This book contains numerous examples of various SQL techniques. It also has practice questions at the end of most chapters. The questions will allow you to practice the skills immediately after studying them. Appendix B contains answers to the practice questions and provides you with another set of examples to study, copy, and adapt to your work.

The book is meant to be a basic reference book and a how-to manual that covers the most important and common Oracle database topics. It is not the ultimate reference book. It is very difficult to learn SQL from these types of books. This book can be used as a reference book, but it does not totally eliminate the need for true reference books that cover the mundane, once-used-in-five-years topics. The purpose of this book is to help you get the skills to analyze, understand, and efficiently extract information from an Oracle database. Developers, database administrators (DBAs), data administrators (DAs), and business analysts normally have a score of books in their work area. No single book can contain everything about all topics. I want this book to be the first book you go to for answers about the Oracle database because it contains the most frequently used information.

This book is based on courses I have taught at Iowa Western Community College and the University of Nebraska at Omaha and on-site seminars I teach at many major companies. During the past four years, I have helped many students understand the basics of Oracle SQL and the techniques used to extract information. The techniques I cover are the result of 15 years of experience producing business information from relational databases. Students say my books and seminar workbooks are “very practical.” I believe in studying and identifying good design, copying it, modifying it, and calling it my own. Much of this good design is included in this book. I truly hope and believe that you will find the information in this book practical, and I hope you steal it and call it your own.

What Is Oracle?

Oracle is the largest database manufacturer and the second-largest software manufacturer in the world. The company began as a relational database manufacturer. In the beginning, Oracle touted its software as “being able to run on any platform.” This openness has been most attractive to companies, and Oracle has tried to maintain its image as an open product. Oracle was at a good place when industry became extremely interested in moving away from network databases and the mainframe.

By allowing companies to use the client/server paradigm, Oracle had a competitive advantage. It also identified the Internet as the future paradigm. Oracle remains the premier database manufacturer due to its foresight. Oracle continues to increase the power of its database. The current version is called Oracle9i. It’s an object-relational database, which has features that allow developers to model objects within the database. The i in the name means that its database can also support the Internet. To this end, Java programming functions can be placed within the database. Oracle can understand applications using the Java functions. Oracle has recognized that Java is an open product and is one of the more important languages of the Web.

Oracle also has several other database products. Personal Oracle is a smaller version of the Oracle Enterprise edition. It resides on the client (your personal computer) and is designed primarily as a stand-alone database. Oracle also has a small database called Oracle Lite9i, which is designed for use on portable computers and handheld devices.

Oracle has very powerful application-development, report-writing, and database-analysis software. The application-development package is currently called the Internet Developer Suite. It consists of a number of products, two of which are Developer6i Form Builder and Report Builder. Developer6i Form Builder is Oracle’s rapid application development (RAD) software. Report Builder is Oracle’s main report-writing software.

Designer6i is Oracle’s computer-aided software engineering (CASE) product. It is one of the best-selling CASE products in the country. In addition to performing database documentation and object creation, it can be used to generate forms (screens). It is integrated with the other Developer6i products. Even though it will not be covered in this book, Designer is used in many shops as a repository of database information. It is an important tool that can be used to develop and obtain ERDs and other database documentation.

The last remaining development software is JDeveloper. This is a Java-based application-development product somewhat similar to Developer6i Form Builder. It can be used to create Java-based applications, both client/server- and Web-based. Borland supplied the core technology for this product, which is why JDeveloper has a strong resemblance to Borland’s Jbuilder. Oracle hopes that JDeveloper will someday be the dominant Web development tool.

Discoverer is Oracle’s database-analysis product. It is an online analytical processing (OLAP) tool. It has a very easy user interface and is an extremely powerful tool for developing and analyzing business information. The success of this product is due to the ease of creating business objects for analysis, and to the end users’ easy use of the product. I have actually had novice students using the product with less than two hours of training. It is a great tool for empowering your users and reducing the report-writing load of the programmers, DAs, and business analysts.

Finally, Oracle has an array of packaged products for businesses. One such product is Oracle Financials, an entity resource planning (ERP) application that companies use to document work requests, purchase the materials, maintain inventory levels, and manage their fixed assets, accounts payable, and other financial concerns. Oracle also has an array of other entity packages. As you can see, Oracle has a large number of tools and products.

Who Should Use This Book?

At Iowa Western and in my seminars, I allow anyone with Windows (or UNIX) experience to enroll in the courses. I would expect that this is the minimum technical criterion to use this book properly. This is an intro book and will cover most of the commonly used areas of Oracle SQL and PL/SQL. It will also have some elementary coverage of relational and object database components and terminology. This book will be of interest to the following people:

  • Mainframe programmers wanting to upgrade or develop SQL skills
  • Systems analysts, developers, or business personnel interested in data administration
  • Students desiring the skills needed to enter the Oracle market
  • Oracle developers looking for new techniques
  • Developers interested in implementing business objects for analysis
  • Business analysts interested in gaining the skills to analyze corporate databases

You would expect a book such as this to be of interest to readers who desire a technical career. Increasingly, however, it is of importance for accountants, financial analysts, and other nontechnical people to have SQL, database, and online analytical processing (OLAP) skills.

A case in point is the job description for accountants at the company at which I am employed. Accountant job descriptions request Oracle knowledge as a needed skill. With the proliferation of ERP-type databases such as Oracle Financials, SAP, or PeopleSoft, nontechnical personnel are having to derive information from these databases. Knowledge of SQL, PL/SQL, and Discoverer will greatly aid these people, giving them an edge over their coworkers. The worker who can furnish information to management is always a valuable asset. The worker who cannot is not as valuable.

Another class of developers who may reap benefits from this book are Microsoft Access developers. Access is a low-level database product that is highly automated. Over the years, I have received many comments that my courses on SQL and database-object creation help students understand what Access is actually doing.

How Is This Book Organized?

This book contains 16 chapters, a glossary appendix, and an answer appendix. It begins with a discussion of the logical data model, which is used to determine what the database represents, to identify data elements, and to identify the data linkages. This information is needed to extract business information effectively using SQL.

The book then discusses the various Oracle database objects. It is important for you to understand these objects, as many of them will affect the SQL that is written. However, it may not be necessary for you to read the latter portions of Chapter 2 until after the SQL chapters. Chapter 2 covers the use of the Data Definition Language (DDL), which is used to create and maintain database objects such as tables or views. The chapter also discusses how to log on to the Oracle database and enter commands. This section is important for the reader new to Oracle. I place the DDL section at the beginning of the book for the readers who want to understand the database engine components before running the engine.

Chapters 4-9 cover the SELECT command, the language used to extract information from the database. Chapter 10 discusses the use of views and se-quences. Views are a really important tool for creating runtime virtual records. Chapter 11 discusses commands that are contained within the database and that can be used to change the presentation of your information. The Oracle database has limited report-writing tools. There are more powerful tools available on the market, including some fine Oracle tools. However, if your company does not have any of these tools, you will always have the tools discussed in Chapter 11 available.

Chapter 12 discusses performance-tuning techniques, those that are common and often used. Chapter 13 discusses business objects, which are database objects that can be used for analysis or to increase the performance of reports. The final three chapters cover Oracle’s PL/SQL language. This language is a must for the data administrator and can be used to create business objects and entity attributes of interest to the user. The language is also used in Oracle’s Report Builder and Form Builder products.

Appendix A of the book is a glossary, providing you with definitions of important database words. Appendix B contains the answers to exercises that reside at the end of many of the chapters. These questions will allow you to practice the discussed topics. I strongly encourage you to perform the practice questions before checking the answers.

Other Sources of Information

Despite the best attempts by the technical editors, copy editors, and me, errors and misunderstandings will exist in this book. It is extremely humbling for an author/teacher to have his students/readers interpret his writing differently from what he expected. I have tried to be as skilled a technical writer as possible; however, I am sure that I fail occasionally. To remedy this, I intend to maintain a Web site that you can use to raise questions and to view the answers to previous inquiries. The site will contain corrections, explanations, and clarifications. I believe this will be a valuable aid to you. The following is my home Web site: www.oracle-trainer.com.

Another valuable site is the Oracle Developer Tools User Group (ODTUG). This is an organization to which I have belonged for several years. The group has an annual conference, a quarterly newsletter, and a special site where members can post enhancement requests to Oracle. Of special importance are the organization’s list servers, the access to which at the present time is free to everyone. The list servers allow you to post, answer, and receive advice about a variety of Oracle topics, including SQL.

I belong to the SQL, Java, Developer6i, and Discoverer list servers. I monitor the questions and answers all day, and so do a number of other highly skilled professionals (and authors). It is an excellent place to get the latest Oracle information or help with technical issues. The ODTUG site is at www.odtug.com.

If you are truly interested in Oracle, you must visit their Web site frequently. The home page (www.oracle.com) can be very complex and appears to change daily, but it is the source of new Oracle information. Following are some of the Oracle pages I recommend. Before trying to access the pages, you should know that many of them are available only to members of the Oracle Technology Network. This is a free membership, and you may join using Oracle’s Web site. It will enable you to access a great deal of information.

  • Oracle Store. This page is the site for purchasing CD packs of Oracle databases—Personal Oracle, one of these databases, can be used on your personal computer for course work—and for the Internet Development Suite. These tools include Developer6i, JDeveloper, Discoverer, and Designer. I strongly encourage you to purchase the database pack and install Personal Oracle-which runs on only Windows NT/2000 at the present time. You can practice all of the techniques discussed in this book. It is one thing to read about Oracle and another actually to employ the techniques. The current site for the CD packs is https://oraclestore.oracle.com/OA_HTML/ibeCZzdMinisites.jsp?a=b. The database package is called Oracle(R) 9i Release 1 (9.0.1) CD Pack for Microsoft Windows.
  • Oracle Certified Professional Programs. Oracle has a variety of certifications, which are a series of tests about Oracle topics. Two of the more popular are the DBA and Developer, which consist of five tests. The initial test for the DBA and Developer certifications concerns SQL and PL/SQL. This book is an excellent primer for this exam. To learn more about Oracle certifications, visit www.oracle.com/education/certification/.
  • Oracle Certified Professional Assessment Test Download. It is possible to download sample certification exams. You might be interested in how you would perform on a certification exam before and after reading this book. Visit www.oracle.com/education/certification/index.html?sts.html to obtain the sample exams.

Installing the Practice Database and Tools

Oracle offers evaluation copies of their products for free (or at a nominal price). Many of my students would like to practice their skills at home. I recommend that they visit the Oracle site and obtain the latest evaluation production.

The students sometimes find that the most difficult part of learning Oracle is installing the Oracle products and the practice database. This is usually because the student is not familiar with the Oracle products, has ordered the wrong products—Oracle can change the names of products—or has problems with versions, or there may be a variety of other reasons. I encounter this problem every semester. The installation process can seem like a daunting task, but I think these instructions will help you get the products loaded and will get you on your way to learning Oracle.

What Software to Get

First of all, you need to acquire the following product:Personal Oracle -This is the personal-computer version of Oracle. It has all of the features you need for this book. It is a much smaller version than the Oracle9i Enterprise edition. The Enterprise Edition may also be used with this book, but it requires Windows NT, 2000, or XP.

If you work for a company that has Oracle installed, you might want to use your company’s installation to practice. Your company will likely have a version of the Enterprise edition of Oracle. This product can also be used, and everything within this book applies to the Enterprise edition.

Versions have differences that might be of interest to the experienced Oracle user. The differences in the SQL basics are not generally substantial, and the version differences are not as important for the new student. If you have access to older versions of Oracle, they can be used with this book. However, I would try to acquire the latest database and product versions.

How to Get the Software

At the time of this writing, Oracle offers several options, the best of which is to purchase the Oracle Database CD Pack or the Oracle Tools CD Pack (which is not covered in this book). Each of the packs currently costs $39.95 and contains the full range of Oracle databases and tools.

Individual Oracle products may also be downloaded from the Oracle Tech Network (http://technet.oracle.com/). You must register, but the registration is free. The only disadvantage of the download process is that files are very large, and you will need a high-speed connection. If you do not have access to such a connection, the CDs are your only option. The products on the CDs and from the Oracle Tech Network are evaluation copies. They are licensed for a 30-day evaluation. However, this small period of time will help you develop skills that can be used in the workplace. In my opinion, the evaluation products are an excellent tool for the student.

In my opinion, the easiest method to obtain the software is to purchase the CD packs. However, several other sources are available:

  • I have had students contact the local Oracle sales representative. This can be an effective mechanism when you have trouble ordering the CD packs.
  • Oracle has a program known as the Oracle Academic Initiative, which allows any qualified academic institution to purchase all Oracle products for $500 per year. It also allows the institution to copy the software for its students. If you are attending an institution enrolled in this program, you should be able to get the software through the school’s IS department.

Setting Up Personal Oracle

The first product to install is the Oracle database. This is an automated pro-cess. Simply place the proper CD into the CD-ROM and follow the instructions. I have seen student installations go relatively easily, and I have seen their installations be very difficult. The difficulty has varied with the different releases of the product. The students did not cause the problems. Incorrect load scripts from Oracle caused the problems. If you encounter difficulties, don’t get frustrated; join the large crowd of new users who have had problems and overcame them. One thing you can do is check my Web site (www.oracle-trainer.com), where I will attempt to have additional instructions or advice available. Another good source is the list server at the Oracle Developer Tools Users Group (www.odtug.com) site. Of course, the best source is Oracle itself.

After installing the database, you must check to see if the database is operational. The following instructions pertain to Oracle 9i.

  1. Before the database can be used, it must be started. When the database is installed, settings are made that start the Oracle database whenever Windows is launched. To determine if the database has been started, check the OracleserviceORCL service. You can view this service by using the Services menu option on the Control Panel, which will display a Windows dialog box. Starting Oracle causes some of your computer’s memory to be used. If you are not using Oracle all the time, you may want to stop the database. This can be done on the Service dialog box.
  2. Launch SQL*Plus. Instructions are contained in Chapter 2. There are two default user IDs and passwords:
    – scott/tiger—This is a default Oracle training ID. It contains the normal privileges allowed for a developer. Your practice database will be installed on this user ID.
    – system/manager—This is the default Oracle master ID. It has the ability to perform all database functions.

If you cannot log on to SQL*Plus, you might not have started the database, you might have entered an incorrect user ID/password, or you might not have installed the Oracle database properly.

What’s Next?

The next chapter will introduce you to relational database terminology and theory. The chapter will discuss entity relationship diagrams, table relationship diagrams, and the principles of normalization. The chapter is important in that it will help you understand how to identify needed data items within a relational database. If you cannot locate the database components you need, you will certainly not be an effective user of SQL.

0201752948P08282002

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted August 15, 2008

    Excellent all round book

    Perfect for someone who is starting into Oracle I use 10g this book is a perfect reference in all categories i.e. PL/SQL etc. Combined with standard Oracle version guides, this is a perfect first to go book.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)