Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SQL Clearly Explained Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SQL Clearly Explained Third Edition Jan L. Harrington Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Morgan Kaufmann Publishers is an imprint of Elsevier. 30 Corporate Drive, Suite 400, Burlington, MA 01803, USA This book is printed on acid-free paper. © 2010 E LSEVIER I NC . All rights reserved. No part of this publication may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or any information storage and retrieval system, without permission in writing from the publisher. Details on how to seek permission, further information about the Publisher’s permissions policies and our arrangements with organizations such as the Copyright Clearance Center and the Copyright Licensing Agency, can be found at our website: www.elsevier.com/permissions. This book and the individual contributions contained in it are protected under copyright by the Publisher (other than as may be noted herein). Notices Knowledge and best practice in this field are constantly changing. As new research and experience broaden our understanding, changes in research methods, professional practices, or medical treatment may become necessary. Practitioners and researchers must always rely on their own experience and knowledge in evaluating and using any information, methods, compounds, or experiments described herein. In using such information or methods they should be mindful of their own safety and the safety of others, including parties for whom they have a professional responsibility. To the fullest extent of the law, neither the Publisher nor the authors, contributors, or editors, assume any liability for any injury and/or damage to persons or property as a matter of products liability, negligence or otherwise, or from any use or operation of any methods, products, instructions, or ideas contained in the material herein. Library of Congress Cataloging-in-Publication Data Harrington, Jan L. SQL clearly explained / Jan L. Harrington. -- 3rd ed. p. cm. Includes indexes. ISBN 978-0-12-375697-8 1. SQL (Computer program language) I. Title. QA76.73.S67H37 2010 005.13'3--dc22 2010009181 British Library Cataloguing-in-Publication Data A catalogue record for this book is available from the British Library. For information on all Morgan Kaufmann publications, visit our Web site at www mkp.com or www.elsevierdirect.com Printed in the United States of America 10 11 12 13 14 5 4 3 2 1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. xiii Preface to the Third Edition If you have had any contact with a relational database, then it is very likely that you have seen the letters “SQL.” SQL (Struc- tured Query Language) is a computer language designed to manipulate relational databases. You can use it to dene a da- tabase’s structure, to modify data, and to retrieve data. is book has been written to give you an in-depth introduc- tion to using SQL, providing a gentle but complete approach to learning the language. You will learn not only SQL syntax, but also how SQL works. Understanding the “how” as well as the “what” will help you create SQL statements that execute as quickly as possible. e elements of the SQL language covered in the rst four parts of this book are based on those parts of the SQL standard that are for use with pure relational databases. Part V covers two non-relational extensions (XML and object-relational ca- pabilities) that have been part of SQL since 2003. Virtually all database management systems that support SQL will provide the bulk of what you will nd in Parts I–IV; implementations of the features in Part V are less common and tend to vary from the standard. ere have been some substantial enhancements to the SQL standard since the second edition of this book, both in the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. xiv Preface relational core features and the non-relational features. ese features have been integrated throughout this third edition. Organization of This Book e ve parts of this book take you from theory to practice: ◊ Part I: e theoretical material underlying relational databases and SQL has been moved into two chapters at the beginning of the book. In previous editions, the material in Chapter 2 (relational algebra) was scattered throughout the book. is organization should make it easier to nd. e third chapter in Part I provides an overview of SQL environments. ◊ Part II: Part II covers interactive SQL retrieval. At rst, this might seem backwards. Why discuss retrieving data before creating a database and getting data into that da- tabase? ere is actually a very good reason for this. SQL presents someone trying to learn the language with a bit of a catch-22. You need to know how to retrieve data before you can modify it, because modifying data means nding the data you want to change. On the other hand, you need to be able to create a database and enter some data before you have some data on which you can perform retrievals. Like Yossarian trying to meet with Major Major, it doesn’t seem that you can win! e best alternative is to have someone who knows how to do it create a sample database and load it with data for you. en you can learn to query that database and carry those techniques over to modifying data. At that point, you’ll have an understanding of SQL basics and will be ready to learn to create databases. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Preface xv ◊ Part III: Part III discusses creating and managing data- base structure. It also covers non-data elements in the database environment, such as managing users/ user ac- counts and transaction control. ◊ Part IV: When SQL-based database environments are being developed, programmers and database adminis- trators do a lot of work using a command-line interface. ere are, however, at least two reasons why SQL pro- gramming is very common: o e typical end-user should not (or cannot) work directly from the SQL command line. We there- fore create application programs to isolate them from direct interaction with the SQL command processor by writing application programs for them to use. o In many cases, there are actions the database should perform in specic circumstances. We don’t want to require users to remember to do these actions, so we write blocks of program code that are stored within the database to be executed automatically at the appropriate time. Part IV introduces several techniques for SQL program- ming: embedded SQL (using a high-level host lan- guage), dynamic SQL, and triggers/stored procedures. ese chapters teach you syntax of SQL programming constructs, but do not teach programming. ◊ Part V: Part V discusses the non-relational extensions that have been added to the SQL standard: XML and object-relational capabilities. Just as Chapter 1 pres- ents a brief introduction to the relational data model, Chapter 18 covers object-oriented concepts, including the dierences between pure object-oriented databases Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. xvi Preface and object-relational databases. Chapter 19 then looks at SQL’s object-relational features. Database Software Much of today’s commercial database software is very ex- pensive and requires expensive hardware on which to run. If you are looking for a database management system for your own use, you needn’t purchase anything should you choose not to. ere are at least two open-source products that will run on reasonable hardware congurations: mySQL (http:// www. mysql.com) and PostgreSQL (http://www.postgresql. org). Both are certainly used in commercial settings, but can also function well as learning environments. Distributions are available for Windows, Linux, and Mac OS X. e SQL commands to create the sample database used in the rst four parts of this book and the SQL commands to insert data into those tables can be downloaded from the Morgan Kaufmann Web site. Teaching Materials If you are using this book as a college text (perhaps jointly with its companion volume, Relational Database Design and Imple- mentation Clearly Explained), you can nd teaching support materials on the Morgan Kaufmann Web site. ese include a sample syllabus, assignments (and where appropriate, solu- tions), a project description, and exams. Acknowledgements Although an author spends a lot of time alone in front of the computer, no book can come into being without the coopera- tion and hard work of many people. It may be my name on Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Preface xvii the cover, but without the people at Morgan Kaufmann, you wouldn’t be holding this book right now. First I’d like to thank the editorial sta, Rick Adams (Senior Acquisitions Editor) and Heather Scherer (Assistant Editor). You’re a joy to work with (as always). Second, I am forever grateful for the production sta, who have done everything they can to make my life easier and to produce a great volume: Anne McGee (Project Manager), Joanne Blank (Designer), and Carol Lewis (Copyeditor). I also can’t forget my support sta: my mother, my son, and the four fur kids. (Now, if the kittens could just distinguish between my leg and a scratching post, my world would be at peace.) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 1 3 You don’t need to be a database designer to use SQL success- fully. However, you do need to know a bit about how rela- tional databases are structured and how to manipulate those structures. is chapter therefore will acquaint you with the basic elements of the relational data model and its terminolo- gy. We’ll nish by looking at the design of the sample database used throughout this book. 1 A database is a place where we store data, but there is more to it than that: We also store information about the relationships between pieces of data. e organization of a database is a logi- cal concept rather than a physical one. Yes, there are les that store the data in a database, but the physical structure of those les usually isn’t a concern for those who use the data. e software that organizes, stores, retrieves, and analyzes data- base data is known as a database management system (DBMS). It isolates the user from the physical data storage mechanisms and structures and lets the user work with data in terms of the logical structure of the data. 1 If you have been reading this book’s companion volume, Relational Database Design and Implementation Clearly Explained, then you will be familiar with the concepts presented in this chapter. You can therefore skip to the last section of this chapter to review the design of the sample database. Schemas and Entities The Relational Data Model ©2010 Elsevier Inc. All rights reserved. 10.1016/B978-0-12-375697-8.50001-7 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... project, join, union, and difference—are fundamental to SQL operations In fact, any DBMS that supports them is said to be relationally complete The remaining operations (product and intersect) are useful for helping us understand how SQL processes queries 1  Restrict is a renaming of the operation that was originally called “select.” However, because SQL s main retrieval command is SELECT, restrict was... remove this watermark 29 30  Chapter 2: Relational Algebra It is possible to use SQL without understanding much about relational algebra However, you will find it easier to formulate effective efficient queries if you have an understanding of what the SQL syntax is asking the DBMS to do There is often more than one way to write a SQL command to obtain a specific result The commands will often differ in the... Split-Merge on www.verypdf.com to remove this watermark 2 Relational Algebra When we use SQL to manipulate data in a database, we are actually using something known as the relational calculus, a method for using a single command to instruct the DBMS to perform one or more actions The DBMS must then break down the SQL command into a set of operations that it can perform one after the other to produce... must do the operations one at a time, in a step-by-step sequence We therefore say that relational algebra is procedural SQL, on the other hand, lets us formulate our queries in a logical way without necessarily specifying the order in which relational operations should be performed SQL is therefore non-procedural There is no official syntax for relational algebra What you will see in this chapter, however,... or columns that make up a primary key, just as is done in Figure 1-3 3   In fact, today’s major DBMSs do not provide direct support for true relational domains Nonetheless you will see that there are SQL constructs that simulate domains Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark 8  Chapter 1:  The Relational Data Model Figure 1-3:  A UML etity Sometimes there is no... of what is happening We’ll deal with effect of nulls at various places throughout this book Base versus Virtual Tables There are two primary types of tables with which you will be working when you use SQL The tables that contain data that are stored in the database are known as base tables However, the DBMS also uses several types of temporary tables that only exist in main memory These are virtual... Split-Merge on www.verypdf.com to remove this watermark Representing Relationships  17 key value must reference an existing primary key value As you will see throughout this book, much of what you do with SQL involves retrieving matching data using primary key–foreign key relationships Foreign keys are not limited to single columns; they can be concatenated, just like primary keys As an example, consider... views, using one or more columns and one or more rows Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark The Design of the Sample Database  19 Views are stored in the database as SQL query expressions Each has a name When someone uses the name of the view, either from the command line or in an application program, the DBMS executes the query and assembles a virtual table in main . remove this watermark. SQL Clearly Explained Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SQL Clearly Explained Third. for SQL program- ming: embedded SQL (using a high-level host lan- guage), dynamic SQL, and triggers/stored procedures. ese chapters teach you syntax of SQL