II{ A IüT]TSHEII
`Quick Reþrence
`
`A
`
`Keuin Kline with Daniel Kline
`
`30,07195740
`
`i
`
`Ll
`
`@
`
`iI
`
`O'REIL
`
`World Programming Limited EXHIBIT 1007; Page 1 of 98
`
`

`

`@
`Umiverstry cf
`Tsmls l-åhnrmicç
`"l'l'14 tlNtvlSSI'IY LiJÍ TÍ'-9Ê¡& AT *rfrmlt
`
`World Programming Limited EXHIBIT 1007; Page 2 of 98
`
`

`

`THE UNIVERSITY OF TEXAS ATAUSTIN
`UNIVERSITY OF TEXAS LIBRARIES
`
`DUE
`
`PCL PCL PCL
`t]IC 0 4 Zoon
`
`FGt FAb pOl"
`
`t'lAY 1 0 ¿t]tlÍ
`
`RETURNED
`
`PCL
`
`N0\ 0Ð ?$$$
`
`NAT U I ¿UUq.
`
`O'REILLY"
`BeiJtng. Cømbríd.ge' Førnhøm' Köln' Pørls' Sebastopol' Tøtþet' Tokyo
`
`World Programming Limited EXHIBIT 1007; Page 3 of 98
`
`

`

`THE GENERAL LIBRARIES
`
`DUE
`
`l! nll PC
`AU6 2 5 2004
`
`r -' r^1. rlcL
`
`uAt{ 18 2005
`
`Fc[ p
`Fr8 f
`
`0r
`5 2005
`
`Pc¿ Pc¿
`AUï 31 2005
`PTL PCL PCL
`stP 21 2005
`trct PcL
`
`Pcr'#rt'åogt
`AUG r I 2006
`
`RET'f,1 pCl_
`
`JUN 1 02004
`
`RET'D PCL
`
`,WÊl'6t0féL
`APR 2 7 2005
`RË ¡'',rl [i'i i,:,\il_. f,{;L
`ApR Z s 200b
`REI
`SEt o2 ¿oou
`
`i
`
`RET'F Þ{ll
`mffirffiOFcr
`sEP ?2?006
`
`ITAT U I ¿UUil
`
`O'REILLY"
`Betflng' Cømbrtdge ' Førnltøtn' Kötn ' Pørts ' Sebastopol ' Tøtøel ' Tokyo
`
`World Programming Limited EXHIBIT 1007; Page 4 of 98
`
`

`

`PERRY-CASTAÑEDA LIBRARY
`
`I
`
`DATE DUE
`
`DATE RETURNED
`
`JUN 0 6 2001
`
`PCL PCL
`JUN 0 6 2001
`
`PCL PCL PCL
`AUG 0 7 2003
`
`. Pc[ pcr
`
`AUC 3 1 ?r¡03
`pcl PCt" PCL
`OcT 1 2?t03
`
`PCL PCI- PCL
`POü¡N
`
`JUN 0 3 2f/04
`
`RET'D PCL
`MAY 1 2 2001
`
`RET'D PCL
`JUN 2 02003
`
`RET'D PCL
`AUo 0B 2003
`
`I RET'D PCL
`
`AU0 3 1 2003
`
`RET'D PCL
`n#b Êfrðßt
`8Y&md& "PCL
`Bñru¡ ßßL
`ilAY 072004
`
`O'REILLY'
`BetJtng' Cømbrtdge' Farnbam' Köln' Pøds' Sebøstopol' Tøtpel' Tokyo
`
`World Programming Limited EXHIBIT 1007; Page 5 of 98
`
`

`

`S
`II{ AI{UTSHELI
`A Desktop Quick Reþrence
`
`Ft
`
`Keuin Kline witb Daniel Kline
`
`O'REILLY"
`BetJtng' Cambrldge' Fambøm' Köln' Pøtls' Sebøstopol' Tøtþet' Tokyo
`
`World Programming Limited EXHIBIT 1007; Page 6 of 98
`
`

`

`SOL in a Nutshell
`by Kevin Kline with Daniel Kline
`
`Copyright @ 2001 O'Reilly & Associates, Inc. All rights reserved.
`Printed in the United States of America.
`
`Published by O'Reilly & Associates, Inc., 101 Morris Street, Sebastopol, CA95472.
`
`Editor: Gigi Estabrook
`
`Production Editor: Mary sheehan
`
`C ove r D esi g n o t: EllieVolckhausen
`
`Printing History:
`January 20ß1: First Edition.
`
`Nutshell Handbook, the Nutshell Handbook logo, and the O'Reilly logo are
`registered trademarks of O'Reilly & Associates, Inc. The association between the
`image of a chameleon and the topic of SQL is a trademark of O'Reilly & Associates,
`Inc.
`
`Many of the designations used by manufacturers and sellers to distinguish their
`products are claimed as trademarks. IØhere those designations appear in this book,
`and O'Reilly & Associates, Inc. was aware of a trademark claim, the designations
`have been printed in caps or initial caps.
`
`\ühile every precaution has been taken in the preparation ofthis book, the publisher
`assumes no responsibility for errors or omissions, or for damages resulting from the
`use of the information contained herein.
`
`Ltbrury of Congress Cøtølogtng-ín-Publlcatlon Datø
`
`Kline, Kevin 8., 1966-
`SQL in a nutshell : a desktop quick reference,/Kevin Kline with Da4iel Kline.
`p.cm.
`Includes index.
`rsBN L-56592-744-3
`1.. SQL server 2. SQL (Computer progfam language) 3. Client/server computing.
`I. Kline, Daniel.
`Q 76,73.567 K55 2000
`005.75'85-dc21
`
`00-065206
`
`rsBN: 1.-56592-744-3
`tMì
`
`World Programming Limited EXHIBIT 1007; Page 7 of 98
`
`

`

`u I 1
`
`.)) 6 7 9
`
`,9
`10
`10
`18
`
`1.9
`23
`26
`
`)7
`
`?'7
`
`27
`
`9i6
`162
`
`Table of Contents
`
`Preþce
`
`Cbapter 1 -SQL, Vendor Implementøtíons,
`ønd Some History
`The Relational Database Model
`The Databases Described in This Book ..-.........,
`The SQL Standard
`Dialects of SQt
`Principles of Relational Databases
`
`Cbapter 2 -Foundationøl Concepts
`Row Processing Versus Set Processing
`The Relational Model
`SQt99 and Vendor-Specific Datarypes
`Processing NULLS
`Categories of Syntax
`Using SQL
`Conclusion
`
`Cbøpter 3-S8L Støtements Comm.and Reference
`Recommended Reading Approach
`Quick SQL Command Reference .,
`DROP Statements
`Conclusion
`
`llt
`
`World Programming Limited EXHIBIT 1007; Page 8 of 98
`
`

`

`Cltøpter 4 -SQL Functlons
`Deterministic and Nondeterrninistic Functions
`Types of Functions
`Vendor Extensions
`Cbapter 5-tlntmplemented' SQL99 Commands """"""""' I94
`Append,tx - SgLgg ønd Vend'or-Spe clfic Kqtwords " " " " " " "' I 9 7
`Ind,ex
`" 205
`
`163
`163
`164
`... r75
`
`lu Tøble of Contents
`
`World Programming Limited EXHIBIT 1007; Page 9 of 98
`
`

`

`Preface
`
`The explosive growth of the information technology industry and the constantly
`growing need to compile, store, access, and manipulate increasingly larger masses
`of data have required the development of ever more sophisticated database
`management tools.
`Since its first incarnation in the 1970s, Structured Query Language (SQL) has been
`developed hand in hand with the information boom, and as a result, is the most
`widely used database manipulation language in business and industry. A number
`of different software companies and program developers, including those in the
`open source movement, have concurrently developed their own SQL dialects in
`response to specific needs. All the while, standards bodies have developed a
`growing list of common features.
`SQI in a Nutsbell identifies the differences befween the various vendor implemen-
`tations of SQL. Readers will find a concise explanation of the Relational Database
`Management System (RDBMS) model, a clear-cut explanation of foundational
`RDBMS concepts, and thorough coverage of basic SQL syntax and commands.
`Most importantly, programmers and developers who use SQL in ø Nutsbell will
`find a concise guide both to the most popular commercial database packages on
`the market (Microsoft SQL Server and OracleSl), and to two of the best known
`open source (bttp://www.opensource.orp) database products (MySQt and
`PostgreSQl). SQI in a Nutsbell's attention to open source SQL products is an affir-
`mation of the growing importance of the open source movement within the
`computing community.
`As a result, SQL in ø Nutsbell benefits several distinct groups of users: the knowl-
`edgeable programmer who requires a concise and handy reference tool, the
`developer who needs to migrate from one SQL dialect to another, and the user
`who comes to SQL from another programming language and wants to learn the
`basics of SQL programming.
`
`World Programming Limited EXHIBIT 1007; Page 10 of 98
`
`

`

`CHAPTER 1
`
`SQL, Vendor Implementøtions,
`ønd Some History
`
`In the 1970s, IBM developed a product called SEQUEL, or Structured English
`Query Language, which ultimately became SQL, the Structured Query Lønguøge.
`IBM, along with other relational database vendors; wanted a st?ind^rdized method
`for accessing and manipulating data in a relational database. Over the decades,
`many competing languages have allowed programmers and developers to access
`and manipulate data. However, few have been as easy to learn and a.s universally
`accepted as SQL. Programmers and developers now have the benefit of learning a
`language that, with minor adjustments, is applicable to a wide variety of database
`applications and products.
`SQL in ø Nutsbell describes four implementations of the current SQL standard,
`SQt99 (also known as SQL3): Microsoft's SQL Server, MySQL, Oracle, and
`PostgreSQl. For those migrating from implementations of the eadier SQL stan-
`dard, this chapter describes the current SQL standard and the ways in which it
`differs from the earlier standard. This chapter also provides a bit of history of the
`standards evolution.
`
`Tbe Reløtional Database Model
`Relational Database Management Systems (RDBMSs), such as SQL Server and
`Oracle, are the primary engines of information systems worldwide, particularþ
`Internet/Intranet applications and distributed client/server computing systems.
`An RDBMS is defined as a system whose users view data as a collection of tables
`related to each other through cofirmon data values. Data is stored in tables, and
`tables are composed of rows and columns. Tables of independent data can be
`linked (or related) to one another if they each have columns of data (called keys)
`that represent the same data value. This concept is so common as to seem trivial;
`however, it was not so long ago that achieving and programming a system capable
`of sustaining the relational model was considered a long shot that would have
`limited usefulness.
`
`1
`
`World Programming Limited EXHIBIT 1007; Page 11 of 98
`
`

`

`Relational data theory was developed by E' F' Codd in the 1960s Codd
`compiled a list of critéria ^ dat^base product must meet to be considered rela-
`tional. For those who are curious, Codd's list appears at the end of this
`chapter.
`
`Tbe Databases Described in Tbis Book
`SQlinaNutsbelldescribestheSQLstandardandthevendorimplementa-
`tiãns of four leading RDBMSs-fwo that are from leading commercial vendors
`(MicrosoftSQLServerandOracle)andtwothatarefromthechiefopen
`source database projects (MySQL and PostgreSQl):
`Microsoft SQL Seraer
`Microsoft SQL Server is a popular RDBMS that runs only on the 'üØindows
`platform. Its features include ease of use, low cost, and high performance'
`This book covers Microsoft SQL Server 2000'
`MvSOL
`lySQf is a popular open source Database Management System (DBMS) that
`is known fãr its blistering performance. It rLtns on numerous operating
`Systems,includingmostLinuxvariants.Toimproveperformance,ithasa
`slimmer feature set than clo many other DBMSs. Its critics point out that it is
`not a fully relational DBMS since it does not support many key features of
`relational databases, particulady in how it processes transactions. This book
`covers MySQL 3.22.9.
`
`Oracle
`oracle is a leading RDBMS in the commercial sector. It runs on a multitude of
`operating systems and hardware platforms. Its scalable and reliable architec-
`turehavemadeittheplatformofchoiceformanyusers.Becauseoftheir
`highly tunable nature, oracle RDBMSs require a well-trained database admin-
`istrator (DBA). SQL in a Nutsbellcovers Oracle Release B 1'
`
`PostgreSQL
`PostgreSQlisoneofthemostfeature-richRDBMssoftheopensourceworld.
`Its compliance with SQL standards is unmatched by other open source
`RDBMd. In addition to its ¡ich ser of featufes, posrgreSQl runs on a wide
`variery of operating systems and hardware platforms' This book covers
`PostgreSQl 6.5.
`
`Tbe SQL Standørd
`To bring greater conformity among vendors, the American National Standards
`Institute (ÑSI) published its first SQL standard in 1986 and a second widely
`adopted standard in 1989. ANSI released updates In 1992, known as SQL92 and
`SqiZ, and again in 1999, termed both SQL99 and SQL3' Each time, ANSI added
`new features ancl incorporatecl new commands and capabilities into the language'
`unique to the SQt99 standard is a group of capabilities that handle object-oriented
`dututyp" extensircns. The International standards organization (ISO) has also
`
`2 Cbapter 1- SQf, Vendor Implementcttions, ønd Some History
`
`World Programming Limited EXHIBIT 1007; Page 12 of 98
`
`

`

`approved SQt99. An important change from SQL92 is that SQL99 expands on
`SQL92's leuels of confoflnønca
`
`Leuels of Conþrmance
`SQLp2 first introduced levels of conformance by defining three categories: Entry,
`Intermediate, and Full. Vendors had to achieve Entry-level conformance to claim
`ANSI SQL compliance. The U.S. National Institute of Standards and Technology
`(NIST) later added the Transitional level between the Entry and Intermediate
`levels. So, NIST's levels of conformance were Entry, Transitional, Intermediate, and
`Full, while ANSI's were only Entry, Intermediate, and Full. Each higher level of the
`standard was a superset of the subordinate level, meaning that each higher level of
`the standard included all the features of the lower level of conformance.
`SQL99 altered the base levels of conformance. Gone aré the Entry, Intermediate,
`and Full levels of conformance. Iíith SQL99, vendors must implement all the
`features of the lowest level of conformance, Core SQL:1999, in order to claim (and
`publish) that they are SQL99 ready. Core SQL:1999-or Core SQLpp, for short-
`includes the old Entry SQL92 feature set, features from other SQL92 levels, and
`some brand new features. This upgrade to the SQL standard enabled vendors to
`go quickly from the Entry SQL92 feature set to the Core SQL99 feature set.
`'$Øhereas SQL!2 featured the Intermediate and Full levels of conformance, SQL99
`has Enbanced SQI:1999. Any DBMS that supports the Core SQL99 benchmarks,
`plus one or more of nine additional feature packages, is now said to meet
`Enhanced SQL:1999 standards defined in SQL99 (also called Enhanced SQL99).
`
`S upþ le m ent al Fe ature s Pa.ck a.ge s
`The SQL99 standard represents the ideal, but very few vendors immediately meet
`or exceed the Core SQL99 requirements. The Core SQL99 standard is like the inter-
`state speed limit: some drivers go above, others go below, but few go exactly the
`speed limit. Similarþ, vendor implementations can vary greatly.
`Two commìttees-one within ANSI and the other within ISO-composed of
`representatives from virtually every RDBMS vendor drafted these definitions. In
`this collaborative and somewhat political environment, vendors must compromise
`on exactly which proposed feature and implementation will be incorporated into
`the new standard. Many times, a nèw feature in the ANSI standard is derived from
`an existing product or is the outgrowth of new research and development from
`the academic community. Consequently, marty vendors adopt some features in the
`standard, and later add still more.
`The nine supplemental features packages, representing different subsets of
`commands, are vendor-optional. Some SQL99 feanrres might show up in multiple
`packages, while others do not
`in any of the packages. These packages and
`^ppex
`their features are described in Table 1-1.
`
`TbeSQLStandard 3
`
`World Programming Limited EXHIBIT 1007; Page 13 of 98
`
`

`

`Tøble 1-1: SQL99 Supplenxental Feøtures Packøges
`
`ID
`PKGOOl
`
`Name
`Enhanced datetime
`facilities
`
`PKGOO2
`
`Enhanced integrity
`management
`
`PKGOO3
`
`OLAP capabilities
`
`PKGOO4
`
`SQL Persistent Stored
`Modules (PSM)
`
`PKGOO'
`
`SQL Call-level Interface
`(CLI)
`
`PKG006
`
`Basic object support
`
`PKGOOT
`
`Enhanced object
`suppoft
`
`Features
`. Interval datatype
`. Time zone specification
`. Full datetime
`. Optional interval qualifier
`. Assertions
`. Referential delete actions
`. Referential update actions
`. Constraintmanagement
`. Subqueries in CHECK constraint
`. Triggers
`. FOR EACH STÀTEMENT triggers
`. Referential action RESTRICT
`. CUBE and ROLLUP
`. INTERSECT operator
`. Row and table constructs
`. FULL OUTERJOIN
`. Scalar subquery values
`. A Droqrammatic extension to SQL that makes it
`suitable for developing more functionally
`complete applications
`. The commâ¡ids c¡,sn, IF, v¡HItE, REPEAT'
`LOOP, and FOR
`¡ Stored Modules
`. Computationalcompleteness
`. INFORMATION-SCHEMAviews
`o gQt- Call-level Interface support: an Application
`Próeramminq Interface (API) that enables SQL
`opeiations tõ be called that is very similar to the
`Open Database Connectivity (ODBC) standard
`. Overloading SQl-invoked functions'and
`Þrocedures
`. User-del-ined rypes with single inheritance; basic
`SQL routines óñ user-definèd types (including
`dynamic dispatch)
`. Reference types
`. CREATE TABLE
`. Array support: basìc anay support, array
`expressiôns, array locators, user-datarype (UDT)
`arräy support, reference-rype amay support, SQL
`routine on arravs
`. Attribute and fiêld reference
`. Reference and dereference operâtions
`. AITERTABTE,ADD
`. Enhanced user-defined rypes (including
`constructor options, attribute defaults, multiple
`inheritance, ahd ordering clause)
`. SQL functions and type-name resolution
`. Subtables
`. ONLY in queries
`' TYPe Predicate
`. Subtype treâtment
`. User-defined CAST functions
`. UDT locators
`. SQL routines on user-defined types such as
`idèntiry functions and generalized expressions
`
`4 Chapter 1- SQL, Vendor Implernentations, a.nd Some Histoty
`
`World Programming Limited EXHIBIT 1007; Page 14 of 98
`
`

`

`Table 1-L: SQL99 Suþþlenxental Features Packages (continued)
`
`u,
`
`aÈ
`
`ID
`PKGO08
`
`PKGOO9
`
`Name
`Àctive database
`features
`SQL Multimedia (MM)
`support
`
`Features
`. Triggers
`
`Handling for streaming multimedia data and for
`large and complex audio and video data
`
`Be aware that a DBMS vendor may claim Enhanced SQL99 compliance by meeting
`Core SQL99 standards plus only one of nine ødded packøgeq so read the vendor's
`fine print for a full description of its program features. By understanding what
`features comprise the nine packages, programmers and developers gain a clear
`idea of the capabilities of a particular DBMS, and how the various features behave
`when SQL code is transported to other database products.
`The ANSI standards-which cover retrieval, manipulation, and management of
`d^t^ in commands, such as SELECT, JOIN, ALTER TABIE, and DROP-formalized
`many SQL behaviors and syntax structures across a variety of products. These
`standards become even more important as open source database products, such as
`MySQL, miniSQL, and PostgresQl, grow in popularity and are developed by
`virtual teams rather than large corporations.
`SQL in a Nutshell explains the SQL implementation of four popular RDBMSs.
`These vendors do not meet all the SQL99 standards; in fact, all RDBMS vendors
`play a constant game of tag with the standards bodies. Many times, as soon as
`vendors close in on the standard, the standards bodies update, refine, or other-
`wise change the benchmark.
`
`SpL99 Støtement Cløsses
`Comparing statement classes further delineates SQL92 and SQL99. In SQL!2, SQL
`statements are grouped into three broad categories: the Datø Mønipula,tíon
`Lønguøge (DMt), the Døta. Definition lønguage (DDL), and the Datø Control
`Lønguage (DCL). The DML provides specific data-manipulation commands such as
`SEIEC\ INSERT, UPDATE, and DELETE. The DDL contains commands that handle
`the accessibility and manipulation of database objects, including CREATE and
`DROP, while the DCL contains the permission-related commands GRANT and
`REVOKE,
`In contrast, SQL99 supplies seven Core categories that provide a general frame-
`work for the types of commands available in SQt. These statement "classes" are
`slightly different than the SQL92 statement classes, since they attempt to identify
`the statements within each class more accurately and logically. Furthermore,
`because SQL is constantly under development, new features and commands enter
`the standard and may necessitate nev/ statement classes. So, to accommodate
`future growth, SQL99 developed new sets of statement classes, making 'them
`somewhat more comprehensible and logical. Additionally, the new statement
`classes now allow some "orphaned" statements-which did not fit well into any of
`the old categories-to be propeily classified.
`
`The SQI Standørd 5
`
`World Programming Limited EXHIBIT 1007; Page 15 of 98
`
`

`

`I
`
`Table 1,-2 identifies the sQL99 statement classes and lists a few commands in each
`class,eachofwhichisf,-rllydiscussedlater'Atthispoint,thekeyistoremember
`the statement class title.
`
`Table 1-2: SQL Støtement Classes
`
`Class
`SQL Connection
`Statements
`SQL Control
`Statements
`
`SQL Data
`Statements
`SQL Diagnostic
`Statements
`SQL Schema
`Statements
`
`SQL Session
`Statements
`SQL Transaction
`Statements
`
`Descriqtion
`end a client connection
`Srafi
`
`Control the execution of a set of SQL
`statcments
`Have a persistent and enduring effect
`upon data
`Provide diagnostic information and raise
`exceptions and errors
`Have a ocrsistcnt and cnduring cffect on
`,r ,ìatabásc schema and obiects within that
`schema
`Control default behavior and other
`parameters for a session
`Set the stafting and ending point of a
`transaction
`
`ExamPle Commandi
`CONNECT,
`DISCONNECT
`
`CAIL,
`RETURN
`SELECT, INSERT,
`UPDATE, DELETE
`GET DIAGNOSNCS
`
`ALTER, CREATT, DROP
`
`SET
`
`COMMIT, ROLLBACK
`
`Those who work with sQL regulady should become familiar with both the old
`(SeL92) ancl the .,"w (Sòfggi statement classes, since many programmers and
`deielopers still use the old nomenclature to refer to current SQL features,
`
`Dialects of SgL
`The constantly evolving nature of the SQt standard has given rise to a number of
`iqf dlolr"r, among ihe various vendors and products. These dialects most
`.oìrrmo'ly evolved because the user community of a given database vendor
`required capabilities in the database before the ANSI committee created a stan-
`¿ur¿. O..urionally though, a new feature is introduced by the academic or
`research communities duè to competitive pressures from competing technologies'
`For example, many database vendors are augmenting their curfent pro8rammatic
`offerings ïiin ¡u.,n (as is the case with Orácle and Sybase) or VBScript (as
`Microsãft is doing). In the future, programmers and developers will use these
`programming languages in concert with SQL to build SQL programs'
`Nonetheless, each of these dialects includes conditional processing (such as that
`controlled through IF . . . T'IIEN statements), control-of-flow functions (such as
`'wHlLTloops), variables, and error handling. Because ANSI had not yet developed
`a standardior these important features, RDBMS developers and vendors were free
`to cfeate their own commands and syntax. In fact, some of the earliest vendors
`from the 1980s have variances in the most elementary commands, such as SELECT,
`because their implementations predate the standards. (ANSI is now refining stan-
`dards that address these shortcomings.)
`some of these dialects have introduced procedural commands to suppoft the func-
`tionality of a much more complete programming language For example' these
`pfoceclural implementations contain error-handling commands' contfol-of-flow
`
`6 Cba.þter 1- SQL, Vendor Implementations, a.nd Sonxe Hßtory
`
`World Programming Limited EXHIBIT 1007; Page 16 of 98
`
`

`

`\U
`
`t
`
`6=
`
`language, conditional commands, variable handling, arrays, and many other exten-
`sions. Although these are technically divergent procedural implementations, they
`are called diølects herc.
`
`Some popular dialects of SQL include:
`
`PL/SQL
`Found in Oracle. PtlSQL stands for Procedural Language,/SQl and contains
`many similarities to the language Ada.
`
`Transact-SQL
`Uses both Microsoft SQL Server and Sybase Adaptive Server. As Microsoft and
`Sybase have moved away from the common platform they shared early in the
`1990s, their implementations of Transact-SQl have also diverged.
`
`PL/þsSQL
`The name of the SQL dialect and extensions implemented in PostgreSQl. The
`acronym stands for Procedural Language/postgreSQl.
`However; even if a vendor conforms to the SQL99 standârds, its commands differ
`from other DBMSs because SQL statements may be parsed, compiled, and
`executed in different ways, especially if differing binding styles are used. There are
`three common binding styles:
`
`SQL Module La.nguøge
`Causes the SQL statements to be prepared when the module is created, and
`'
`executed when the module is called (like a stored procedure).
`Embedded SQL Syntax
`Allows the SQL statements to be prepared when the host language program is
`precompiled, and executed when the host program is called (like PRO*C or
`PRO*Fortran).
`Direct SQL Inuocation
`Causes a static SQL statement to be prepared then immediately executed.
`Therefore, differences in binding style may be pne more reason DBMSs function
`differently. Binding styles go deep into the heart of the database code. In general,
`the SQL commands discussed in this book utilize the Direct SQL Invocation
`binding style. However, when the situation warrants, other relevant binding styles
`are discussed within the command reference of each specific command.
`
`Princiþles of Relatiorløl Døtabases
`Following are E.F. Codd's Twelve Principles of Relational Databases. These princi-
`ples continue to be the litmus test used to validate the "relational" characteristics of
`a database product; a database product that does not meet all of these rules is not
`fully relational. These rules do not apply to applications development, but they do
`determine whether the database engine itself can be considered truly "relational."
`Currently, most RDBMSs pass Codd's test, including all of the databases discussed
`in this book, except MySQt. (MySQt does not currently support views or atomíc
`transactions. Therefore, it does not qualify as a true relational DBMS under Codd's
`rules.)
`
`Principles of Reløtional Datøbøses 7
`
`World Programming Limited EXHIBIT 1007; Page 17 of 98
`
`

`

`Codd.'s Rutesfor ø Truly Reløtlonal Døtøbøse System
`Codd's criteria provide the benchmarks for defining RDBs Knowing and
`understanding these principles will help you develop and design RDBs:
`1, Information is represented logically in tables'
`2.. Datamust be logically accessible by table, primary key' and column'
`3.Nullvaluesmustbeuniformlytreatedas..missinginformation,,,notas
`empty strings, blanks, or zeros'
`4. tvleiaâata (data about the database) must be stored in the database iust as
`regular data is.
`5. Aiingle language must be able to define data, yig-¡ys, integrity constraints'
`authorization, transactions, and data manipulation'
`6. Views must show the updates of their base tables and vice versa'
`7. A single operation -uri b. able to retrieve, insert, update' or delete data'
`g. Batch- anà end_user operations are logically sepafate from physical
`storage and access methods.
`9. Batcñ and end-user operations can change the database schema without
`having to recreate it or the applications built upon it'
`10. Integiity consrfainrs must be available and srored in the RDB metadata,
`not in an aPPlication Program.
`11. The data mànipulatión únguage of the relational system should not care
`whereorhowthephysicaldataisdistributedandshouldnotrequire
`alteration if the physical data is centralized or distributed'
`12. Any row processing done in the system must obey the same integrity
`rules and constraints that set-processing operations do'
`
`Knowing and understanding these principles assists progra-t:T:1d developers
`in the pioper development ãnd design of Relational Darabases (RDBs).
`
`8 Cbaþter 1- SQL, Vendor Implementøtions, ønd Some HßtotY
`
`World Programming Limited EXHIBIT 1007; Page 18 of 98
`
`

`

`-
`
`CHAPTER 2
`
`Foundationøl Concepts
`
`SQL provides aî easy, intuitive ìvay to interact with a database. The SQL99 stan-
`dard does not define the concept of a "database," but it does define all the
`functions and concepts needed for a user to create, retrieve, update, and delete
`data. lt is important to review a few of the concepts upon which the SQL standard
`is based.
`
`Rout Processìng Versus Set Processíng
`Other database manipulation languages, such as Xbase or Visual Basic, perform
`their data operations quite differently from SQL. These languages require the
`programmer to tell the program exactly how to treat the data, one record at a time.
`9lggg*t-þ-._.ptogram cycles down through a list of records, performing its logic on
`one record after another, this style of programming is frequently called row
`þrocessing or þrocedural prograrnming
`Þ*Q!plggg*" operate in logical sefs of data. Set theory is applied when the .FROrl,l
`cla¡¡sç is used, as i¡ the SELECT statement. In effect, data is selected from a set
`called a table. Unlike the row processing style, set processing allows a programmer
`to tell the database simply wbat is,required, not bow each individual piece of data
`should be handled. Sometimes, set processing is referred to as declaratiue
`processing, since a programmer declares only what data is necessary, as in "Give
`me all employees in the southern region who eam more than $70,000 per year,"
`rather than describes the exact procedure used to manipulate the data.
`
`{ I
`
`ffi-
`
`Set theory was the brainchild of Russian mathematician Georg
`Cantor, who developed it at the end of the nineteenth century. At
`the time, set theory (and his theory of the infinite) was quite contro-
`versial; today, set theory is such a common part of life that it is
`learned in elementary school.
`
`9
`
`World Programming Limited EXHIBIT 1007; Page 19 of 98
`
`

`

`Examples of set theory in conjunction with relational databases are detailed in the
`following section.
`
`Tbe Relational Model
`Effective SQL programming requires that the programmer think in terms of sets of
`data, rather than of individual rows. The RDBS model follows a linguistic protocol
`to define the hierarchy of data sets within the SQt99 standard.
`Figure 2-1 is a description of the SQL99 terminology used to describe the hierar-
`chical working sets used by a relational database---clusters contain sets of catalogs;
`catalogs contain sets of schemas; schemas contain sets of objects, such as tables
`and views; and tables and views are composed of sets of records.
`In the relational model, data is shown logically as a two-dimensional table that
`describes a single entity (for example, business expenses). Data in the table is
`displayed in columns and rows, Each column of the table describes a specific
`attribute of the entiry. For example, in a Business_Expense table, a column called
`Expense_Date might show when the expense was incured. Each record in the
`table describes a specific entity; in this case, everything that makes up a business
`expense (when it happened, how much it cost, who incumed the expense, what it
`was for, and so on). The specific values of each attribute are supposed to be
`a.tofttic; that is, they are supposed to contain one, and only one, value. If a table is
`constructed in which the intersection of a row and column can contain more than
`one distinct value, then one of SQL's primary design guidelines has been violated.
`There are rules of behavior specified for column values. Foremost is that the
`column values must share a common domøin, better known as a datatype. For
`example, the value 'ELMER' should not be placed into the Expense-Date field. The
`Expense_Date field should contain only dates; therefore, this column would be
`defined as having a date datatype. In addition, SQL99 further controls the values of
`such a field through the application of rules. A SQt rule might limit Expense_Dare
`to expenses less than a year old.
`Additionally, data access for all individuals and computer processes is controlled at
`the schema level by an <AutborizationlD> ot user. Permissions to specific sets of
`data may be granted or restricted to each user.
`
`Moreover, SQL databases also employ chøracter sets and colløtions. Character sets
`are the "symbols" used by the "language" of the data. Chancter sets can contain
`multiple collations. A collation is the basic set of rules that define how SQL sorts
`the data. For example, an American English character set might be sorted either by
`character-order, case-insensitive, or by character-order, case-sensitive.
`SQL9 9 and Vendor-Sþ ecifi.c Da,ta,types
`The previous section mentioned that a table could contain one or many columns,
`each with a single defining datatype. In ¡eal wodd applications, datatypes provide
`some control and efficiency as to how tables are defined. Using specific datatypes
`enables better, more understandable queries and controls the integrity of data.
`
`10 Chøpter 2- Foundational Concepts
`
`World Programming Limited EXHIBIT 1007; Page 20 of 98
`
`

`

`clusTlRs
`
`Conlain one
`or mony
`I
`ftTAtoGS
`
`ConlaÍn one
`0r
`
`\dnï
`scfiÍtrlÂs
`
`Conîoin one
`ot nonl
`
`I
`
`orJlcts
`
`A duster.is o uniquely nomed set of colologs ovoiloble lo q S0L sesion. Thh is
`;üË;!tïäï;iqïä# iilî ilöbilsproduc. According r, rl,, ¡rl¡¡ ;i;;läållv
`dusrers oko conrrot who sers oaes ro rhe dãi,, il¡ ;ñi ;;i;iüd¡,ffüi, **,
`fjgl'*:q:_f^ry"ry:f m0-st implemenr0ri0n'; su¿h ;' d;;¿Ëàn,i [,üffi ä'i0iieñ;
`rr0(K permßstons 0t the (0t0l0g l0yer.
`
`A cololog k o.uniquely nomed set of schemos.
`user, you might be more comfortoble with the
`
`lf youle on 0rocle or Mk¡osoft S0[ Server
`lerm infon(e.
`
`A xhemo is o uniquely nomed sel of obiects ond doto owned bv o oiven user_ [vnrv
`(0t0l0p must c0nt0in th.e ll'lfORMAI|0N_S(Ht|,lA, whkh conroiirs rñet¡oro obãui ól
`rne olher obieds tored in lhe rotolog. A srhemo is úe rough equivolenl of o dotobose.
`
`An obþrt h o uniquely nomed set of doto or S0l funoionoliry Schemo ob¡eos include
`r0Dres, vteì,vs, modules, 0nd r0ulines; i.e., slored proredures ónd functions.
`
`C¡
`
`e)È
`$ti
`ai
`
`ll the ohþa is o
`il noy conloin
`
`løble or view,
`one ot nonl
`
`c0ilrfrrils
`
`A column is o uniquely nomed sel of volues rhot defines o specifk ofiribute of o hble enlity.
`
`ContaÍn one
`ü monl
`
`I
`D0lt¡lAlll ond
`U9[R D[FIT{[D
`TYPTS
`
`ßUIES snd
`ASS[nil0ils
`
`lhese identify the set of volid ond ollowoble volues for o given column.
`
`lhese identify furúer rules thor define volid ond ollowoble vorues for o given column. For
`exomple, o tiigger is o 50[ rule.
`
`Figure 2-1: SQL99 Dataset hierarcby
`
`The tricky thing about SQL99 datatypes is that they do not map directly ro an iden_
`tical implementation in a given vendor's product. Although ìh" .,.r-,áo* provide
`"datatypes" rhat comespond to the SeL99 datatypes, these vendor_specif