oracle | Page 2

. . . .308
,mast_ora_sqlTOC.fm.10634 Page vii Wednesday, March 27, 2002 2:23 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved.Preface|
ix
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 applica-
tion 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 impor-
tant and useful of Oracle’s SQL features, but we show ways to apply them to solve spe-
cific problems.
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, hierarchi-
cal queries, grouping operations, analytical queries, etc.
• 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 Oracle9i, such as new date and time fea-
tures, ANSI-compliant joins, and new grouping and analytical functions.
• Use best practices to write efficient, maintainable SQL queries.
Audience for This Book
This book is for Oracle developers and database administrators. 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 pro-
ductivity, allowing you to get more done in less time, and with increased certainty
that the SQL statements you write are indeed correct.
,ch00.8321 Page ix Wednesday, March 27, 2002 2:18 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved. x|
Preface
Platform and Version
We used Oracle8i(releases 8.1.6 and 8.1.7) and Oracle9i(release 9.0.1) in this book.
We’ve covered many of Oracle9i’s important new SQL features, including ANSI-
standard join syntax, new time/date datatypes, and various analytical functions.
Most of the concepts, syntax, and examples apply to earlier releases of Oracle as
well. We specifically point out the new Oracle9ifeatures.
Structure of This Book
This book is divided into 14 chapters:
• Chapter 1,Introduction to SQL, 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,The WHERE Clause, describes ways to filter data in your SQL state-
ments. 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,Joins, describes constructs used to access data from multiple, related
tables. The important concepts of inner join and outer join are discussed in this
chapter. The new ANSI-compliant join syntax introduced in Oracle9iis also
discussed.
• Chapter 4,Group Operations, shows you how to generate summary informa-
tion, 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,Subqueries, shows you how to use correlated and noncorrelated sub-
queries and inline views to solve complex problems that would otherwise require
procedural code together with more than one query.
• Chapter 6,Handling Temporal Data, talks about handling date and time infor-
mation in an Oracle database. Learn the tricks and traps of querying time-based
data. Also learn about Oracle9i’s many new date and time datatypes.
• Chapter 7,Set Operations, shows you how to use UNION, INTERSECT, and
MINUS to combine results from two or more independent component queries
into one.
• Chapter 8,Hierarchical Queries, shows you how to store and extract hierarchi-
cal information (such as in an organizational chart) from a relational table. Ora-
cle provides several features to facilitate working with hierarchical data.
,ch00.8321 Page x Wednesday, March 27, 2002 2:18 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved.Preface|
xi
• Chapter 9,DECODE and CASE, talks about two very powerful yet simple fea-
tures of Oracle SQL that enable you to