Copyright

Copyright 2004, 2002 O'Reilly Media, Inc.

Printed in the United States of America.

Published by O'Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.

O'Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://safari.oreilly.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or corporate@oreilly.com.

Nutshell Handbook, the Nutshell Handbook logo, and the O'Reilly logo are registered trademarks of O'Reilly Media, Inc. Mastering Oracle SQL , the image of lantern flies, and related trade dress are trademarks of O'Reilly Media, Inc.

Oracle and all Oracle-based trademarks and logos are trademarks or registered trademarks of Oracle Corporation in the United States and other countries . O'Reilly Media, Inc., is independent of Oracle Corporation.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O'Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps.

While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

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.