Joe Celko’s

1y ago
10 Views
2 Downloads
4.88 MB
817 Pages
Last View : 1m ago
Last Download : 2m ago
Upload by : Fiona Harless
Transcription

J o e C e l k o ’sSQL for SmartiesFourth Edition

The Morgan Kaufmann Series in Data Management Systems (Selected Titles)Joe Celko’s Data, Measurements andStandards in SQLJoe CelkoInformation Modeling and RelationalDatabases, 2nd EditionTerry Halpin, Tony MorganJoe Celko’s Thinking in SetsJoe CelkoBusiness MetadataBill Inmon, Bonnie O’Neil, Lowell FrymanUnleashing Web 2.0Gottfried Vossen, Stephan HagemannEnterprise Knowledge ManagementDavid LoshinBusiness Process Change, 2nd EditionPaul HarmonIT Manager’s Handbook, 2nd EditionBill Holtsnider & Brian JaffeJoe Celko’s Puzzles and Answers,2nd EditionJoe CelkoMaking Shoes for the Cobbler’s ChildrenCharles BetzJoe Celko’s Analytics and OLAP in SQLJoe CelkoData Preparation for Data Mining UsingSASMamdouh RefaatQuerying XML: XQuery, XPath, and SQL/XML in ContextJim Melton and Stephen BuxtonLocation-Based ServicesJochen Schiller and Agnès VoisardWeb Farming for the Data WarehouseRichard D. HackathornManaging Time in Relational Databases:How to Design, Update and QueryTemporal DataTom Johnston and Randall WeisManagement of Heterogeneous andAutonomous Database SystemsEdited by Ahmed Elmagarmid, MarekRusinkiewicz, Amit ShethDatabase Modeling with Microsoft Visiofor Enterprise ArchitectsTerry Halpin, Ken Evans, Patrick Hallock,Bill MacleanObject-Relational DBMSs: 2nd EditionMichael Stonebraker and Paul Brown,withDorothy MooreDesigning Data-Intensive Web ApplicationsStephano Ceri, Piero Fraternali, AldoBongio, Marco Brambilla, Sara Comai,Maristella MateraUniversal Database Management: A Guideto Object/Relational TechnologyCynthia Maro SaraccoMining the Web: Discovering Knowledgefrom Hypertext DataSoumen ChakrabartiAdvanced SQL: 1999—Understanding ObjectRelational and Other Advanced FeaturesJim MeltonDatabase Tuning: Principles, Experiments,and Troubleshooting TechniquesDennis Shasha, Philippe BonnetSQL:1999—Understanding RelationalLanguage ComponentsJim Melton, Alan R. SimonInformation Visualization in Data Miningand Knowledge DiscoveryEdited by Usama Fayyad, Georges G.Grinstein, Andreas WierseTransactional Information SystemsGerhard Weikum and Gottfried VossenData Mining: Concepts and Techniques,2nd EditionJiawei Han and Micheline KamberSpatial DatabasesPhilippe Rigaux, Michel Scholl, and AgnesVoisardDatabase Modeling and Design: LogicalDesign, 4th EditionToby J, Teorey, Sam S. Lightstone, ThomasP. NadeauManaging Reference Data in EnterpriseDatabaseMalcolm ChisholmFoundations of Multidimensional andMetric Data StructuresHanan SametJoe Celko’s SQL for Smarties: AdvancedSQL Programming, 4th EditionJoe CelkoUnderstanding SQL and Java TogetherJim Melton and Andrew EisenbergDatabase: Principles, Programming, andPerformance, 2nd EditionPatrick and Elizabeth O’NeilThe Object Data StandarEdited by R. G. G. Cattell, Douglas BarryMoving Objects DatabasesRalf Hartmut Güting and MarkusSchneiderData on the Web: From Relations toSemistructured Data and XMLSerge Abiteboul, Peter Buneman, Dan SuciuJoe Celko’s SQL Programming StyleJoe CelkoData Mining, Third Edition PracticalMachine Learning Tools and Techniqueswith Java ImplementationsIan Witten, Eibe FrankData Mining, Second Edition: Conceptsand TechniquesJiawei Han, Micheline Kamber, Jian peiFuzzy Modeling and Genetic Algorithmsfor Data Mining and ExplorationEarl CoxData Modeling Essentials, 3rd EditionGraeme C. Simsion and Graham C. WittJoe Celko’s Data and Databases: Conceptsin PracticeJoe CelkoDeveloping Time-Oriented DatabaseApplications in SQLRichard T. SnodgrassReadings in Database Systems, 3rd EditionEdited by Michael Stonebraker, Joseph M.HellersteinUnderstanding SQL’s Stored Procedures:A Complete Guide to SQL/PSMJim MeltonPrinciples of Multimedia Database SystemsV. S. SubrahmanianPrinciples of Database Query Processingfor Advanced ApplicationsClement T. Yu, Weiyi MengAdvanced Database SystemsCarlo Zaniolo, Stefano Ceri, ChristosFaloutsos, Richard T. Snodgrass, V. S.Subrahmanian, Roberto ZicariPrinciples of Transaction Processing,2nd EditionPhilip A. Bernstein, Eric NewcomerUsing the New DB2: IBMs ObjectRelational Database SystemDon ChamberlinDistributed AlgorithmsNancy A. LynchActive Database Systems: Triggers andRules For Advanced Database ProcessingEdited by Jennifer Widom, Stefano CeriMigrating Legacy Systems: Gateways,Interfaces, & the Incremental ApproachMichael L. Brodie, Michael StonebrakerAtomic TransactionsNancy Lynch, Michael Merritt, WilliamWeihl, Alan FeketeQuery Processing for Advanced DatabaseSystemsEdited by Johann Christoph Freytag,David Maier, Gottfried VossenTransaction ProcessingJim Gray, Andreas ReuterDatabase Transaction Models forAdvanced ApplicationsEdited by Ahmed K. ElmagarmidA Guide to Developing Client/Server SQLApplicationsSetrag Khoshafian, Arvola Chan, AnnaWong, Harry K. T. Wong

J o e C e l k o ’sSQL for SmartiesAdvanced SQL ProgrammingFourth EditionJoe CelkoAMSTERDAM BOSTON HEIDELBERG LONDONNEW YORK OXFORD PARIS SAN DIEGOSAN FRANCISCO SINGAPORE SYDNEY TOKYOMorgan Kaufmann is an imprint of Elsevier

Acquiring Editor: Rick AdamsDevelopment Editor: David BevansProject Manager: Sarah BinnsDesigner: Joanne BlankMorgan Kaufmann is an imprint of Elsevier30 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 writingfrom the Publisher. Details on how to seek permission, further information about the Publisher’s permissions policiesand 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).NoticesKnowledge and best practice in this field are constantly changing. As new research and experience broaden ourunderstanding, changes in research methods or professional practices may become necessary. Practitioners andresearchers must always rely on their own experience and knowledge in evaluating and using any information ormethods described herein. In using such information or methods they should be mindful of their own safety andthe 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 forany injury and/or damage to persons or property as a matter of product liability, negligence or otherwise, or from anyuse or operation of any methods, products, instructions, or ideas contained in the material herein.Library of Congress Cataloging-in-Publication DataApplication submitted.British Library Cataloguing-in-Publication DataA catalogue record for this book is available from the British Library.ISBN: 978-0-12-382022-8Printed in the United States of America10 11 12 13 1410 9 8 7 6 5 4 3 2 1Typeset by: diacriTech, Chennai, IndiaFor information on all MK publications visit our website at www.mkp.com.

To Ann and Jackers

This page intentionally left blank

CONTENTSviiContentsAbout the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixIntroduction to the Fourth Edition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiChapter 1 Databases versus File Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.11.21.31.41.51.6Tables as Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Tables as Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Rows versus Records. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Columns versus Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Schema Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .CREATE SCHEMA Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .445677Chapter 2 Transactions and Concurrency Control . . . . . . . . . . . . . . . . . . . . . 112.12.22.32.42.52.62.7Sessions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Transactions and ACID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Concurrency Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Pessimistic Concurrency Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . .SNAPSHOT Isolation and Optimistic Concurrency. . . . . . . . . . . . . . .Logical Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Deadlock and Livelocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11121418192121Chapter 3 Schema Level Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233.1 CREATE SCHEMA Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233.2  CREATE PROCEDURE, CREATE FUNCTION, andCREATE TRIGGER 243.3 CREATE DOMAIN Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243.4 CREATE SEQUENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253.5 CREATE ASSERTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263.6 Character Set Related Constructs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Chapter 4 Locating Data and Special Numbers . . . . . . . . . . . . . . . . . . . . . . . 354.14.24.34.44.5Exposed Physical Locators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Generated Identifiers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sequence Generator Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Preallocated Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Special Series. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3540424344

viii   CONTENTSChapter 5 Base Tables and Related Elements. . . . . . . . . . . . . . . . . . . . . . . . . 515.1 CREATE TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535.2 Nested UNIQUE Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625.3 CREATE ASSERTION Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765.4 TEMPORARY Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775.5 Manipulating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785.6 Avoiding Attribute Splitting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815.7 Modeling Class Hierarchies in DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . 835.8 Exposed Physical Locators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855.9 Auto-Incrementing Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855.10 Generated Identifiers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 905.11 A Remark on Duplicate Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 945.12 Other Schema Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 965.13 Temporary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 975.14 CREATE DOMAIN Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 985.15 CREATE TRIGGER Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 995.16 CREATE PROCEDURE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 995.17 DECLARE CURSOR Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Chapter 6 P rocedural, Semiprocedural, andDeclarative Programming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1056.16.26.36.46.56.66.7Basics of Software Engineering. . . . . . . . . . . . . . . . . . . . . . . . . . . . .Cohesion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Coupling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The Big Leap. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Rewriting Tricks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Functions for Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Procedural versus Logical Decomposition . . . . . . . . . . . . . . . . . . . .105105106107114118119Chapter 7 Procedural Constructs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1237.17.27.37.47.57.6CREATE PROCEDURE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123CREATE TRIGGER. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124CURSORs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127SEQUENCEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Generated Columns 142Table Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

CONTENTSixChapter 8 Auxiliary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1458.1 The Series Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1458.2 Lookup Auxiliary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1518.3 Auxiliary Function Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1598.4 Global Constants Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1698.5  A Note on Converting ProceduralCode to Tables 175Chapter 9 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1819.1 Functional and Multivalued Dependencies. . . . . . . . . . . . . . . . . . . .9.2 First Normal Form (1NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.3 Second Normal Form (2NF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.4 Third Normal Form (3NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.5 Elementary Key Normal Form (EKNF). . . . . . . . . . . . . . . . . . . . . . . .9.6 Boyce-Codd Normal Form (BCNF). . . . . . . . . . . . . . . . . . . . . . . . . . .9.7 Fourth Normal Form (4NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.8 Fifth Normal Form (5NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.9 Domain-Key Normal Form (DKNF). . . . . . . . . . . . . . . . . . . . . . . . . . .9.10 Practical Hints for Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . .9.11 Key Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.12 Practical Hints for Denormalization . . . . . . . . . . . . . . . . . . . . . . . . .183184188189191192194194196204205208Chapter 10 Numeric Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21510.110.210.310.410.510.610.710.8Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Numeric Type Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Four Function Arithmetic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Arithmetic and NULLs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Converting Values to and from NULL . . . . . . . . . . . . . . . . . . . . . . .Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Unique Value Generators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .IP Addresses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215220222224225228232235Chapter 11 Temporal Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23711.1 Notes on Calendar Standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . .11.2 SQL Temporal Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11.3 INTERVAL Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237240246

x   CONTENTS11.4 Temporal Arithmetic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11.5 The Nature of Temporal Data Models. . . . . . . . . . . . . . . . . . . . . . .249250Chapter 12 Character Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25512.112.212.312.412.5Problems with SQL Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Standard String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Common Vendor Extensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Cutter Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Nested Replacement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255258259268269Chapter 13 NULLs: Missing Data in SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27113.1 Empty and Missing Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.2 Missing Values in Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.3 Context and Missing Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.4 Comparing NULLs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.5 NULLs and Logic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.6 Math and NULLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.7 Functions and NULLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.8 NULLs and Host Languages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.9 Design Advice for NULLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13.10 A Note on Multiple NULL Values. . . . . . . . . . . . . . . . . . . . . . . . . .272273275276277281281281282285Chapter 14 Multiple Column Data Elements. . . . . . . . . . . . . . . . . . . . . . . . . . 28914.114.214.314.414.514.6Distance Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Storing an IPv4 Address in SQL. . . . . . . . . . . . . . . . . . . . . . . . . . .Storing an IPv6 Address in SQL. . . . . . . . . . . . . . . . . . . . . . . . . . .Currency and Other Unit Conversions. . . . . . . . . . . . . . . . . . . . . .Social Security Numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Rational Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .289291293294295298Chapter 15 Table Operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29915.115.215.315.415.5DELETE FROM Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .INSERT INTO Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The UPDATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .A Note on Flaws in a Common Vendor Extension . . . . . . . . . . . .MERGE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299307310317319

CONTENTSxiChapter 16 Comparison or Theta Operators. . . . . . . . . . . . . . . . . . . . . . . . . . 32316.1 Converting Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16.2 Row Comparisons in SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16.3 IS [NOT] DISTINCT FROM Operator. . . . . . . . . . . . . . . . . . . . . .323326328Chapter 17 Valued Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32917.1 IS NULL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17.2 IS [NOT]{TRUE FALSE UNKNOWN} Predicate. . . . . . . . . . .17.3 IS [NOT] NORMALIZED Predicate. . . . . . . . . . . . . . . . . . . . . . . . .329330332Chapter 18 CASE Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33318.1 The CASE Expression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18.2 Subquery Expressions and Constants. . . . . . . . . . . . . . . . . . . . . .18.3 Rozenshtein Characteristic Functions . . . . . . . . . . . . . . . . . . . . . .333341342Chapter 19 LIKE and SIMILAR TO Predicates. . . . . . . . . . . . . . . . . . . . . 34519.119.219.319.419.519.619.7Tricks with Patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Results with NULL Values and Empty Strings . . . . . . . . . . . . . . . .LIKE Is Not Equality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Avoiding the LIKE Predicate with a Join. . . . . . . . . . . . . . . . . . . .CASE Expressions and LIKE Search Conditions. . . . . . . . . . . . . .SIMILAR TO Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Tricks with Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .346347348348349350352Chapter 20 B ETWEEN and OVERLAPSPredicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35520.1 The BETWEEN Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20.2 OVERLAPS Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .355358Chapter 21 The [NOT] IN() Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . 36921.121.221.321.421.5Optimizing the IN() Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . .Replacing ORs with the IN() Predicate . . . . . . . . . . . . . . . . . . . . .NULLs and the IN() Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . .IN() Predicate and Referential Constraints. . . . . . . . . . . . . . . . . .IN() Predicate and Scalar Queries. . . . . . . . . . . . . . . . . . . . . . . . .370373374376377

xii   CONTENTSChapter 22 EXISTS() Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38122.122.222.322.422.522.6EXISTS and NULLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .EXISTS and INNER JOINs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .NOT EXISTS and OUTER JOINs. . . . . . . . . . . . . . . . . . . . . . . . . . .EXISTS() and Quantifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .EXISTS() and Referential Constraints . . . . . . . . . . . . . . . . . . . . .EXISTS and Three-Valued Logic. . . . . . . . . . . . . . . . . . . . . . . . . . .382384385385386387Chapter 23 Quantified Subquery Predicates . . . . . . . . . . . . . . . . . . . . . . . . . 38923.123.223.323.423.5Scalar Subquery Comparisons. . . . . . . . . . . . . . . . . . . . . . . . . . . .Quantifiers and Missing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . .The ALL Predicate and Extrema Functions . . . . . . . . . . . . . . . . . .The UNIQUE Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The DISTINCT Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .389391393394395Chapter 24 The Simple SELECT Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . 39724.1 SELECT Statement Execution Order. . . . . . . . . . . . . . . . . . . . . . . .24.2 One-Level SELECT Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . .397397Chapter 25 Advanced SELECT Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . 40725.125.225.325.425.525.625.725.8Correlated Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Infixed INNER JOINs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .OUTER JOINs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .UNION JOIN Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Scalar SELECT Expressions. . . . . . . . . . . . . . . . . . . . . .

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

Related Documents:

View All Post Clients Mockup. . Email: Phone: Credit Card Remove Joe User Joe.user@thesignman.com 919-857-8956 *****1467 Remove Joe User Joe.user@thesignman.com 919-857-8956 *****1467 Remove Joe User Joe.us

If the power cord is damaged, do not use the appliance. Contact your authorized Snow Joe Sun Joe dealer or call the Snow Joe Sun Joe customer service center at 1-866-SNOWJOE (1-866-766-9563). Keep the machine on a stable, flat surface during operation,

Performance, 2 ndEdition 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 S erge Abiteboul, Peter Buneman, Dan Suciu Data Mining, Third Edition Practical Machine Learning Tools

I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). I am trying to teach the reader how to work in logical and declarative terms, instead of in

The Morgan Kaufmann Series in Data Management Systems Series Editor: Jim Gray, Microsoft Research Joe Celko’s Analytics and OLAP in

Inside Microsoft SQL Server 2008: T-SQL Querying (Microsoft Press, 2009) and Chapter 2 of Itzik Ben-Gan's Microsoft SQL Server 2008: T-SQL Fundamentals (Microsoft Press, 2008). For more information on the use of "Set of all " see Joe Celko's Thinking in Sets (Morgan Kaufman, 2008).

Independent Personal Pronouns Personal Pronouns in Hebrew Person, Gender, Number Singular Person, Gender, Number Plural 3ms (he, it) א ִוה 3mp (they) Sֵה ,הַָּ֫ ֵה 3fs (she, it) א O ה 3fp (they) Uֵה , הַָּ֫ ֵה 2ms (you) הָּ תַא2mp (you all) Sֶּ תַא 2fs (you) ְ תַא 2fp (you

Mata kulian Anatomi dan Fisiologi Ternak di fakultas Peternakan merupakan mata kuliah wajib bagi para mahasiswa peternakan dan m.k. ini diberikan pada semester 3 dengan jumlah sks 4 (2 kuliah dan 2 praktikum.Ilmu Anatomi dan Fisiologi ternak ini merupakan m.k. dasar yang harus dipahami oleh semua mahasiswa peternakan. Ilmu Anatomi dan Fisiologi Ternak ini yang mendasari ilmu-ilmu yang akan .