Mastering Oracle SQL, 2nd Edition

Preface

SQL is the language for accessing a relational database. SQL provides a set of statements for storing and retrieving data to and from a relational database. It has gained steadily in popularity ever since the first relational database was unleashed upon the world. Other languages have been put forth, but SQL is now accepted as the standard language for almost all relational database implementations, including Oracle.

SQL is different from other programming languages because it is nonprocedural. Unlike programs in other languages, where you specify the sequence of steps to be performed, a SQL program (more appropriately called a SQL statement) only expresses the desired result. The responsibility for determining how the data will be processed to generate the desired result is left to the database management system. The nonprocedural nature of SQL makes it easier to access data in application programs.

If you are using an Oracle database, SQL is the interface you use to access the data stored in your database. SQL allows you to create database structures such as tables (to store your data), views, and indexes. SQL allows you to insert data into the database, and to retrieve that stored data in a desired format (for example, you might sort it). Finally, SQL allows you to modify, delete, and otherwise manipulate your stored data. SQL is the key to everything you do with the database. It’s important to know how to get the most out of that interface. Mastery over the SQL language is one of the most vital requirements of a database developer or database administrator.

Why We Wrote This Book

Our motivation for writing this book stems from our own experiences learning how to use the Oracle database and Oracle’s implementation of the SQL language. Oracle’s SQL documentation consists of a reference manual that doesn’t go into details about the practical usefulness of the various SQL features that Oracle supports. Nor does the manual present complex, real-life examples.

When we looked for help with SQL in the computer book market, we found that there are really two types of SQL books available. Most are the reference type that describe features and syntax, but that don’t tell you how to apply that knowledge to real-life problems. The other type of book, very few-in-number, discusses the application of SQL in a dry and theoretical style without using any particular vendor’s implementation. Since every database vendor implements their own variation of SQL, we find books based on “standard” SQL to be of limited usefulness.

In writing this book, we decided to write a practical book focused squarely on Oracle’s version of SQL. Oracle is the market-leading database, and it’s also the database on which we’ve honed our SQL expertise. In this book, we not only cover the most important and useful of Oracle’s SQL features, but we show ways to apply them to solve specific problems.

What’s New in Oracle SQL?

When we wrote the first edition of this book, Oracle9i had just come out, and we managed to cover some of the interesting and new features in that release of the database. Now, Oracle Database 10g has just been released, and there are even more new features to talk about:

• A new, MODEL clause has been added to the SELECT statement, enabling you to write queries that perform spreadsheet-like calculations against multidimensional arrays created from data you select from the database.

• Oracle has added support for using regular expressions from SQL, and with a vengeance. Not only can you use regular expressions to select data, but also to manipulate data in various, useful ways. For example, you can perform regular expression search-and-replace operations. No other database vendor that we know of offers such powerful, regular expression functionality.

• XML is everywhere these days, and that hasn’t gone unnoticed in the world of SQL. The ANSI/ISO folk have created the SQL/XML standard, which defines mechanisms for selecting relational data and presenting it in XML form. Oracle supports this standard, which involves several, new SQL functions. Oracle also now supports XML as a native data type.

These are just the big features, which, of course, we cover in this second edition. In addition, we cover many small updates to Oracle SQL, such as the multiset union operators that enable you to perform set operations involving nested table collections.

Finally, we’ve worked carefully together as a team, not only with each other, but also with our editor, to ensure that all examples in this book are drawn from a single data set. You’ll be able to download that data set from this book’s catalog page. You can then use it to follow along with our examples.

Objectives of This Book

The single most important objective of this book is to help you harness the power of Oracle SQL to the maximum extent possible. You will learn to:

• Understand the features and capabilities of the SQL language, as implemented by Oracle.

• Use complex SQL features, such as outer joins, correlated subqueries, hierarchical queries, grouping operations, and analytical queries.

• Use DECODE and CASE to implement conditional logic in your SQL queries.

• Write SQL statements that operate against partitions, objects, and collections, such as nested tables and variable arrays.

• Use the new SQL features introduced in Oracle Database 10g, such as regular expressions and interrow calculations.

• Use best-practices to write efficient, maintainable SQL queries.

One topic that is important to us and many of our readers, but which is not explicitly discussed in this book is SQL tuning. Tuning tips are sprinkled throughout the book, but we do not include a chapter on tuning for the following reasons:

• Tuning is a large topic, and reasonable coverage of SQL tuning would easily double or triple the size of this book.

• There are already many excellent Oracle-specific and general-purpose tuning books on the market, whereas there are very few books (in our opinion, exactly one) that thoroughly explore the feature set of Oracle SQL.

• In many ways, mastery of Oracle’s SQL implementation is the most important tool in your tuning toolkit.

With this book under your belt, you will be less likely to write SQL statements that perform badly, and you will be able to employ multiple strategies to rework existing statements.

Audience for This Book

This book is for Oracle developers, database administrators, and anyone who needs access to data stored in an Oracle database for reporting or ad-hoc analysis. Whether you are new to the world of databases or a seasoned professional, if you use SQL to access an Oracle database, this book is for you. Whether you use simple queries to access data or embed them in PL/SQL or Java programs, SQL is the core of all data access tasks in your application. Knowing the power and flexibility of SQL will improve your productivity, allowing you to get more done in less time, and with increased certainty that the SQL statements you write are indeed correct.

Platform and Version

We used Oracle Database 10g in writing this book. We’ve covered many of Oracle Database 10g’s important new SQL features, including regular expressions, hierarchical query features, object and collection functionality, and interrow calculations. Most of the concepts, syntax, and examples apply to earlier releases of Oracle as well. We specifically point out the new Oracle Database 10g features.

Structure of This Book

This book is divided into 18 chapters and 1 appendix:

• Chapter 1, introduces the SQL language and describes its brief history. This chapter is primarily for those readers who have little or no prior SQL experience. You’ll find simple examples of the core SQL statements (SELECT, INSERT, UPDATE, and DELETE) and of SQL’s basic features.

• Chapter 2, describes ways to filter data in your SQL statements. You’ll learn to restrict the results of a query to the rows you wish to see, and restrict the results of a data manipulation statement to the rows you wish to modify.

• Chapter 3, describes constructs used to access data from multiple, related tables. The important concepts of inner join and outer join are discussed in this chapter.

• Chapter 4, shows you how to generate summary information, such as totals and subtotals, from your data. Learn how to define groups of rows, and how to apply various aggregate functions to summarize data in those groups.

• Chapter 5, shows you how to use correlated and noncorrelated subqueries and inline views to solve complex problems that would otherwise require procedural code together with more than one query.

• Chapter 6, talks about handling date and time information in an Oracle database. Learn the tricks and traps of querying time-based data.

• Chapter 7, shows you how to use UNION, INTERSECT, and MINUS to combine results from two or more independent component queries into one.

• Chapter 8, shows you how to store and extract hierarchical information (such as in an organizational chart) from a relational table. Oracle provides many features to facilitate working with hierarchical data, including several new features introduced in Oracle Database 10g.

• Chapter 9, talks about two very powerful yet simple features of Oracle SQL that enable you to simulate conditional logic in what is otherwise a declarative language.

• Chapter 10, discusses the issues involved with creating and accessing partitioned tables using SQL. Learn to write SQL statements that operate on specific partitions and subpartitions.

• Chapter 11, explores the integration of SQL and PL/SQL. This chapter describes how to call PL/SQL stored procedures and functions from SQL statements, and how to write efficient SQL statements within PL/SQL programs.

• Chapter 12, explores the object-oriented aspects of the Oracle database server, including object types and collections.

• Chapter 13, deals with complex grouping operations used mostly in decision support systems. We show you how to use Oracle features such as ROLLUP, CUBE, and GROUPING SETS to efficiently generate various levels of summary information required by decision-support applications. We also discuss the grouping features that enable composite and concatenated groupings, including the GROUP_ID and GROUPING_ID functions.

• Chapter 14, deals with analytical queries and analytic functions. Learn how to use ranking, windowing, and reporting functions to generate decision-support information.

• Chapter 15, talks about best practices that you should follow to write efficient and maintainable queries. Learn which SQL constructs are the most efficient for a given situation. For example, we describe when it’s better to use WHERE instead of HAVING to restrict query results. We also discuss the performance implications of using bind variables vis-à-vis literal SQL.

• Chapter 16, explores how the Oracle server can store XML documents, features used to navigate, search, and extract content from XML documents, and functions used to generate XML documents from ordinary tables.

• Chapter 17, shows how to write and interpret regular expressions for performing advanced text searches and substitutions.

• Chapter 18, introduces the new, MODEL clause, which lets you manipulate relational data as if it were a big, multidimensional, spreadsheet (Oracle prefers the term model). Model queries enable you to solve problems using a single SQL statement that previously would have required you to download data to a third-party, spreadsheet program such as Microsoft Excel.

• The Appendix, describes the SQL89 join syntax, and Oracle’s proprietary, outer-join syntax. Only this syntax was available for joins until the release of Oracle9i Database, which introduced support for the newer, and better, SQL92 join syntax.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Used for filenames, directory names, table names, field names, and exampl URLs. It is also used for emphasis and for the first use of a technical term.

Constant width

Used for examples and to show the contents of files and the output of commands. Also used for column names, XML element names, regular expressions, SQL literals mentioned in the text, and function names.

Constant width italic

Used in syntax descriptions to indicate user-defined items.

Constant width bold

Indicates user input in examples showing an interaction. Also indicates emphasized code elements to which you should pay particular attention.

Constant width bold italic

Used in code examples to emphasize aspects of the SQL statements, or results, that are under discussion.

UPPERCASE

In syntax descriptions, indicates keywords.

lowercase

In syntax descriptions, indicates user-defined items, such as variables.

[ ]

In syntax descriptions, square brackets enclose optional items.

{ }

In syntax descriptions, curly brackets enclose a set of items from which you must choose only one.

|

In syntax descriptions, a vertical bar separates the items enclosed in curly or square brackets, as in {TRUE | FALSE}.

. . .

In syntax descriptions, ellipses indicate repeating elements.

Tip

Indicates a tip, suggestion, or general note. For example, we use notes to point you to useful new features in Oracle Database 10g.

Warning

Indicates a warning or caution. For example, we’ll tell you if a certain SQL clause might have unintended consequences if not used carefully.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: "Mastering Oracle SQL, Second Edition, by Sanjay Mishra and Alan Beaulieu. Copyright 2004 O’Reilly Media, Inc., 0-596-00632-2.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at:

[e-mail].

Comments and Questions

We have tested and verified the information in this book to the best of our ability, but you may find that features have changed or that we have made mistakes. If so, please notify us by writing to:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

[phone] (in the United States or Canada)

[phone] (international or local)

[phone] (fax)

You can also send messages electronically. To be put on the mailing list or request a catalog, send email to:

[e-mail]

To ask technical questions or comment on the book, send email to:

[e-mail]

O’Reilly has a web site for this book, where you can find examples and errata (previously reported errors and corrections are available for public view there). You can access this page at:

http://www.oreilly.com/catalog/0596006322

For more information about this book and others, see the O’Reilly web site:

http://www.oreilly.com

Acknowledgments

We are indebted to a great many people who have contributed in the development and production of this book. We owe a huge debt of gratitude to Jonathan Gennick, the editor of the book. Jonathan’s vision for this book, close attention to details, and exceptional editing skills are the reasons this book is here today.

Our sincere thanks to our technical reviewers: Diana Lorentz, Jason Bucata, Trudy Pelzer, and Peter Linsley, who generously gave their valuable time to read and comment on a draft copy of this book. Their contributions have greatly improved its accuracy, readability, and value.

This book certainly would not have been possible without a lot of hard work and support from the skillful staff at O’Reilly, including Ellie Volckhausen and Emma Colby, the cover designers; David Futato, the interior designer; Julie Hawks, who converted the files; Matt Hutchinson, the production editor; Rob Romano and Jessamyn Read, the illustrators; and Sarah Sherman, Marlowe Shaeffer, and Claire Cloutier, who provided quality control.

From Sanjay

I would like to thank my coauthor Alan and my coauthor/editor Jonathan Gennick for constant cooperation and smooth execution during the first as well as the second edition of this book.

My adventure with Oracle’s database started in the Tribology Workbench project at Tata Steel, Jamshedpur, India. Sincere thanks to my co-workers in the Tribology Workbench project for all the experiments and explorations we did during our learning days with Oracle. Ever since, Oracle database technology has become a way of life for me.

Special thanks the readers of the first edition whose feedback, comments, questions, and suggestions helped improve the second edition of the book. Sincere thanks to my current and previous co-workers for their support and encouragement.

Last, but not the least, I thank my wife, Sudipti, for her support, understanding, and constant encouragement.

From Alan

I would like to thank my coauthor Sanjay and my coauthor/editor Jonathan Gennick for helping to make the second edition of this book a reality. I would also like to thank the many readers of our first edition who pointed out errors, asked questions, and made suggestions; with your help, our second edition is a much better book.

Most of all, I would like to thank my wife, Nancy, for her support, patience, and encouragement, and my daughters, Michelle and Nicole, for their love and inspiration.