`ru'w^,
`
`Ese¡c¡RNEffi
`
`te
`erence
`
`)ompreh
`
`rf SQt capab
`
`;tandards,
`
`rogramming
`
`'.1frflilinrfrfltlffiîflllliliilnlfijuil[utttlttllrum
`
`nnunrnlp I lliçtnrv marftg{ tfendS. I
`þmparisoi I
`3oor?s?¿s¿ ,ing brandS I
`o 5917 3Oo97A7252
`I
`I
`I ur Jvr uurvrJ
`I
`
`Cl) features 0racle8,
`]tllicrosoft S0[ Server 1
`lnformix, Sybase, and
`IBltll D82 trial softltlare
`
`)est-selling SQL ønd UNIX øuthors
`
`World Programming Limited EXHIBIT 1010
`Page 1 of 167
`
`
`
`I i
`
`i
`
`I
`
`'
`
`I I I i ;
`
`tr
`
`'rllfi LIBRÀRIr
`or
`fitc uNIvERstTl
`0F'tEXAs
`AT
`AUSl'IN
`
`World Programming Limited EXHIBIT 1010
`Page 2 of 167
`
`
`
`World Programming Limited EXHIBIT 1010
`Page 3 of 167
`
`
`
`World Programming Limited EXHIBIT 1010
`Page 4 of 167
`
`
`
`rrt
`
`Osborne/McGraw-Hill
`2600 Tenth Street
`Berkeley, CaIiÍ ornia 9 47 l0
`U.S.A.
`For information on translations or book distributors outside the U.S.A., or to arrange bulk
`¡;;;h"r; discounts for sales promotions, premiums, or fund-raisers, please contact
`^Osborne/McGraw-Hill at the above address'
`
`SQL: The ComPlete Reference
`
`Copyright O199gby The McGraw-Hill Companies. All rights reserved' Printed in the United
`Staìés o"f America. Éxcept as permitted undei the Copyright Aú of t976, no part of thìs
`publication may be ."pioduced or distributed in any form or by any-means, or stored in a
`ã",îu"räîr rliíi"*i ryrt.*, without the prior writíen permission of the publisher, with the
`.*.uptlo" that the prog.u* listings *uy P" entered, stored, and executed in a computer system'
`but they may not be reproduced for publication'
`Licensed Materials - Property of IBM.
`IBM@ DB2@ Universal óataúase Personal Edition, Version 5.2, lor the Windows@ operating
`Environmentso Copyright IBM Corp. 1993,1998.,411 Rights Reserved.
`U.S. Government Users"Restricted Rights - Use, duplicaiiot o. disclosure restricted by GSA ADP
`Schedule Contract with IBM CorP'
`@ l99g Informix Corporation. All rights reserved. Informix@ is a trademark of Informix
`ðo.po.ãtio" or its afiiliates and is re"gistered in the U.S. and some other jurisdictions'
`Microsoft@ sQL server'" 7.0 Evaluation Edition. Copyright Microsoft Corporation, 1997-98'
`rights reserved.
`oracleS Personal Editiono 1996,1998, Oracle Corporation. Al1 rights reserved'
`Copyright A ß96 - 1998, Sybase, Inc. All rights reserved'
`
`^ll
`
`890 DOC DOC 01987654321
`rsBN 0-07-211845-8
`
`Publisher
`Brandon A. Nordin
`Associate Publisher and
`Editor-in-Chief
`Scott Rogers
`Senior Acquisitions Editot
`Wendy Rinaldi
`Acquisitions Editor
`Jane K. Brownlow
`Project Editor
`Heidi Poulin
`Editorial Assistant
`Monika Faltiss
`
`Copy Editor
`Nancy CrumPton
`Proofreader
`Rhonda Holmes
`Indexer
`Valerie Robbins
`Computer Designer
`Jani Beckwith
`Michelle Galicia
`Illustrators
`Robert Hansen
`Brian Wells
`Beth Young
`
`Information has been obtained by Osborne/McGraw-Hill from sources believed to be reliable. However, because of the
`possibilitv of human or mechaniáal errot by our sources, osborne/McGraw-Hill, or others, osborne'/McGraw-Hill does not
`,f* oll"ro.y, adequacy, or completeness of any information and is not responsible for any errors or omissions or
`;;";i;;
`ihe results obtained from use of such information.
`
`World Programming Limited EXHIBIT 1010
`Page 5 of 167
`
`
`
`Contents at aGlance
`
`| ,;;. An Overulew of SQL
`1, Introduction
`2 A Quick Tour of SQL
`3 SQL In Perspective
`4 Relational Databases
`
`' :,r , - Retrieving Data
`5 SQL Basics . . .
`6 Simple Queries
`7 Multi-Table Queries (Joins)
`I Summary Queries
`I Subqueries and Query Expressions
`
`10
`LL
`L2
`
`Database Updates
`Data Integrity
`Transaction Processing
`
`v
`
`J
`13
`23
`47
`
`67
`9t
`1,37
`1,97
`2L7
`
`271,
`29r
`327
`
`World Programming Limited EXHIBIT 1010
`Page 6 of 167
`
`
`
`36t
`407
`429
`455
`
`487
`543
`601,
`
`681
`735
`751
`793
`831,
`
`849
`857
`877
`885
`895
`915
`937
`
`S0l,: The Complete Reference
`
`Peft lV i 'r 'l:', 11.¡.t.r ! 1"'!r'.ìr:if'
`13 Creating a Database
`L4 Views
`15 SQL Security
`16 The System Catalog
`
`L7 Embedded SQL
`18 Dynamic SQL*
`19 SQL APrS
`
`Paft Vl ,: ;'Ìx' r ';ri;:..,'. 1.¡1
`
`::::
`
`;:;
`
`TIII
`
`:
`
`20
`Database Processing and Stored Procedures
`2L
`SQL and Data Warehousing
`22
`SQL Nefworking and Distributed Databases
`23 SQL and Objects
`24 The Future of SQL
`
`Port Vll ',; j , jir ir:i:)r
`A The Sample Database
`:I
`B Database Vendor Profiles
`C Company and Product LiqT
`;I
`D SQL Syntax Reference
`E SQL Call Level Interface
`F SQL Information Schema Standard
`G CD-ROM Installation Guide
`
`;TT
`
`World Programming Limited EXHIBIT 1010
`Page 7 of 167
`
`
`
`Contents
`
`XXIII
`XXV
`
`J 4 6 B 8 9 9 9 9
`
`1,0
`
`1.0
`
`Acknowledgments
`Preface
`
`Eil
`
`An Ovsruþw of $QL
`
`.,:- , , .
`ilxiiir L Introduction
`The SQL Language
`The Role of SQL
`SQL Features and Benefits
`Vendor Independence . . .,.
`Portability Across Computer Systems
`SQL Standards . .
`IBM Endorsement (DB2) .
`Microsoft Commitment (ODBC and ADO)
`Relational Foundation
`High-Level, English-Like Structure
`
`wt
`
`World Programming Limited EXHIBIT 1010
`Page 8 of 167
`
`
`
`SQt: Ihe Complete Ref erence
`
`Interactive, Ad Hoc Queries
`Programmatic Database Access " . .
`Multiple Views of Data
`CompleteDatabase Language' . . . .
`Dynamic Data Definition
`Client/Server Architecture
`Extensibility and Object Technology
`Internet Database Access
`Java Integration (|DBC)
`2 A Quick Tour of SQL
`A Simple Database
`Retrieving Data
`Summarizing Data
`Adding Data to the Database
`Deleting Data
`UpdatingtheDatabase .' . . .
`Protecting Data .
`Creating a Database
`Summary
`3 SQL In Perspective . . .
`SQL and Database Management . . ' .
`A Brief History of SQL
`The Early Years
`Early Relational Products ' . .
`IBM Products
`CommercialAccePtance . .' .
`SQL Standards
`The ANSI/ISO Standards . . . .
`Other SQL Standards
`ODBC and the SQL Access GrouP
`The Portability MYth
`SQL and Networking
`Centralized Architecture
`File Server Architecture
`Client/ Server Architecture
`Multi-Tier Architecture
`The Proliferation of SQL .
`SQL and IBM's Unified Database Strategy
`SQL on MinicomPuters ' .
`SQL on Unix-Based SYstems
`SQL on Personal ComPuters
`
`10
`10
`1.0
`11
`11,
`11
`11,
`11
`12
`
`L3
`1,4
`
`1,4
`17
`1.8
`19
`19
`20
`21,
`22
`
`23
`24
`25
`26
`26
`27
`27
`29
`29
`31.
`3L
`32
`34
`34
`35
`36
`3/
`38
`39
`39
`40
`40
`
`World Programming Limited EXHIBIT 1010
`Page 9 of 167
`
`
`
`SQL and Transaction Processing
`SQL and Workgroup Databases
`SQL and Data Warehousing
`Summary
`
`llll 4 Relational Databases
`EarlyDataModels .....
`File Management Systems
`Hierarchical Databases . .
`NetworkDatabases . ... .
`The Relational Data Model
`The Sample Databhpe
`Tables
`Primary Keys
`Relationships
`Foreign Keys
`Codd's Twelve Rules *
`Summary
`
`EIT
`
`J-i,
`
`ii.r,,
`
`Ill s sQL Basics
`Statements .. .
`Names
`
`Table Names
`Column Names
`Data Types
`Constants
`Numeric Constants
`String Constants . .
`Date and Time Constants i
`S)¡mbolic Constants
`Expressions
`Built-in Functions
`Missing Data (uull, Values)
`Summary
`
`: ll 6 Simpte Queries
`The SELECT Statement . . .
`The srr,ncr Clause
`The rRou Clause
`
`Contents
`
`41
`42
`43
`45
`
`47
`48
`48
`49
`51.
`53
`54
`56
`57
`59
`59
`61,
`64
`
`67
`68
`72
`74
`74
`75
`80
`81
`B1
`82
`83
`84
`B5
`86
`88
`
`91
`92
`94
`94
`
`World Programming Limited EXHIBIT 1010
`Page 10 of 167
`
`
`
`S0L: The Complele Reference
`
`Query Results
`Simple Queries
`Calculated Columns
`Selecting All Columns (srlnct *)
`Duplicate Rows (nr srrNCT)
`Row Selection (wunnr Clause)
`.. '.. ]' r'....
`SearchConditions
`Comparison Test (=, 1),1, 1=, ), )=) ' . ' . . ,
`Range Test (BETWEEN)
`Set MembershiP Test (ru)
`Pattern Matching Test (Lrxu)
`Null Value Test (lS NULL)
`Compound Search Conditions (AND, oR, and Not)
`Sorting Query Results (onlnn ev Clause)
`Rules for Single-Table Query Processing
`Combining Query Results (ul¡rou)
`Unions and DuPlicate Rows
`Unions andSorting* . .,'
`Multiple uNToNs
`Summary
`
`tll 7 Multi-Table Queries (foins)
`A Two-Table QuerY ExamPle
`Simple joins (Equi-|oins)
`Parent/Child Queries
`Joins with Row Selection Criteria
`Multiple Matching Columns
`Queries with Three or More Tables
`Other Equi-Joins
`Non-Equi ]oins
`SQL Considerations for Multi-Table Queries
`Qualified Column NamPs
`All-ColumnSelections r . . . .'
`Self-Joins
`TableAliases....
`Multi-TableQueryPerformance
`The Structure of a Ioin . .
`Table Multiplication
`Rules for Multi-Table Query Processing
`*
`Outer Joins
`Left and Right Outer Joins * . . '
`Outer Join Notation *
`Joins and the SQL2 Standard
`
`. ... . . .
`
`'.'.'.'../
`
`94
`98
`100
`103
`104
`105
`107
`108
`
`11"Ltt{
`1t6
`119
`12L
`124
`L27
`128
`130
`132
`132
`135
`
`137
`138
`t40
`142
`L45
`L45
`146
`t49
`152
`153
`153
`155
`155
`159
`160
`1,61
`161,
`162
`1,64
`1,68
`172
`174
`
`World Programming Limited EXHIBIT 1010
`Page 11 of 167
`
`
`
`i¡fÌ1¡ ll I
`{{¿Ì I ij
`
`8
`
`Inner Joins in SQL2
`Outer Joins in SQL2 *
`Cross Joins and Union Joins in SQL2 *
`Multi-Table ]oins in SQL2
`Summary
`
`Summary Que¡ies
`Column Functions
`Computing a Column Total (sul,.r)
`Computing a Column Average (avc)
`Finding Extreme Values (uru and uax)
`Counting Data Values (couur)
`Column Functions in the Select List . .
`NULL Values and Column Functions
`Duplicate Row Elimination (nr srrNcT)
`Grouped Queries (cnoue ev Clause)
`Multiple Grouping Columns
`Restrictions on Grouped Queries
`NULL Values in Grouping Columns
`Group Search Conditions (HAVrNc Clause)
`Restrictions on Group Search Conditions
`NULL Values and Group Search Conditions
`HAVING Without GROUP BY
`Summary
`
`9
`
`Subqueries and Query Expressions . .
`Using Subqueries
`\Atrhat Is a Subquery?
`Subqueries in the wunRn Clause
`Outer References
`Subquery Search Conditions
`Subquery Comparison Test ( =, 1)¡ 1¡ 1= t >, >=)
`Set Membership Test (rN),
`Existence Test (EXISTS )
`QuantifiedTests (ANY and ALL) *
`Subqueries and Joins
`Nested Subqueries
`Correlated Subqueries
`Subqueries in the uavtNG Clause
`Subquery Summary
`Advanced Queries in SQL2
`Scalar-Valued Expressions (SQL2)
`Row Value Expressions (SQL2) . .
`
`Contents
`
`174
`177
`179
`181
`1,84
`
`187
`188
`r91,
`191
`192
`194
`195
`197
`199
`201,
`204
`208
`210
`211,
`21,5
`21,5
`21,6
`216
`
`217
`21,8
`219
`221"
`222
`223
`224
`226
`228
`231,
`237
`239
`240
`243
`245
`246
`248
`254
`
`World Programming Limited EXHIBIT 1010
`Page 12 of 167
`
`
`
`ffi
`
`SQ[: The comPlete Reference
`
`wíl 10
`
`i¿il] { i
`rÏ,l ,i: il LL
`
`Table Value ExPressions (SQL2)
`QuerYExPressions(SQl2) ' ' ' '
`SQL Queries-A Final SummarY
`
`EII
`
`Database UPdates
`Adding Data to the Database ' ' ' '
`The Single-Row TNSERT Statement
`The Multi-Row ÍNSERT Statement
`Bulk Load Utilities
`DeletingDatafromtheDatabase ' ' ' " ' ' '
`The DELETE Statement
`Deleting All Rows
`DELETE with SubquerY *
`ModifyingDataintheDatabase
`The UPDATE Statement
`UPdating All Rows
`uÞlnrn with SubquerY *
`Summary
`
`' ' " ' " ' '
`
`Data IntegritY
`What Is Data Integrity? . .
`Required Data .
`SimpleValidityChecking'''' :''' ;^^-'^'''''
`' Column Check Constraints (SQL2) ' ' ' '
`Domains (SQL2)
`Entity IntegritY
`Other Uniqueness Constraints
`Uniqueness and uul,t' Values
`Referential IntegritY
`Ref erential IntegritY'Problems
`Delete and UPdate Rules *
`CascadedDeletes andUpdates *''' "'
`Referential CYcles
`ForeignKeys andNULLValues *''' "'
`Advanced Constraint Capabilities (SQL2) ' ' " '
`Assertions
`SQL2 Constraint TYPes
`Deferred Constraint Checking
`Business Rules
`
`258
`261"
`267
`
`271.
`272
`273
`277
`280
`280
`280
`282
`283
`285
`286
`288
`289
`290
`
`29L
`292
`294
`294
`295
`296
`297
`298
`298
`299
`300
`302
`305
`308
`312
`31.3
`3L4
`315
`3L6
`319
`
`World Programming Limited EXHIBIT 1010
`Page 13 of 167
`
`
`
`Contents
`
`320
`323
`324
`324
`325
`
`327
`328
`329
`332
`334
`336
`338
`338
`339
`341,
`342
`342
`345
`345
`347
`348
`351
`357
`
`36L
`362
`364
`365
`365
`376
`J//
`381
`382
`382
`383
`385
`388
`392
`
`\Ä/hat Is a Trigger?
`Triggers and Referential Integrity . . . . .
`Trigger Advantages and Disadvantages
`Triggers and theSQlStandard . . . . . . .
`Summary
`þ$f f L2 Transaction Processing
`\zVhat Is a Transaction?
`coMMrr and Rol,l,sacx
`The ANSI/ISO Transaction Model
`Other Transaction Models
`Transactions: Behind the Scenes
`Transactions and Multi-User Processing
`The Lost Update Problem
`The Uncommitted Data Problem
`The Inconsistent Data Problem
`The Phantom Insert Problem . .
`ConcurrentTransactions .....
`
`Locking
`
`* L
`
`ocking Levels
`Shared and Exclusive Locks . . .
`Deadlocks
`Advanced Locking Techniques *
`Summary
`
`l.iiÌ:i t3 Creating a Database . . . .
`The Data Definition Language
`Creating a Database
`Table Definitions
`Creating a Table (cnnarn 'TABLE)
`Removing a Table (onoe rABLE)
`Changing a Table Definition (alrnn rABLE)
`Constraint Definitions
`Assertions
`Domains
`Aliases and Synonyms (cnnarn/DRop ALIAS)
`Indexes (cnnarn/oRoP TNDEX)
`Managing Other Database Objects
`Database Structure
`
`World Programming Limited EXHIBIT 1010
`Page 14 of 167
`
`
`
`ffi
`
`sQ[: ïhe complete Reference
`
`ffirs L4
`
`Single-Database Architecture
`Multi-DatabaseArchitecture'
`Multi-Location Architecture
`Database Structure and the ANSI/ßO Standard
`SQL2 Catalogs
`SQL2 Schemas
`Summary
`
`Views
`\Âtrhat Is a View?
`How the DBMS Handles Views
`Advantages of Views
`Disadvantages of Views
`Creating a View (cnnern vrEW)
`Horizontal Views
`Vertical Views
`Row/Column Subset Views
`GrouPed Views
`|oined Views
`Updating a View
`'
`Viu- Updates and the ANSI/ISO Standard
`View Uþdates in Commercial SQL Products
`Checking View Updates (cHncx oPTroN) ' '
`Dropping a View (nnoe vrEl^l)
`Summary
`
`ffiêå 15
`
`SQL SecuritY
`SQL SecuritY ConcePts
`User-Ids
`SecuritY Objects
`Privileges
`Views and SQL SecuritY
`Granting Privileges (cneNr)'
`Column Privileges
`Passing Privileges (cneNt oPTroN)
`Revoking Privileges (nnvoxn)
`REVoKE and the GRANT oPTroN
`REVoKE and the ANSI/ISO Standard
`Summary
`
`ffiËä 16
`
`The System Catalog
`What ls the SYstem Catalog? . " " : '
`The Catalog and QuerY Tools
`
`393
`394
`396
`398
`401
`402
`406
`
`407
`408
`410
`410
`411
`4r1
`412
`4L4
`416
`416
`419
`421
`422
`423
`424
`426
`428
`
`429
`430
`432
`436
`436
`439
`442
`444
`445
`447
`450
`452
`454
`
`455
`456
`457
`
`World Programming Limited EXHIBIT 1010
`Page 15 of 167
`
`
`
`The Catalog and the ANSI/ISO Standard
`Catalog Contents
`Table Information
`Column Information
`Viewlnformation . .. . . .
`Remarks
`Relationship Information
`User Information
`Privileges Information
`The SQL2 Information Schema
`Other Catalog Information . . ,
`Summary
`
`ffiåfi L7 Embedded set-
`Programmatic SQL Techniques
`DBMSStatementProcessing . . . . ..
`Embedded SQL Concepts . . . .
`Developing an Embedded SQL Program . .
`Running an Embedded SQL Program
`Simple Embedded SQL Statements . .
`Declaring Tables
`Error Handlirg . .
`Using Host Variables
`Data Retrieval in Embedded SQL
`Single-Row Queries
`Multi-Row Queries
`Cursor-Based Deletes and Updates
`Cursors and Transaction Processing
`Summary
`ffi ã# 18 Dynamic SQL*
`Limitations of Static SQL
`Dynamic SQL Concepts
`Dynamic Statement Execution (rxncurn TMMEDTATE)
`Two-Step Dynamic Execution
`The pRnpaRE Statement
`The nxncurE Statement
`Dynamic Queries
`The onscnrBE Statement
`
`Contents
`
`457
`458
`459
`464
`466
`470
`470
`474
`476
`477
`483
`483
`
`487
`488
`490
`491,
`492
`496
`498
`501
`502
`51.1
`520
`520
`527
`535
`540
`541,
`
`543
`544
`546
`547
`550
`554
`555
`564
`569
`
`World Programming Limited EXHIBIT 1010
`Page 16 of 167
`
`
`
`Sll[: The Complete ReferenGe
`
`ì: i..! : ;
`
`19
`
`The ¡nCr,aRE CURSOR Statement
`The Dynamic oPPl¡ Statement . . . .
`The Dynamic FETCH Statement . . .
`The Dynamic CLOSE Statement . . .
`Dynamic SQL Dialects . . .
`Dynamic SQL in SQL/DS
`Dynamic SQL in Oracle
`Dynamic SQL and the SQL2 Standard ' ' ' .
`Basic Dynamic SQL2 Statements ' '
`SQL2 and the SQLnA
`SQL2 and Dynamic SQL Queries
`Summary
`
`SQL APIS
`API Concepts
`The dblib API (SQL Server)
`Basic SQL Server Techniques
`SQL Server Queries
`Positioned Updates
`Dynamic Queries
`ODBC and the SQL/CLI Standard
`Call-Level Interface Standardization
`CLI Structures
`CLI Statement Processing
`CLI Errors and Diagnostic Information . . .' .'
`CLI Attributes
`CLl lnformation Calls
`The ODBC APL
`The Structure of ODBC
`ODBC and DBMS Independence ' . '
`ODBC Catalog Functions
`Extended ODBC Capabilities
`The Oracle Call Interface (OCI),
`Legacy OCI .
`OCI and OracleS
`Summary
`
`EU
`
`ffiXË 20
`
`Database Processing and Stored Procedures
`Stored Procedure Concepts
`A Basic Example
`Using Stored Procedures
`Creating a Stored Procedure
`
`572
`Ð/z
`575
`576
`576
`577
`580
`584
`585
`587
`594
`598
`
`601
`602
`604
`606
`613
`619
`620
`625
`625
`63t
`635
`654
`656
`658
`659
`659
`661,
`66L
`663
`667
`667
`670
`677
`
`681,
`682
`683
`684
`686
`
`World Programming Limited EXHIBIT 1010
`Page 17 of 167
`
`
`
`Calling a Stored Procedure
`Stored Procedure Variables
`Statement Blocks
`Returning a Value
`Returning Values via Parameters
`Conditional Execution
`Repeated Execution
`Other Flow-of-Control Constructs
`Cursor-Based RePetition
`Handling Error Conditions
`Advantages of Stored Procedures
`StoredProcedurePerformance . .' .
`System-Defined Stored Procedures
`External Stored Procedures
`Triggers
`Advantages and Disadvantages of Triggers
`Triggers in Transact-SQl
`Triggers in Informix SPL
`Triggers in Oracle PLISQL
`Other Trigger Considerations
`Stored Procedures and the SQL Standard
`Core Capabilities
`Creating a SQL Routine
`Flow-of-Control Statements ' .
`Cursor Operations
`Block Structure . .
`Error Handling . .
`Routine Name Overloading
`Other Stored Procedure Considerations
`Summary
`:;lîi: 21" SQL and Data Warehousing
`Data Warehousing Concepts
`Components of a Data Wai'ehouse . . .
`The Evolution of Data Warehousing
`Database Architecture for Warehousing
`Fact Cubes
`StarSchemas....
`Multi-Level Dimensions
`SQL Extensions for Data Warehousing
`WarehousePerformance . . . .
`Load Performance
`QueryPerformance....
`Summary
`
`Contents
`
`689
`690
`692
`695
`697
`701,
`703
`705
`706
`710
`713
`71.3
`71,4
`715
`71.6
`71,6
`717
`719
`722
`723
`724
`724
`725
`725
`726
`727
`730
`731,
`732
`733
`
`735
`736
`738
`739
`740
`740
`742
`745
`746
`747
`747
`749
`750
`
`World Programming Limited EXHIBIT 1010
`Page 18 of 167
`
`
`
`S0L: The Complete Reference
`
`i#f { 22 SQL Networking and Distributed Databases
`The Challenge of Distributed Data Management . . .
`Distributing Data-Practical Approaches
`Remote Database Access
`Remote Data Transparency
`TableExtracts...
`Table Replication .
`Updateable Replicas
`Replication Tradeoffs
`Typical Replication Architectures . . . . .
`Distributed Database Access
`Remote Requests
`RemoteTransactions . . . .
`DistributedTransactions ...
`Distributed Requests
`The Two-Phase Commit Protocol
`Network Applications and Database Architecture . .
`Client/Server Applications and Database Architecture
`Client/Server Applications with Stored Procedures . . . . .
`Enterprise Applications and Data Caching
`High-Volume Internet Data Management
`Summary
`
`23
`
`SQL and Objects
`Object-Oriented Databases . .
`Object-Oriented Database Characteristics . . . .
`Pros and Cons of Object-Oriented Databases
`Objects and the Database Market
`Object-Relational Databases .
`Large Object Support
`BLoBs in the Relational Model
`Specialized BLOB Processing .
`Abstract (Structured) Data Types
`Defining Abstract Data Types
`Manipulating Abstract Data Types . . .
`Inheritance
`Table Inheritance-Implementing Object Classes
`Sets, Arrays, and Collections
`Defining Collections
`Querying Collection Data .
`Manipulating CollectionData . . . .
`Collections and Stored Procedures
`User-Defined Data Types
`Methods and Stored Procedures
`Summary
`
`751,
`752
`758
`758
`762
`764
`766
`769
`770
`771,
`776
`777
`778
`779
`780
`782
`785
`786
`787
`789
`790
`792
`
`793
`794
`794
`796
`797
`798
`799
`799
`801
`803
`805
`808
`809
`812
`815
`817
`820
`822
`823
`825
`826
`830
`
`World Programming Limited EXHIBIT 1010
`Page 19 of 167
`
`
`
`ffi ñn 24 The Future of SQL
`Database Market Trends
`Enterprise Database Market Maturity
`Market Diversity and Segmentation . .
`Packaged Enterprise Applications . . .
`Hardware Performance Gains
`Benchmark Wars
`SQL Standardization
`SQL in the Next Decade
`Distributed Databases
`Massive Data Warehousing
`Ultra-High-Perf ormance Databases
`hrternet and Network Services Integration
`Embedded Databases
`Object Integration
`
`'ffi9Ë
`
`ffi$$
`
`EtrI
`A The Sample Database
`B Database Vendor Profiles
`A2i,Inc. (www.a2i.com) . .
`Angara Database Systems (www.angara.com)
`Arbor Software (www.hyperion.com)
`Ardent Software (www.ardentsoftware.com)
`Centura Software (www.centurasoft.com)
`Cloudscape, Inc. (www.cloudscape.com)
`Computer Associates (www.cai.com)
`Computer Corporation of America (www.cca-int.com)
`Empress Software (www.empress.com)
`IBM Corporation (www.ibm.com)
`Informix Software (www.informix.com)
`Microsoft Corporation (www.microsoft.com)
`Object Design (www.odi.com) . . .
`Objectivity (www.objectivity.com)
`Oracle Corporation (www.oracle.com)
`Persistence Software (www.persistence.com)
`Pervasive Software (www.pervasive.com)
`Quadbase Systems (www.quadbase.com) . .
`Raima Corporation (www.raima.com)
`Red Brick Systems (www.redbrick.com)
`Rogue Wave Software (www.roguewave.com)
`Sybase, Inc. (www.sybase.com)
`
`Contents
`
`831
`832
`833
`834
`834
`835
`837
`839
`840
`841
`841,
`842
`843
`843
`844
`
`849
`
`857
`859
`859
`859
`860
`860
`B61
`861.
`862
`863
`863
`864
`86s
`866
`867
`867
`868
`869
`869
`870
`870
`871
`871,
`
`World Programming Limited EXHIBIT 1010
`Page 20 of 167
`
`
`
`xx SQt: The ComPlete Ref erence
`
`Tache Group (www.tachegroup.com)
`Tandem Computers (www.tandem.com)
`TimesTen Performance Software (www.timesten'com)
`Versant Corporation (www.versant.com)
`C CompanY and Product List
`D SQL Syntax Reference . . . . . .
`Data Definition Statements' . .' . .'
`Basic Data Manipulation Statements
`Transaction Processing Statements
`Cursor-BasedStatements . ' ' '....
`Query Expressions
`Search Conditions
`Expressions
`Statement Elements
`Simple Elements
`
`ts
`
`SQL Call Level Interface
`CLI Return Values
`General Handle Management Routines
`SQL Environment Management Routines
`SQL Connection Management Routines
`SQL Statement Management Routines . . '
`SQL Statement Execution Routines
`Query Results Processing Routines
`Query Results Description Routines
`Query Results Descriptor Management Routines
`Deferred Dynamic Parameter Processing Routines
`Error, Status, and Diagnostic Routines . ' . .
`CLI Implementation Information Routines
`CLI Parameter Value Codes
`F SQL Information Schema Standard
`SCHEMATA ViCW .
`TABLES View
`COLUMNS View .
`VIEWS View
`V]trW TABLE-USAGEViCW
`VIEW-COLUMN-USAGEView' . .' . .
`TABLE_CONSTRAINTS ViCW . '....
`REF'ERENT IAL-CONSTRA]NTS ViCW
`CHECK-CONSTRAINTSViCW .. .'..
`KEY-COLUMN_USAGE ViCW
`
`873
`873
`874
`874
`
`877
`
`885
`886
`BB8
`889
`889
`890
`892
`892
`893
`893
`
`895
`898
`898
`898
`899
`901,
`902
`902
`904
`904
`906
`906
`907
`908
`
`915
`917
`918
`919
`920
`921
`922
`922
`923
`924
`925
`
`World Programming Limited EXHIBIT 1010
`Page 21 of 167
`
`
`
`ASSERTIONS View
`CONS TRA INT_TABLE_USAGE View
`CONSTRAINT_COLUMN_USAGE View
`TABLE_PRIVILEGES View
`COLUMN_PRIVTLEGES View ......
`USAGE_PRTVILEGES View
`DOMAINS View .
`DOMAIN_CONSTRAINTS View . . ., .
`DOMATN_COLUMN_USAGN View
`CHARACTER-SETS View
`COLLATIONS View
`TRANSLATIONS View
`SQL_LANGUAGES View
`
`'.,",i1,i G CD-ROM Installation Guide
`Installing the SQL DBMS Software
`MicrosoftSQLServerT .........
`Hardware and Soffware Requirements
`SQL Server Services User Accounts
`SQL Server 7.0 Installation . . .
`Starting SQL Server 7.0 . .
`Stopping SQL Server 7.0 . . .
`Uninstalling SQL Server 7.0 . .
`
`OracleB
`
`Products Included on the CD
`Hardware and Software Requirements
`Products Available for Installation . . . . .
`OracleS Personal Edition Installation . . .
`Starting OracleS Personal Edition
`Stopping OracleS Personal Edition . . . . .
`Uninstalling Oracle8 Personal Edition . .
`Informix
`Hardware and Software
`Informix User Accounts
`Informix Personal Edition Installation
`Starting Informix Personal Edition . . .
`Stopping Informix Personal Edition . .
`Troubleshooting Installation Problems
`Uninstalling Informix Personal Edition
`
`Requirements
`
`Sybase
`
`Hardware and Software Requirements
`Sybase Adaptive Server Anywhere Installation
`Starting Sybase Adaptive Server Anywhere
`Stopping Sybase Adaptive Server Anywhere
`Uninstalling Sybase Adaptive Server Anywhere
`
`Contents
`
`925
`926
`926
`927
`928
`929
`930
`931.
`932
`932
`933
`934
`935
`
`937
`939
`939
`940
`940
`941
`942
`943
`943
`944
`944
`946
`947
`948
`950
`950
`950
`951,
`952
`952
`952
`954
`954
`955
`955
`956
`956
`957
`958
`958
`958
`
`World Programming Limited EXHIBIT 1010
`Page 22 of 167
`
`
`
`E
`
`sQt: The comptete Reference
`
`IBM DB2
`Hardware and Software Requirements
`DB2 User Accounts
`DB2 Personal Edition Installation
`Starting DB2 Personal Edition
`Stopping DB2 Personal Edition
`Verifying the Installation
`Troubleshooting Installation Problems
`Uninstalling DB2 Personal Edition . . .
`
`Index
`
`959
`959
`960
`96L
`962
`962
`963
`963
`963
`965
`
`World Programming Limited EXHIBIT 1010
`Page 23 of 167
`
`
`
`6
`
`:1,
`
`:T
`
`f
`
`ì
`
`.i
`
`l
`
`,i::rjl
`ii
`
`I
`
`World Programming Limited EXHIBIT 1010
`Page 24 of 167
`
`
`
`4 SQt: The ComPlete Ref erence
`
`fnhe SQL language and relational database systems based on it are one of the most
`' I 'ilñ;nt
`fi.rñdation technologies in the computer industry today. Over the last
`I ä;äJ",in" pop.rtutity of SQL ñas exploded, and it stands today as the standatd
`computer database lättg,-tuge. Literally hundreds of database products now support
`SeL, running or-r.o*pirter systems fiom mainframes to personal computers and even
`handheld delices. An officiai international SQL standard has been adopted an$
`expanded twice. Virtually every major enterprise software product relies on SQL for its
`data management, and Sbl is át tfré core of îh" dutubutu pioducts from Microsoft and
`Oracle, twã of the largest software companies in the world. From its obscure
`u"glr-rr-rlrrg, as an lBM"research project, sQL nus leaped to prominence as both an
`im"portanl computer technology and a powerful market force'
`is SQL? Wh'y is it imþortant? What can it do, and how does it work?
`'Whut,
`"ru.ily,
`If SeL is really á standárd, *hy ut" there so many different v^ersions and dialects? How
`Jofoprrtu, SeL products like bQL S"trr"t, Oraclé, Informix, Sybase, and DB2 compare?
`Ho* äoe, SQL rãlate to Microsoft standards, such as ODBC and COM? How does
`JDBC link sQL to the world of Java and object technology? Does sQL really scale from
`mainframes to handheld devices? Has it really delivered the performance needed for
`high-volume transaction processing? How will SQL impact.the way you use
`coäputers, and how .un yorl get thle most out of this important data management tool?
`
`I -.I lne SQL Language
`sQL is a tool for organizing,managing, and retrieving data stored by a computer
`daiabase. The namð "SQL"ls an abbreviation for Structured Query Languøge.Fot
`historical reasons, SQL is usually pronounced "sequel," but the alternate pronunciation
`t9
`"S.e.L." is also used. As the nameìmplies, SQL is a computetlanguøge that you ":9
`inteiact with a database. In fact, SQLïorks with one specific type of database, called a
`relgtionøI dstabase.
`Figure 1-L shows how sQL works. The computer system in the figure lnas a dnta-bøse
`in a business, the database
`that sìãres important information. If the compnter
`"ystem^is
`might store inïentory, production, sales,,or payroll data. On a-personal computer, the
`daìábase might store dåta about the checks you have written, lists of people and their
`phone numb"ers, or data extracted from a larger computer system. The computer
`þrogrunl that controls the database is called a datøbase management system, or DBMS'
`'
`irynutr you need to retrieve data from a database, you use the SQL language.to
`make the request. The DBMS processes the sQL request, retrieves the requested
`data, and ,"irrrr-r, it to you. This process of requesting data from a database and
`receiving back the ,"rúlt, is caléd a database query-hence the name Structured
`Query Language.
`The name structured Query Language is actually somewhat of a misnomer' First of
`all, SeL is far more than a qruiy toof although that was its original purpose and
`
`World Programming Limited EXHIBIT 1010
`Page 25 of 167
`
`
`
`Chapter 1: lntroduction 5
`
`$¡""**-'f
`
`SQL Request
`
`010011
`110101
`101110
`
`Data
`
`010011
`110101
`101110
`
`il lil il llll
`
`Database
`Management
`System
`
`il
`
`Computer System
`
`Flgure 1-1. Using SQL for database access
`
`retrieving data is still one of its most important functions. SQL is used to control all of
`the functions that a DBMS provides for its users, including:
`I Døta definition. SQL lets a user define the structure and organization of the
`stored data and relationships among the stored data items'
`I Datø retrieaal. SQL allows a user or an application program to retrieve stored
`data from the database and use it'
`I Dats mnnipulation SQL allows a user or an application program to update the
`database by adding new data, removing old data, and modifying previously
`stored data.
`I Access control. SQL can be used to restrict a user's ability to retrieve, add, and
`modify data, protecting stored data against unauthorized access.
`I Data sharing. SQL is used to coordinate data sharing by concurrent users,
`ensuring that they do not interfere with one another'
`I Data integrity. SQL defines integrity constraints in the database, protecting it
`from corruption due to inconsistent updates or system failures.
`SQL is thus a comprehensive language for controlling and interacting with a
`database management sYstem.
`second, sQi is not really a complete computer language like CoBoL, C, C++, or
`]ava. SQL contains no IF statement for testing conditions, and no GOTO, DO, or F OR
`statements for program flow control. Instead, SQL is a database sublnnguage, consisting
`
`World Programming Limited EXHIBIT 1010
`Page 26 of 167
`
`
`
`6 SQt: The ComPlete Ref erenGe
`
`ofaboutfortystatementsspecializedfordatabasemanageme-ttasks'TheseSQL
`statements can be embeddedinto another language, suchãs COBOL or C, to extend that
`;"; use in database access. Alternatively, they can be explicitly sent to a
`;õ;ù;
`d atabase management-ttt** for processing, viá a cali leael interface from a language
`such as C, C++, or java.
`Finally, SQL is not a particularly structured language, especially when compared to
`r,ighly ,ti".tr.ied languág"s ,u.h as C, pascal, or Java. Instead, sQL statements
`resemble English r""i;;"";;complete with "noise words" that don't add to the meaning
`of the statement but make it ,"ud. *o." naturally' There-are quite a few inconsistencies
`in the sQL language, and there are also some special rules to prevent you from
`construcìing SéL ãtatements that look perfectly legal, but don't make sense'
`lrr nu-",'SQL haä erierged as the standard language for
`Despite the inaccuiã"1
`using relational databaseá. SqL is both a powerful lañguage and one that is relatively
`"r
`easy to learn. The qrri"t to.rrãf SeL in thä next chaptei will give you a good overview
`of tire language and its capabilities'
`
`-
`
`The Role of SQL
`sQL is not itself a database management system, nor is it a stand-alone product' You
`cannot go into u .o*f.ri"istore aîd "buy bgl." Instead, SQL is an integral part of a
`databasemanagementsystem,alanguageandatoolforcommunicatingwiththe-
`DBMS. Figure 1-2 shows some of thðcomponents of a typical DBMS, and how SQL acts
`as the "glue" that links them together'
`T,,e datøbase engineis the heärt of the DBMS, responsible for actually structuring'
`storing, and retrievi"g ih" á"t" in the database. It aicepts SQL requests from other,..
`DBMS components, ,ir.n u' a forms facility, report writer, or interactive query facility,
`fromuser-writtenapplicationprograms,andevenfromothercomputersystems.As
`the figure shows, SQL plays many different roles:
`f SQL is aninteractíae query languøge' Users type SQL commands into an
`interactive Sqi p.ograrrito ,ãtriãrre data unã aisptay it on the screen, providing
`a convenient, easy-tó-use tool for ad hoc database queries'
`rSQLisadøtrlbnseprogrrlmminglønguøge,ProgrammersembedSQLcommands
`into their application programs to ac-cess thé data in a database. Both
`user-written p.ãjru-i urä dutubure utility programs (such as report writers
`and data entry tðols) use this technique for database access'
`rsQLisadatøbnseadministrationlnnguage,Thedatabaseadministrator
`- rfiponsible for managing a minicõmp'uter or mainframe database uses SQL to
`define the database stîuc"ture and control access to the stored data.
`rSQLlsaclimt/seraerlanguøge.Personalcomputerproql"ouseSQLtocommunicate
`ovef a network with daiabãse servels that store shared data' This client/server
`architecturehasbecomeverypopularforenterprise-classapplications'
`
`World Programming Limited EXHIBIT 1010
`Page 27 of 167
`
`
`
`Chapter 1: lntroduction 7
`
`f SQL is anlnternet data access language.Internet web servers that interact with
`corporate data and Internet applications servers all use SQL as a standard
`language for accessing corporate databases.
`f SQL is a distributed database language. Distributed database management systems
`use SQL to help distribute data across many connected computer systems. The
`DBMS software on each system uses SQL to communicate with the other
`systems, sending requests for data access.
`f SQL is a database gatewny løngunge.In a computer network with a mix of
`different DBMS products, SQL is often used in a gateway that allows one brand
`of DBMS to communicate with another brand.
`
`SQL has thus emerged as a useful, powerful tool for linking people, computer
`programs, and computer systems to the data stored in a relational database.
`
`m
`
`010011
`110101
`101110
`
`Programrning
`Tools
`
`t
`
`Forms
`Facility
`
`Report
`Writer
`
`Interactive
`Query Tool
`
`Application
`Program
`
`Database
`Front-end
`
`SQL
`
`SQL
`
`SQL
`
`SQL
`
`SQL
`
`ü
`
`SQL
`
`to other
`computer
`systems
`
`Database
`Engine
`
`t
`
`Database
`
`SQL
`
`Database
`Gateway
`
`to other
`DBMS
`brands
`
`Fl$ulc L2. Campanents of