Register your product to gain access to bonus material or receive a coupon.
Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts
- By John Adolph Palinski
- Published Aug 8, 2002 by Addison-Wesley Professional.
Book
- Sorry, this book is no longer in print.
About
Features
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
Description
- Copyright 2003
- Dimensions: 7-3/8" x 9-1/4"
- Pages: 528
- Edition: 1st
- Book
- ISBN-10: 0-201-75294-8
- ISBN-13: 978-0-201-75294-6
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 database. 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). 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.
Sample Content
Downloadable Sample Chapter
Click below for Sample Chapter(s) related to this title:
Sample
Chapter 9
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. 0201752948T03282002
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 on-line 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.
The 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 only covers 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 blue print. This blue print is the Entity Relationship Drawing (ERD) or my own favorite tool, the Table Relationship Drawing. 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”. The 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. This is Oracle’s programming language. This 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 is does not totally eliminate the need for true reference books that cover the mundane, once used in five-year 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.
The book is based upon courses I have taught at Iowa Western Community College, 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 fifteen 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 their 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. Oracle9i is an object-relational database, which has features that allow developers to model objects within the database. The “i” in the name means that Oracle intends 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 PC) and is designed primarily as a stand-alone database. Oracle also has a small database called Oracle Lite9i. This product is designed for use on mobile PC’s and hand held 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. Developer 6i Form Builder and Report Builder are two of its products. Developer6i Form Builder is Oracle’s rapid application development (RAD) software. Report Builder is Oracle’s main report writing software.
Designer 6i 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 Developer 6i 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. It 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 on-line analytical processing (OLAP) tool. It has a very easy user interface. It 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 the easy use of the product by end users. I have actually had novice students using the product with less than 2 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. An example of this type product is Oracle Financials. This product is an “entity resource planning” (ERP) type application used by companies 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 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 properly use this book. 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 for the following people:
You would expect a book such as this to be of interest to readers that desire a technical career. Increasingly it is of importance for accountants, financial analysts, and other non-technical people to have SQL, database, and “on-line-analytical-processing” (OLAP) skills.
A case in point is the job description for accountants at the company for 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 People Soft, non-technical personnel are having derive information from these databases. Knowledge of SQL, PL/SQL, and Discoverer will greatly aid these people. This knowledge will give these personnel an edge over other co-workers. The worker that can furnish information to management is always a valuable asset. The worker that cannot is not as valuable.
Another class of developers that may reap benefits from this book are Microsoft Access developers. Access is a low level database product that is highly automated. I have had many comments over the years 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 sixteen chapters, a glossary appendix, and an answer appendix. The book begins with a discussion of the logical data model. This model is used to determine what the database represents, to identify data elements, and to identify the data linkages. This information is needed to effectively extract business information using SQL.
The book then discusses the various Oracle database objects. It is important for you to understand these objects. Many of them will affect the SQL that is written. However, it may not be necessary for you to read the later portions of Chapter 2 until after the SQL chapters. Chapter 2 covers the use of the Data Definition Language (DDL). This language is used 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 only place the DDL section at the beginning of the book for the readers that want to understand the database engine components before running the engine.
Chapters 4–9 cover the Select command. This is the language used to extract information from the database. Chapter 10 discusses the use of views and sequences. Views are a really important tool for creating run time virtual records. Chapter 11 discusses commands contained within the database that can be used to change the presentation of your information. The Oracle database has limited report-writing tools. There are many more powerful tools available, 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. This chapter covers the more common and often used techniques. Chapter 13 discusses business objects. Business objects are database objects that can be used for analysis or to increase the performance of reports. The final three chapters will cover Oracle’s PL/SQL language. This language is a must for the data administrator. It 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 1 of the book is a glossary. This appendix has definitions of important database words. Appendix 2 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.
CONVENTIONS
There are two conventions that will be used throughout this book. These are:
Bolded Text —The first occurrence of a keyword that will be defined in the glossary.
Italics—Identifies places in a command syntax template that will require a user defined value.
OTHER SOURCES OF INFORMATION
Despite the best attempts by the technical editors, copy editors, and myself, 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 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 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. They have 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. At the present time access to the list servers 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. There are also a number of other highly skilled professionals (and authors) who monitor the list servers during the day. 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 often appears to change daily. However, it is important that you visit the site since it is the source of new Oracle information. Below are some of the Oracle pages I recommend. Before trying to access the pages you should know that many of them are only available to members of the Oracle Technology Network. This is a free membership and you may join using Oracle’s Web site. You will be able to access a great deal of information using this membership.The Oracle Store page is the site for purchasing CD packs of Oracle databases (Personal Oracle, one of these databases, can be used on your PC for course work) and for the Internet Development Suite. These tools include Developer 6i, JDeveloper, Discoverer, and Designer. I strongly encourage you to purchase the database pack and install Personal Oracle (It only runs on 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 to actually employ the techniques. The current site for the CD packs is: https://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=11536&jfn=CAB2E7AB8B81E955A27B05
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. Certifications are a series of tests about Oracle topics. Two of the more popular are the DBA and Developer certifications. The certifications consist of five tests. The initial test for the both 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: http://www.oracle.com/education/certification/
Oracle Certified Professional Assessment Test Download. It is possible to download sample certification exams. You may be interested in how you would perform on a certification exam before and after reading this book. Visit: http://www.oracle.com/education/certification/index.html?sts.html to obtain the sample exams.
0201752948P03282002
Index
Click below to download the Index file related to this title:
Index