[Elsevier] SQL For Smarties - Advanced SQL programming(2011 4rd).pdf - SQL various books - smifos

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

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 ield 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 .

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:

• ColumnsforSimpleTalk(RedgateSoftware)

• “CELKO,” Intelligent Enterprise magazine (CMP)

• BMC’s DBAzine.com e-magazine(BMCSoftware)

• “SQLExplorer,” DBMS (Miller Freeman)

• “CelkoonSQL,” Database Programming and Design (Miller

Freeman)

• “WATCOM SQL Corner,” Powerbuilder Developers’ Journal

(SysCon)

• “SQLPuzzle,” Boxes and Arrows (Frank Sweet Publishing)

• “DBMS/Report,” Systems Integration (CahnerZiff)“DataDesk,”

Tech Specialist (R&D)

• “DataPoints,” PC Techniques (Coriolis Group)

• “CelkoonSoftware,” Computing (VNC Publications, UK )

• “SELECT*FROMAustin”(ArrayPublications,TheNetherlands)

Inaddition,Mr.Celkowaseditorforthe“Puzzles&Problems”

sectionofABACUS(SpringerVerlag)andherantheCASEFORUM

section18,“CelkoonSQL,”onCompuServe.

INTRODUCTION TO THE FOURTH EDITION xxi

INTRODUCTION TO THE

FOURTH EDITION

This book, like the irst, 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 irst 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 iles 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 irmly 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 ile 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 ind 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

[Elsevier] SQL For Smarties - Advanced SQL programming(2011 4rd).pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: