Rischert - Oracle SQL by Example, Fourth Edition

Upper Saddle River, NJ Boston Indianapolis San Francisco New York
Toronto Montreal London Munich Paris Madrid Cape Town Sydney
Tokyo Singapore Mexico City

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 the publisher was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals.

The author and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein.

The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales, which may include electronic versions and/or custom covers and content particular to your business, training goals, marketing focus, and branding interests. For more information, please contact:

U.S. Corporate and Government Sales
(800) 382-3419

For sales outside the United States please contact:

International Sales

Visit us on the Web: informit.com/ph

Library of Congress Cataloging-in-Publication Data:

Rischert, Alice.
Oracle SQL by example / Alice Rischert.
p. cm.
ISBN 978-0-13-714283-5 (pbk. : alk. paper) 1. SQL (Computer program language) 2. Oracle
(Computer file) I. Title.
QA76.73.S67R57 2009
005.13'3dc22
2009022733

Copyright 2010 Pearson Education, Inc.

All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, write to:

Pearson Education, Inc.
Rights and Contracts Department
501 Boylston Street, Suite 900
Boston, MA 02116
Fax (617) 671-3447

ISBN-13: 978-0-137-14283-5
ISBN-10: 0-137-14283-8

Text printed in the United States on recycled paper at Edwards Brothers in Ann Arbor, Michigan
First printing August 2009

Editor-in-Chief: Mark Taub
Acquisitions Editor: Trina MacDonald
Development Editor: Michael Thurston
Managing Editor: Kristy Hart
Technical Editors: Oleg Voskoboynikov
Robert Vollman
Project Editors: Julie Anderson
Jovana San Nicolas-Shirley
Copy Editor: Kitty Wilson
Indexer: Alice Rischert
Proofreader: Sheri Cain
Publishing Coordinator: Olivia Basegio
Interior Designer: Gary Adair
Cover Designer: Anne Jones
Compositors: Nonie Ratcliff
Gloria Schurick

To my daughter, Kirsten, and my parents, Albert and Hilde

The year was 1680; the place was the Levanta region on the eastern shores of the Mediterranean. The political climate was stable enough to support peaceful trade among nations, and seaports were bustling with merchants from many countries. These merchants found trade outside their native land profitable, but they faced many challenges in travel, as well as in adapting to foreign cultures and seemingly strange customs. One major challenge was communication. As a seller of goods, a key sales technique is verbally extolling the virtues of your product to a potential customer, but how do you market to a target audience whose language you have not mastered? Sign language and written symbols can be quite effective but are not nearly as personal and understandable (and beneficial to sales) as well-chosen words. The problem with spoken language in a melting pot like the Levant is that there are many languages to master.

The solution that emerged to this problem nearly a half-century ago has been used by people of many lands throughout history: When gathered in a multilingual environment, speak a common language. In the Levant, the common language that evolved was a combination of Italian, Spanish, French, Greek, Arabic, and Turkish. This language, called Lingua Franca, became a standard in many ports, as merchants used it to successfully communicate with their customers and fellow traders. Those who learned and mastered this language became the most effective and successful businesspeople. Although Lingua Franca is now extinct, the term lingua franca is still applied to any common language used for communication between people of diverse native languages.

Todays information technology (IT) industry uses the concept of lingua franca. Businesses solve their data management requirements by using relational databases. The challenges that companies face when using relational databases are mainly in creating flexible and efficient human interfaces. Developing customized user interfaces often takes much in the way of effort and resources if it is to properly fulfill the requirements of the business.

IT professionals who are tasked with creating modern database systems must rely on their training and expertise, both in designing the proper database storage objects and in programming the most efficient application programs. In the recent past, developers of relational database systems have created application programs using procedural programming languages such as COBOL, Fortran, and C. Regardless of the procedural language used, there is an additional language embedded in these procedural programs. This language offers a standard way to define and manipulate the data in a relational database and is, therefore, the lingua franca of database technology: Structured Query Language (SQL).

With SQL, developers using diverse programming languages, database software, hardware, and operating systems can effectively communicate with the database in a completely standard way. A standard language means that the written code is understood easily and can be supported fully and enhanced quickly. This is the promise of SQL, and this promise has been fulfilled successfully for decades, in countless relational database application programs.

The problem today is not the lack of a common language, as was the case in the days before a lingua franca. SQL is the common language. The problem today is in the assimilation and proper use of this language. As the computer industry continues its logarithmic growth, the number of application developers increases similarly. Each new developer who writes programs that need to access a relational database must be trained in the lingua franca. In the past, with a small number of new developers, this was a manageable feat for most organizations. When a new developer was in training, she or he would learn both a procedural programming language and the database language embedded in itSQL.

Today, IT solutions are leaning toward object orientation. Object-oriented analysis, design, and programming have come of age and, according to popular opinion, are now the best tools for creating computer systems. As a result, object-oriented languages, such as Java, C#, and C++, are replacing the traditional procedural languages for building new systems. From the perspective of a database management system, this is merely a shift in the main programming language that the developer uses to create the applications user interface. The core database language, SQL, is still required and is of key importance.