Joe Celko's SQL for Smarties: Advanced SQL Programming (4th edition) PDF
Preview Joe Celko's SQL for Smarties: Advanced SQL Programming (4th edition)
Joe Celko’s SQL for Smarties Fourth Edition Joe Celko’s Data, Measurements and Standards in SQL Joe Celko Information Modeling and Relational Databases, 2nd Edition Terry Halpin, Tony Morgan Joe Celko’s Thinking in Sets Joe Celko Business Metadata Bill Inmon, Bonnie O’Neil, Lowell Fryman Unleashing Web 2.0 Gottfried Vossen, Stephan Hagemann Enterprise Knowledge Management David Loshin Business Process Change, 2nd Edition Paul Harmon IT Manager’s Handbook, 2nd Edition Bill Holtsnider & Brian Jaffe Joe Celko’s Puzzles and Answers, 2nd Edition Joe Celko Making Shoes for the Cobbler’s Children Charles Betz Joe Celko’s Analytics and OLAP in SQL Joe Celko Data Preparation for Data Mining Using SAS Mamdouh Refaat Querying XML: XQuery, XPath, and SQL/ XML in Context Jim Melton and Stephen Buxton Data Mining: Concepts and Techniques, 2nd Edition Jiawei Han and Micheline Kamber Database Modeling and Design: Logical Design, 4th Edition Toby J, Teorey, Sam S. Lightstone, Thomas P. Nadeau Foundations of Multidimensional and Metric Data Structures Hanan Samet Joe Celko’s SQL for Smarties: Advanced SQL Programming, 4th Edition Joe Celko Moving Objects Databases Ralf Hartmut Güting and Markus Schneider Joe Celko’s SQL Programming Style Joe Celko Data Mining, Second Edition: Concepts and Techniques Jiawei Han, Micheline Kamber, Jian pei Fuzzy Modeling and Genetic Algorithms for Data Mining and Exploration Earl Cox Data Modeling Essentials, 3rd Edition Graeme C. Simsion and Graham C. Witt Location-Based Services Jochen Schiller and Agnès Voisard Managing Time in Relational Databases: How to Design, Update and Query Temporal Data Tom Johnston and Randall Weis Database Modeling with Microsoft® Visio for Enterprise Architects Terry Halpin, Ken Evans, Patrick Hallock, Bill Maclean Designing Data-Intensive Web Applications Stephano Ceri, Piero Fraternali, Aldo Bongio, Marco Brambilla, Sara Comai, Maristella Matera Mining the Web: Discovering Knowledge from Hypertext Data Soumen Chakrabarti Advanced SQL: 1999—Understanding Object- Relational and Other Advanced Features Jim Melton Database Tuning: Principles, Experiments, and Troubleshooting Techniques Dennis Shasha, Philippe Bonnet SQL:1999—Understanding Relational Language Components Jim Melton, Alan R. Simon Information Visualization in Data Mining and Knowledge Discovery Edited by Usama Fayyad, Georges G. Grinstein, Andreas Wierse Transactional Information Systems Gerhard Weikum and Gottfried Vossen Spatial Databases Philippe Rigaux, Michel Scholl, and Agnes Voisard Managing Reference Data in Enterprise Database Malcolm Chisholm Understanding SQL and Java Together Jim Melton and Andrew Eisenberg Database: Principles, Programming, and Performance, 2nd Edition Patrick and Elizabeth O’Neil The Object Data Standar Edited by R. G. G. Cattell, Douglas Barry Data on the Web: From Relations to Semistructured Data and XML Serge Abiteboul, Peter Buneman, Dan Suciu Data Mining, Third Edition Practical Machine Learning Tools and Techniques with Java Implementations Ian Witten, Eibe Frank Joe Celko’s Data and Databases: Concepts in Practice Joe Celko Developing Time-Oriented Database Applications in SQL Richard T. Snodgrass Web Farming for the Data Warehouse Richard D. Hackathorn Management of Heterogeneous and Autonomous Database Systems Edited by Ahmed Elmagarmid, Marek Rusinkiewicz, Amit Sheth Object-Relational DBMSs: 2nd Edition Michael Stonebraker and Paul Brown, with Dorothy Moore Universal Database Management: A Guide to Object/Relational Technology Cynthia Maro Saracco Readings in Database Systems, 3rd Edition Edited by Michael Stonebraker, Joseph M. Hellerstein Understanding SQL’s Stored Procedures: A Complete Guide to SQL/PSM Jim Melton Principles of Multimedia Database Systems V. S. Subrahmanian Principles of Database Query Processing for Advanced Applications Clement T. Yu, Weiyi Meng Advanced Database Systems Carlo Zaniolo, Stefano Ceri, Christos Faloutsos, Richard T. Snodgrass, V. S. Subrahmanian, Roberto Zicari Principles of Transaction Processing, 2nd Edition Philip A. Bernstein, Eric Newcomer Using the New DB2: IBMs Object- Relational Database System Don Chamberlin Distributed Algorithms Nancy A. Lynch Active Database Systems: Triggers and Rules For Advanced Database Processing Edited by Jennifer Widom, Stefano Ceri Migrating Legacy Systems: Gateways, Interfaces, & the Incremental Approach Michael L. Brodie, Michael Stonebraker Atomic Transactions Nancy Lynch, Michael Merritt, William Weihl, Alan Fekete Query Processing for Advanced Database Systems Edited by Johann Christoph Freytag, David Maier, Gottfried Vossen Transaction Processing Jim Gray, Andreas Reuter Database Transaction Models for Advanced Applications Edited by Ahmed K. Elmagarmid A Guide to Developing Client/Server SQL Applications Setrag Khoshafian, Arvola Chan, Anna Wong, Harry K. T. Wong The Morgan Kaufmann Series in Data Management Systems (Selected Titles) Joe Celko’s SQL for Smarties Advanced SQL Programming Fourth Edition Joe Celko AMSTERDAM • BOSTON • HEIDELBERG • LONDON NEW YORK • OXFORD • PARIS • SAN DIEGO SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO Morgan Kaufmann is an imprint of Elsevier Acquiring Editor: Rick Adams Development Editor: David Bevans Project Manager: Sarah Binns Designer: Joanne Blank Morgan Kaufmann is an imprint of Elsevier 30 Corporate Drive, Suite 400, Burlington, MA 01803, USA © 2011 Elsevier Inc. 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 or professional practices may become necessary. Practitioners and researchers must always rely on their own experience and knowledge in evaluating and using any information or methods 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 product 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 Application submitted. British Library Cataloguing-in-Publication Data A catalogue record for this book is available from the British Library. ISBN: 978-0-12-382022-8 Printed in the United States of America 10 11 12 13 14 10 9 8 7 6 5 4 3 2 1 Typeset by: diacriTech, Chennai, India For information on all MK publications visit our website at www.mkp.com. To Ann and Jackers ABOUT THE AUTHOR xix About the Author Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. He has written over 900 columns in the computer trade and academic press, mostly dealing with data and databases, and has authored seven other books on SQL for Morgan Kaufmann: • SQL for Smarties (1995, 1999, 2005, 2010) • SQL Puzzles and Answers (1997, 2006) • Data and Databases (1999) • Trees and Hierarchies in SQL (2004) • SQL Programming Style (2005) • Analytics and OLAP in SQL (2005) • Thinking in Sets (2008) Mr. Celko’s past columns include: • Columns for Simple Talk (Redgate Software) • “CELKO,” Intelligent Enterprise magazine (CMP) • BMC’s DBAzine.com e-magazine (BMC Software) • “SQL Explorer,” DBMS (Miller Freeman) • “Celko on SQL,” Database Programming and Design (Miller Freeman) • “WATCOM SQL Corner,” Powerbuilder Developers’ Journal (SysCon) • “SQL Puzzle,” Boxes and Arrows (Frank Sweet Publishing) • “DBMS/Report,” Systems Integration (Cahner Ziff) “Data Desk,” Tech Specialist (R&D) • “Data Points,” PC Techniques (Coriolis Group) • “Celko on Software,” Computing (VNC Publications, UK) • “SELECT * FROM Austin” (Array Publications, The Netherlands) In addition, Mr. Celko was editor for the “Puzzles & Problems” section of ABACUS (SpringerVerlag) and he ran the CASEFORUM section 18, “Celko on SQL,” on CompuServe. INTRODUCTION TO THE FOURTH EDITION xxi INTRODUCTION TO THE FOURTH EDITION This book, like the first, second, and third editions before it, is for the working SQL programmer who wants to pick up some advanced programming tips and techniques. It assumes that the reader is an SQL programmer with a year or more of actual experience. This is not an introductory book, so let’s not have any gripes in the amazon.com reviews about that like we did with the prior editions. The first edition was published 10 years ago, and became a minor classic among working SQL programmers. I have seen copies of this book on the desks of real programmers in real pro- gramming shops almost everywhere I have been. The true com- pliment are the Post-it® notes sticking out of the top. People really use it often enough to put stickies in it! Wow! What Changed in Ten Years Hierarchical and network databases still run vital legacy systems in major corporations. SQL people do not like to admit that IMS and traditional files are still out there in the Fortune 500. But SQL people can be proud of the gains SQL-based systems have made over the decades. We have all the new applications and all the important smaller databases. OO programming is firmly in place, but may give ground to functional programming in the next decade. Object and object- relational databases found niche markets, but never caught on with the mainstream. XML is no longer a fad in 2010. Technically, it is syntax for describing and moving data from one platform to another, but its support tools allow searching and reformatting. There is an SQL/XML subcommittee in INCITS H2 (the current name of the original ANSI X3H2 Database Standards Committee) making sure they can work together. Data warehousing is no longer an exotic luxury only for major corporations. Thanks to the declining prices of hardware and software, medium-sized companies now use the technology. Writing OLAP queries is different from OLTP queries and prob- ably needs its own “Smarties” book now. xxii INTRODUCTION TO THE FOURTH EDITION Open Source databases are doing quite well and are gaining more and more Standards conformance. The LAMP platform (Linux, Apache, MySQL, and Python/PHP) has most of the web sites. Ingres, Postgres, Firebird, and other products have the ANSI SQL-92 features, most of the SQL-99, and some of the SQL:2003 features. Columnar databases, parallelism, and Optimistic Concurrency are all showing up in commercial product instead of the labora- tory. The SQL Standards have changed over time, but not always for the better. Parts of it have become more relational and set- oriented while other parts put in things that clearly are proce- dural, deal with nonrelational data, and are based on file system models. To quote David McGoveran, “A committee never met a feature it did not like.” And he seems to be quite right. But with all the turmoil the ANSI/ISO Standard SQL-92 was the common subset that will port across SQL products to do use- ful work. In fact, years ago, the US government described the SQL-99 standard as “a standard in progress” and required SQL-92 conformance for federal contracts. We had the FIPS-127 conformance test suite in place during the development of SQL-92, so all the vendors could move in the same direction. Unfortunately, the Clinton administration canceled the program and conformance began to drift. Michael M. Gorman, President of Whitemarsh Information Systems Corporation and secretary of INCITS H2 for over 20 years, has a great essay on this and other political aspects of SQL’s history at Wiscorp.com that is worth reading. Today, the SQL-99 standard is the one to use for portable code on the greatest number of platforms. But vendors are adding SQL:2003 features so rapidly, I do not feel that I have to stick to a minimal standard. New in This Edition In the second edition, I dropped some of the theory from the book and moved it to Data and Databases (ISBN 13:978-1558604322). I find no reason to add it back into this edition. I have moved and greatly expanded techniques for trees and hierarchies into their own book (Trees and Hierarchies in SQL, ISBN 13:978-1558609204) because there was enough material to justify it. There is a short mention of some techniques here, but not to the detailed level in the other book. I put programming tips for newbies into their own book (SQL Programming Style, ISBN 13:978-0120887972) because this book INTRODUCTION TO THE FOURTH EDITION xxiii is an advanced programmer’s book and I assume that the reader is now writing real SQL, not some dialect or his or her native programming language in a thin disguise. I also assume that the reader can translate Standard SQL into his or her local dialect without much effort. I have tried to provide comments with the solutions, to explain why they work. I hope this will help the reader see under- lying principles that can be used in other situations. A lot of people have contributed material, either directly or via Newsgroups and I cannot thank all of them. But I made a real effort to put names in the text next to the code. In case I missed anyone, I got material or ideas from Aaron Bertrand, Alejandro Mesa, Anith Sen, Craig Mullins (who has done the tech reads on several editions), Daniel A. Morgan, David Portas, David Cressey, Dawn M. Wolthuis, Don Burleson, Erland Sommarskog, Itzak Ben-Gan, John Gilson, Knut Stolze, Ken Henderson, Louis Davidson, Dan Guzman, Hugo Kornelis, Richard Romley, Serge Rielau, Steve Kass, Tom Moreau, Troels Arvin, Vadim Tropashko, Plamen Ratchev, Gert-Jan Strik, and probably a dozen others I am forgetting. Corrections and Additions Please send any corrections, additions, suggestions, improvements, or alternative solutions to me or to the publisher. Especially if you have a better way of doing something. www.mkp.com 1 1 DATABASES VERSUS FILE SYSTEMS It ain’t so much the things we don’t know that get us in trouble. It’s the things we know that ain’t so. Artemus Ward (William Graham Sumner), American Writer and Humorist, 1834–1867 Databases and RDBMS in particular are nothing like the file systems that came with COBOL, FORTRAN, C, BASIC, PL/I, Java, or any of the procedural and OO programming languages. We used to say that SQL means “Scarcely Qualifies as a Language” because it has no I/O of its own. SQL depends on a host language to get and receive data to and from end users. Programming languages are usually based on some underly- ing model; if you understand the model, the language makes much more sense. For example, FORTRAN is based on algebra. This does not mean that FORTRAN is exactly like algebra. But if you know algebra, FORTRAN does not look all that strange to you. You can write an expression in an assignment statement or make a good guess as to the names of library functions you have never seen before. Programmers are used to working with files in almost every other programming language. The design of files was derived from paper forms; they are very physical and very dependent on the host programming language. A COBOL file could not eas- ily be read by a FORTRAN program and vice versa. In fact, it was hard to share files among programs written in the same program- ming language! The most primitive form of a file is a sequence of records that are ordered within the file and referenced by physical position. You open a file then read a first record, followed by a series of next records until you come to the last record to raise Joe Celko’s SQL for Smarties. DOI: 10.1016/B978-0-12-382022-8.00001-6 Copyright © 2011 by Elsevier Inc. All rights reserved. 2 Chapter 1 DATABASES VERSUS FILE SYSTEMS the end-of-file condition. You navigate among these records and perform actions one record at a time. The actions you take on one file have no effect on other files that are not in the same program. Only programs can change files. The model for SQL is data kept in sets, not in physical files. The “unit of work” in SQL is the whole schema, not individual tables. Sets are those mathematical abstractions you studied in school. Sets are not ordered and the members of a set are all of the same type. When you do an operation on a set, the action hap- pens “all at once” to the entire membership. That is, if I ask for the subset of odd numbers from the set of positive integers, I get all of them back as a single set. I do not build the set of odd numbers by sequentially inspecting one element at a time. I define odd numbers with a rule—“If the remainder is 1 when you divide the number by 2, it is odd”—that could test any integer and classify it. Parallel processing is one of many, many advantages of having a set-oriented model. SQL is not a perfect set language any more than FORTRAN is a perfect algebraic language, as we will see. But when in doubt about something in SQL, ask yourself how you would specify it in terms of sets and you will probably get the right answer. SQL is much like Gaul—it is divided into three parts, which are three sublanguages: • DDL: Data Declaration Language • DML: Data Manipulation Language • DCL: Data Control Language The Data Declaration Language (DDL) is what defines the database content and maintains the integrity of that data. Data in files have no integrity constraints, default values, or relation- ships; if one program scrabbles the data, then the next program is screwed. Talk to an older programmer about reading a COBOL file with a FORTRAN program and getting output instead of errors. The more effort and care you put into the DDL, the better your RDBMS will work. The DDL works with the DML and the DCL; SQL is an integrated whole and not a bunch of discon- nected parts. The Data Manipulation Language (DML) is where most of my readers will earn a living doing queries, inserts, updates, and deletes. If you have normalized data and build a good schema, then your job is much easier and the results are good. Procedural code will compile the same way every time. SQL does not work that way. Each time a query or other statement is processed, the execu- tion plan can change based on the current state of the database. As quoted by Plato in Cratylus, “Everything flows, nothing stands still.” Chapter 1 DATABASES VERSUS FILE SYSTEMS 3 The Data Control Language (DCL) is not a data security language, it is an access control language. It does not encrypt the data; encryption is not in the SQL Standards, but vendors have such options. It is not generally stressed in most SQL books and I am not going to do much with it. DCL deserves a small book unto itself. It is the neglected third leg on a three-legged stool. Maybe I will write such a book some day. Now let’s look at fundamental concepts. If you already have a background in data processing with traditional file systems, the first things to unlearn are: 1. Database schemas are not file sets. Files do not have relation- ships among themselves; everything is done in applications. SQL does not mention anything about the physical storage in the Standard, but files are based on physically contigu- ous storage. This started with punch cards, was mimicked in magnetic tapes, and then on early disk drives. I made this item first on my list because this is where all the problems start. 2. Tables are not files; they are parts of a schema. The schema is the unit of work. I cannot have tables with the same name in the same schema. A file system assigns a name to a file when it is mounted on a physical drive; a table has a name in the database. A file has a physical existence, but a table can be virtual (VIEW, CTE, query result, etc.). 3. Rows are not records. Records get meaning from the applica- tion reading them. Records are sequential, so first, last, next, and prior make sense; rows have no physical ordering (ORDER BY is a clause in a CURSOR). Records have physical locators, such as pointers and record numbers. Rows have relational keys, which are based on uniqueness of a subset of attributes in a data model. The mechanism is not specified and it varies quite a bit from SQL to SQL. 4. Columns are not fields. Fields get meaning from the appli- cation reading them, and they may have several meanings depending on the applications. Fields are sequential within a record and do not have data types, constraints, or defaults. This is active versus passive data! Columns are also NULL-able, a concept that does not exist in fields. Fields have to have physi- cal existence, but columns can be computed or virtual. If you want to have a computed column value, you can have it in the application, not the file. Another conceptual difference is that a file is usually data that deals with a whole business process. A file has to have enough data in itself to support applications for that one business process. 4 Chapter 1 DATABASES VERSUS FILE SYSTEMS Files tend to be “mixed” data, which can be described by the name of the business process, such as “The Payroll file” or something like that. Tables can be either entities or relationships within a business process. This means that the data held in one file is often put into several tables. Tables tend to be “pure” data that can be described by single words. The payroll would now have separate tables for timecards, employees, projects, and so forth. 1.1 Tables as Entities An entity is a physical or conceptual “thing” that has meaning by itself. A person, a sale, or a product would be an example. In a relational database, an entity is defined by its attributes. Each occurrence of an entity is a single row in the table. Each attribute is a column in the row. The value of the attribute is a scalar. To remind users that tables are sets of entities, I like to use collective or plural nouns that describe the function of the enti- ties within the system for the names of tables. Thus, “Employee” is a bad name because it is singular; “Employees” is a better name because it is plural; “Personnel” is best because it is col- lective and does not summon up a mental picture of individual persons. This also follows the ISO 11179 Standards for metadata. I cover this in detail in my book, SQL Programming Style (ISBN 978-0120887972). If you have tables with exactly the same structure, then they are sets of the same kind of elements. But you should have only one set for each kind of data element! Files, on the other hand, were physically separate units of storage that could be alike— each tape or disk file represents a step in the PROCEDURE, such as moving from raw data, to edited data, and finally to archived data. In SQL, this should be a status flag in a table. 1.2 Tables as Relationships A relationship is shown in a table by columns that reference one or more entity tables. Without the entities, the relationship has no meaning, but the relationship can have attributes of its own. For example, a show business contract might have an agent, an employer, and a talent. The method of payment is an attribute of the contract itself, and not of any of the three parties. This means that a column can have REFERENCES to other tables. Files and fields do not do that.
The list of books you might like
