M I
`
`910?? rim]?
`0 MILLION
`QUE Bangs
`
`
`
`Snap's Exhibit No. 1018
`001
`
` Snap's Exhibit No. 1018
`001
`
`

`

`
`
`Follow a few simple capitalization conventions that will make the SQL code you
`ivrite easier to read. p. 16
`
`Understand the conceptual basis for functional dependency and how this relates to
`the way you design your databases. p. 35
`
`Create an auto~incrementing column to serve as the primary key for your tables to
`improve the performance of your database applications. p. 37
`
`Use the lowest normal form that still enables you to avoid update anomalies to keep
`performance costs to a minimum. 11. 53
`
`Use two periods between the database name and the table name as shorthand nota~
`lion if the name of the user who owns the database can be inferred. p. 59
`
`Use triggers to perform advanced tests on data in a particular field before it is insert-
`ed or modified. 11. 68
`
`Include the column list in all your INSERT statements so that your code is easier to
`understand when you return to it later. p. 82
`
`Use a subquery within an INSERT statement if you are using a database that does not
`enable you to make changes to tables after they've been created. p. 83
`
`As your SELECT statements increase in complexity, place your clauses on separate lines
`to make it easier to determine where each clause begins and ends and in turn to
`make your code easier to read. [1. 101
`
`Use SELECT ' to quickly view the contents of a database when you don’t remember
`exactly what the structure of a table is or you need to ViCW all the records before you
`write a query to extract the specific data you’re looking for. p. 104
`
`Group elements of an expression within parentheses to illustrate clearly the order in
`which the expression is evaluated and what elements are related. p. 108
`
`Use IN comparisons, rather than multiple comparisons linked using on, whenever
`possible in order to make your queries easier to maintain. p. 135
`
`maximizes an inside back cover
`
`Snap's Exhibit No. 1018
`002
`
` Snap's Exhibit No. 1018
`002
`
`

`

`CONTENTS AT A GLANCE
`Introduction
`1
`
`Designing and Constructing a Database
`The Structured Query Language
`'1'
`Database Design
`27
`5?
`Creating Databases
`Creating, Changing, and Removing Records
`
`79
`
`Retrieving Data irons a Database
`Tl'lt.I SELECI' Statement
`99
`127
`Using the WHERE Clause
`149
`Aggregating Query Results
`Combining Tables Using Joins
`Subqueries
`201
`
`'ITI
`
`hHN—d—
`
`WONG!“
`
`lll Database Management
`In Using Views
`229
`I1 The SQL Security Model
`
`255
`
`IV Real-World Issues
`
`279
`l2 Handling Specific Types of Data
`I: Database Performance and Integrity
`H Transactions and Cursors
`331
`
`309
`
`V Stored Procedures
`
`357
`‘IS Writing Stored Procedures
`16 More On Transact-SQL Stored Procedures
`11 Writing Oracle PL/SQL Stored Procedures
`
`385
`403
`
`VI Specific Databases
`l8 Oracle
`431
`Is Microsoft SQL Sewer and Sybase Adaptive Sewer
`10 Microsoft Access
`485
`21 MSQL and MYSQL
`509
`
`457
`
`VII Publishing Databases on the Web
`
`22 Web Programming Fundamentals
`23 Web Application Servers
`553
`24 A Sample Web Application
`5?9
`
`533
`
`Appendix
`A System-Defined Exceptions in PL/SQ 619
`
`Index
`
`621
`
`
`
`Rafe Colbum
`
`A Division of Macmillan USA
`201 West 103rd Street
`
`Indianapolis. Indiana 46290
`
`Snap's Exhibit No. 1018
`003
`
` Snap's Exhibit No. 1018
`003
`
`

`

`SPECIAL EDITION USING SQL
`
`Copyright © 2000 by Que
`
`All rights reserved. No part of this book shall be repro—
`duced, stored in a retrieval system, or transmitted by any
`means, electronic, mechanical, photocopying, recording, or
`otherwise, without written permission from the publisher.
`No patent liability is assumed with respect to the use of the
`information contained herein. Although every precaution
`has been taken in the preparation of this book, the pub-
`lisher and author assume no responsibility for errors or
`omissions. Neither is any liability assumed for damages
`resulting from the use of the information contained herein.
`International Standard Book Number: 0-7897-1974—6
`
`Library of Congress Catalog Card Number: 98-89290
`Printed in the United States of America
`
`First Printing: October 1999
`
`01
`
`00
`
`99
`
`4
`
`3
`
`2
`
`1
`
`TRADEMARKS
`
`All terms mentioned in this book that are known to be
`trademarks or service marks have been appropriately capi—
`talized. Que cannot attest to the accuracy of this informa-
`tion. Use of a term in this book should not be regarded as
`affecting the validity of any trademark or service mark.
`
`WARNING AND DISCLAIMER
`
`Every effort has been made to make this book as complete
`and as accurate as possible, but no warranty or fitness is
`implied. The information provided is on an “as is” basis.
`The authors and the publisher shall have neither liability
`nor responsibility to any person or entity with respect to
`any loss or damages arising from the information con-
`tained in this book.
`
`Publisher
`Dean Miller
`
`Acquisitions Editor
`Randy Haubner
`
`Development Editor
`Sean Dixon
`
`Managing Editor
`Lisa Wilson
`
`Proiect Editor
`Natalie Harris
`
`Copy Editors
`Kelly Talbot
`Barbara Hacha
`
`Indexer
`Diane Brenner
`
`Proofreader
`Benjamin Berg
`Technical Editor
`Michael Ask
`
`Team Coordinator
`Cindy Teeters
`
`Software Development Specialist
`Jason Haines
`
`Interior Design
`Ruth Harvey
`
`Cover Design
`Dan Armstrong
`Ruth Harvey
`
`Layout Technicians
`Tarn Osborn
`Staci Somers
`Mark Walchle
`
`Production Control
`Dan Harris
`Heather Mosernan
`
`Snap's Exhibit No. 1018
`004
`
` Snap's Exhibit No. 1018
`004
`
`

`

`TABLE OF CONTENTS
`
`Intrlducliun l
`
`I Designing and Constructing a
`Database
`
`I The Stmrtured Query Language
`
`1
`
`The Relational Model
`
`8
`
`Rules Concerning Data Structure
`Rules Regarding Data
`Manipulation
`10
`Rules Concerning Data Integrity
`
`10
`
`11
`
`The SQL Language
`Data Manipulation
`Data Definition
`14
`Database Administration
`
`12
`13
`
`14
`
`SQL Standards
`
`15
`
`15
`SQL Syntax
`Case Sensitivity
`White Space
`16
`Nesting
`17
`Quoting Strings
`Parentheses
`19
`
`16
`
`18
`
`Relational Operations
`Selection
`20
`
`20
`
`Projection
`Join
`21
`
`21
`
`Database Application Delivery
`Host—Based Applications
`22
`The Client]Server Era
`
`23
`
`22
`
`'Darabase Application Delivery via the
`Web 24
`Command-Line Interfaces
`
`25
`
`In the Real World
`
`25
`
`2 Database Design
`
`27
`
`Database Structure
`
`28
`
`29
`Databases Good and Bad
`Characteristics of Good Databases
`
`30
`
`Symptoms of Bad Databases
`
`30
`
`The Design Process
`
`30
`
`The Pro-design Phase of Design
`
`31
`
`Organizing Your Data
`
`32
`
`Functional Dependency and Candidate
`Keys
`34
`34
`Functional Dependency
`Candidate Keys and Primary Keys
`Cheating 37
`
`36
`
`33
`
`Entity-Relationship Modeling
`Identifying Entities
`38
`39
`Identifying Properties
`41
`Identify Relationships
`One-to-One Relationships
`Many-to-One Relationships
`ManyrtoiMany Relationships
`Diagramming Relationships
`
`41
`41
`42
`43
`
`Normalization
`
`4-4
`
`Nonloss Decomposition 45
`The First Normal Form 46
`Second Normal Form 47
`The Third Normal Form 50
`
`Boyce-Codd Normal Form 51
`Higher Normal Forms
`52
`Denormalization
`53
`
`In the Real World
`
`54
`
`Creating Databases 5?
`
`Creating a Database
`
`58
`
`Choosing Which Database to Access
`
`59
`
`Creating a Table
`
`59
`
`Snap's Exhibit No. 1018
`005
`
` Snap's Exhibit No. 1018
`005
`
`

`

`IV I SPECIAL EDITION USING SQL
`
`Relational Data Types
`String Data
`61
`Numeric Data
`
`62
`
`60
`
`Temporal Data Types
`
`63
`
`Specifying Keys
`Foreign Keys
`
`64
`65
`
`Column Constraints
`
`66
`
`Disallowing Null Values
`Other Constraints
`67
`
`66
`
`Default Values
`
`68
`
`Design of the Movie Info Database
`The Mavies Table
`69
`The Studios Table
`71
`
`69
`
`72
`The People Table
`The Cast_Movies Table
`The Locations "Table
`74
`
`73
`
`Indexes
`
`74
`
`Creating Unique Indexes
`Clustered Indexes
`75
`Reasons to Index a Column
`Met! Not to Index
`76
`
`75
`
`76
`
`In the Real World
`
`77
`
`Creating. Changing, and Removing
`Rerords
`19
`
`Preparing Your Data
`
`80
`
`The INSERT Statement
`
`80
`
`Inserting Null or Default Values
`
`82
`
`Using SELECT and INSERT
`Together
`83
`Copying Tables and Eliminating
`Duplicate Rows
`85
`
`The DELETE Statement
`
`The UPDATE Statement
`
`88
`
`89
`
`The TRUNCATE Statement
`
`‘Jl
`
`The DROP Statement
`DROP TABLE 92
`DROP INDEX 92
`
`91
`
`The ALTER Statement
`ALTER TABLE 92
`
`92
`
`In the Real World
`
`94
`
`Retrieving Data from a Database
`
`The SELECT Statemenl 99
`
`Anatomy of a SELECT Statement
`
`1110
`
`Specifying Columns to Retrieve
`Selecting All Columns Using ’
`
`101
`103
`
`Performing Calculations on Selected
`Data
`104
`
`Including Expressions in a SELECT
`Statement
`1114
`
`Using Column Names in
`Expressions
`105
`Arithmetic Operators Supported by
`SQL 107
`
`Using AS to Name Columns and
`Expressions
`108
`
`Filtering Query Results Using the
`WHERE Clause
`110
`
`Operators for Performing
`Comparisons
`1 1 1
`Case Sensitivity in String
`Comparisons
`1 13
`Performing Calculations in the
`WHERE Clause
`114
`
`114
`Dealing with Null Values
`1 15
`Testing For Null Values
`Accelunting for Null Values in Other
`Queries
`116
`
`118
`Sorting Query Results
`Using ORDER BY 118
`Sorting Records in Descending
`Order
`120
`
`Snap's Exhibit No. 1018
`006
`
` Snap's Exhibit No. 1018
`006
`
`

`

`Sorting by Expressions
`Secondary Sorts
`121
`
`120
`
`How the Equality of Strings Is
`Determined
`123
`
`How Strings Are Compared
`Sorting Strings Ignoring Case
`Character Sets
`125
`
`124
`124
`
`In the Real World
`
`125
`
`Using the WHERE Clause
`
`127
`
`The WHERE Clause, 3 Recap
`
`123
`
`Using Logical Operators in the
`WHERE Clause
`128
`
`129
`Programming Logic
`129
`The AND Operator
`130
`The OR Operator
`130
`The NOT Operator
`Using Multiple Boolean
`Operators
`132
`Operator Precendence
`
`133
`
`The W Clause
`
`133
`
`IN and Subqueries
`IN Versus OR 13 5
`NOT IN 13 5
`
`134-
`
`136
`The BETWEEN Clause
`NOT BETWEEN 138
`
`BETWEEN and Temporal
`Data
`138
`
`BETWEEN and Strings
`
`139
`
`Matching Parts of Strings Using
`LIKE 139
`
`140
`Constructing Patterns
`Compound WHERE Clauses
`Using LIKE 142
`Escaping Wildcard Characters
`LIKE and Performance
`143
`
`143
`
`Matching One or More
`Characters
`144
`
`CONTENTS I
`
`1.;
`
`Useful Functions for WHERE
`Clauses
`144
`
`145
`
`Removing Excess Spaces
`String Length
`145
`Type Conversion Functions
`Nesting Functions ,146
`Aggregate Functions
`146
`
`I45
`
`In the Real World
`
`147
`
`Aggregating Query Results
`
`149
`
`Selecting Unique Values Using DIS-
`TINCT 1 5 0
`
`Selecting DISTINCT
`Combinations of Values
`DISTmCT and Nulls
`
`15 I
`152
`
`1 S 3
`Aggregate Functions
`Aggregate Functions and the
`WHERE Clause
`154
`
`Renaming Aggregate Resale
`
`155
`
`15S
`The COUNTO Function
`156
`COUNTO and Nulls
`COUNTO and DISTINCT 156
`
`The SUMO and AVGO Functions
`The MING and MAXO
`Functions
`1 5 8
`
`157
`
`Dividing Aggregates Into
`Categories
`158
`The GROUP BY Clause
`
`159
`
`161
`Groups and Subgroups
`GROUP BY and ORDER BY 162
`
`Using GROUP BY with the
`WHERE Clause
`164
`
`Filtering Query Results Using
`HAVING 165
`
`Compound Expressions and the
`HAVING Clause
`166
`HAVING and WHERE 166
`
`In the Real World
`
`167
`
`Snap's Exhibit No. 1018
`007
`
` Snap's Exhibit No. 1018
`007
`
`

`

`V:
`
`I SPECIAL EDITION USING SQL
`
`8 Combining Tables Using Joins
`
`I'll
`
`Joins and Normalization
`
`172
`
`172
`What Is 3 Join?
`[73
`Join-compatible Columns
`The Joining Condition
`175
`
`176
`Using Joins
`Determining What Columns to
`Select
`176
`
`Shortening Table Names
`Joins and Relationships
`
`17?
`178
`
`Howjoins Are Processed
`
`179
`
`180
`Types ofJoins
`180
`NaturalJoins
`Joins Based on Other Conditions
`Self-joins
`182
`
`Joining More Than Two Tables
`Using Joining Tables
`185
`
`184
`
`Outer Joins
`
`187
`
`1 88
`UNION Joins
`190
`The ALL Option
`Combining UNION and
`ORDER BY 191
`INTERSEC’I‘
`193
`MENUS
`193
`
`SOL—92 Join Syntax
`Inner Joins
`194
`Outer Joins
`196
`
`194
`
`In the Real World
`
`198
`
`9 Subqueries
`
`101
`
`Subqueries that Return a List of Values
`206
`
`Using Subqueries with TN 206
`Using IN with Correlated
`Subqueries
`208
`Replacing a Selfjoin with a
`Subqucry 209
`Subqueries and NOT IN 210
`Using EXISTS 212
`Finding Empty Sets Using NOT
`EXISTS 2 1 3
`
`Using Comparison Operators with
`ANY and ALL 213
`
`Using ANY 213
`Using ALL 215
`
`181
`
`Subqueries that Return a Single
`Value
`216
`
`Subqueries and Aggregate
`Functions
`217
`
`219
`Writing Complex Queries
`Nesting Subqueries
`219
`Combining Subqueries andjoins
`Using a Subquery in a HAVILTG
`Clause
`22]
`
`220
`
`Using Subqueries in UPDATE and
`DELETE Statements
`222
`
`Using Subqueries with DELETE 222
`Using Suhqueries with UPDATE 222
`
`Using Subqueries with INSERT 224
`
`In the Real World
`
`225
`
`111 Database Management
`
`202
`What Is a Subquery?
`Replacing a Subquery with a Join
`
`204
`
`10 Using Views
`
`229
`
`Creating Views
`
`2 30
`
`204
`Types of Subqueries
`Noncorrelated Subqueries
`Correlated Subqueries
`205
`
`204
`
`Advantages of Using Views
`Convenience
`2 3 2
`
`232
`
`Hiding the Effects of
`Normalization
`232
`
`Snap's Exhibit No. 1018
`008
`
` Snap's Exhibit No. 1018
`008
`
`

`

`CO NTENTS I VII
`
`Restricting Data Available to
`Users
`233
`
`Creating a Layer of Abstraction
`
`234
`
`Creating Column Aliases
`
`234
`
`235
`Single—Table Views
`Views with Expressions and
`Functions
`236
`
`Using Aggregate Functions in
`Views
`238
`
`Vlews that Usejoins
`
`240
`
`Creating mews with Subqueries
`
`241
`
`Using Other Join Operations in
`Views
`242
`
`NestingViews
`
`243
`
`Updating Views
`
`244
`
`Tasks You Can Accomplish with
`Views
`247
`
`248
`The Tables in the Example
`How the System Works
`249
`The Views in the System 249
`The Article Retrieval Query 252
`
`In the Real World
`
`253
`
`I'l
`
`The 501. Security Model
`
`255
`
`An Overview of Database Security
`The Database Administrator
`Account
`256
`Database Owners
`
`257
`
`256
`
`Creating Database Users
`Modifying Users
`2 S 8
`Removing Users
`25 8
`
`257
`
`259
`
`Database Elements
`Databases
`259
`Tables
`261
`Vlews
`261
`Colunms and View Columns
`
`262
`
`Using GRANT and REVOKE 262
`The Public User
`264
`
`Allowing Users to Grant
`Privileges
`264
`Order of Grants and Revokes
`
`265
`Security Roles
`Creating and Dropping Roles
`Assigning Users to Roles
`266
`Assigning Roles to Other Roles
`
`Views and Database Security 270
`Customizing Access Through
`Vlews
`271
`
`In the Real World
`
`275
`
`265
`
`266
`
`268
`
`IV Real-World Issues
`
`12 Handling Specific Types of Data
`
`279
`
`Numeric Data Types
`Sorting Numbers
`Nulls and Numbers
`
`280
`281
`233
`
`Mathematical Expressions
`Common Mathematical
`Funciions
`285
`
`284
`
`Formatting Numbers
`
`286
`
`288
`
`288
`String Data Types
`Concatenating Strings
`String Functions
`289
`Searching Strings
`289
`Substrings
`290
`Combining Searches and
`Substrings
`291
`Soundex
`292
`
`Dealing with Dates Z94
`Formatting Dates for Output
`Formatting Dates for Input
`Date Arithmetic
`296
`
`294
`295
`
`298
`Comparing Dates
`Rounding Temporal Values
`
`299
`
`Snap's Exhibit No. 1018
`009
`
` Snap's Exhibit No. 1018
`009
`
`

`

`VIII
`
`I SPECIAL EDITION Usmo SQL
`
`300
`Converting Data Between Types
`300
`The CONVERTO Function
`Converting Data Types in Oracle
`
`303
`
`In the Real World
`
`306
`
`l3
`
`Database Performance and
`Integrity
`309
`
`Database Locks
`
`337
`
`339
`Types of Locks
`Transactions and Stored
`Procedures
`340
`
`The Transaction Log
`
`340
`
`Cursors
`
`341
`
`Improving Database Performance
`Performance Considerations in
`
`310
`
`Database Design
`
`310
`
`Performance Measurement Tools
`Oracle Autotrace
`3 12
`Transact-SQT. SHOWPLAN 3 14
`
`311
`
`316
`Indexes
`316
`Clustered Indexes
`Queries that Utilize Indexes
`
`317
`
`318
`The Query Optimizer
`How Query Optimizers Work 319
`Indexes and the Optimizer
`320
`Handling Multiple Indexes in One
`Query
`321
`323
`Data Set Operations
`The Query Optimizer and Views
`The Query Optimizer and
`Subqueries
`324
`324
`Join Operations
`join-Related Hints
`325
`
`323
`
`Data Integrity 326
`
`Integrityr Versus Performance
`
`327
`
`In the Real World
`
`3253
`
`M
`
`Transactions and Cursors BI
`
`33!
`Transactions
`How Transactions 'Work
`
`333
`
`Using Transactions in Oracle
`Autocommit
`335
`
`333
`
`Using Transactions in Transact-SQL 336
`
`Using Cursors in Transact-SQL 34]
`Declaring a Cursor
`342
`Opening a Cursor
`343
`Fetching Rows from a Cursor
`Updating or Deleting with
`Cursors
`344
`
`343
`
`Closing and Deallocming Cursors
`
`345
`
`Using Cursors in Oracle PUSQL 346
`Declaring Cursors
`347
`Using Cursors Within PLJSQL
`Programs
`347
`Cursor Attributes
`
`348
`
`Looping Over a Cursor
`
`349
`
`In the Real World
`
`351
`
`Stored Procedures
`
`15
`
`Writing Stored Procedures
`
`357
`
`359
`Writing a Stored Procedure
`Passing Parameters to a Stored
`Procedure
`360
`
`Working With Variables
`Setting Variables
`364
`
`363
`
`Defining Blocks of Code
`
`366
`
`Conditional Statements Using IF 367
`Using Queries in IF Expressions
`363
`Using Blocks of Code with IF
`Statements
`369
`
`ELSE 369
`Using IF
`Nesting IF Statements and Blocks
`
`370
`
`Snap's Exhibit No. 1018
`010
`
` Snap's Exhibit No. 1018
`010
`
`

`

`CONTENTS I
`
`IX
`
`372
`Using Loops
`372
`WHILE Loops
`374
`Infinite Loops
`Restarting a L00p Using
`CONTINUE 3 7 S
`
`Nested Loops
`
`376
`
`Looping Over a Cursor
`
`377
`
`379
`Triggers
`38E}
`Creating Triggers
`380
`Removing Triggers
`380
`Writing Triggers
`Constraining Triggers Based on
`Column
`381
`
`In the Real World
`
`382
`
`More On Transact-50L Stored
`Procedures
`385
`
`General Transact—SOL Programming
`Information
`386
`
`Global Variables
`
`386
`
`Using RETURN to Leave Stored
`Procedures
`388
`
`388
`Handling Errors
`Using RAISERROR 388
`Using RAISERROR with Microsoft
`SQL Server
`389
`
`Using RAISERROR with Sybase
`Trapping Errors
`392
`Using the RETURN Statement
`Creating User-Defined Error
`Messages
`394
`
`392
`
`393
`
`395
`Using Temporary Objects
`395
`How tempdb Works
`Creating a Temporary Table
`Classes of Temporary Objects
`Persistent Temporary Objects
`
`396
`396
`397
`
`WAITFOR 397
`
`Advanced Trigger—Writing
`Techniques
`398
`ROLLBACK TRIGGER 398
`
`Using the INSERTED and
`DELETED Tables in Triggers
`
`399
`
`In the Real World
`
`400
`
`1'!
`
`Writing Oracle Pl/SQL Stored
`Procedures
`403
`
`The Declaration Section
`Constants
`406
`
`405
`
`Declaring Cursors
`
`406
`
`The Executable Section
`
`407
`
`Getting Data Out of a PLI'SQL
`Block
`407
`
`Assigning Values to Variables
`Conditional Statements
`40?
`
`407
`
`4G9
`Loops
`Looping Over a Cursor 409
`For L00ps
`410
`FOR Loops and Cursors
`WHILE Loops
`411
`WHILE Loops and Cursors
`GOTO 412
`
`4-10
`
`412
`
`Selecting Values Into Variables
`
`413
`
`413
`Exception Handling
`Defining Your Own Exceptions
`
`415
`
`416
`Writing Stored Procedures
`416
`Creating a Procedure
`Executing a Stored Procedure
`
`417
`
`Creating and Using Custom
`Functions
`4-18
`
`Bundling Procedures and Functions in
`Packages
`419
`Creating a Package Specification
`
`420
`
`Debugging PL/SQL Queries
`SHOW ERRORS 422
`DBMS_OUTPUT 422
`
`421
`
`Snap's Exhibit No. 1018
`011
`
` Snap's Exhibit No. 1018
`011
`
`

`

`X I SPECIAL EDITION USING SQL
`
`423
`Triggers
`424
`Writing a Trigger
`INSTEAD OF Triggers
`Manipulating Triggers
`
`426
`427
`
`In the Real World 427
`
`Specific Databases
`
`Oracle
`
`411
`
`SQL*PLUS 432
`Launching SQL*PLUS 432
`Using SQL’PLUS 434
`Loading SQL‘PLUS Scripts
`The login.sql Script
`436
`436
`Editing SQL Commands
`Saving SQL*PLUS Output to a
`File
`437
`
`435
`
`Controlling Output Presentation 439
`
`Creating Reports in SQL‘PLUS 440
`Creating Report Titles
`441
`Altering Data Display 44]
`Other Data Manipulation
`Commands
`444
`
`Oracle System V1ews
`Using System Views
`
`447
`448
`
`Sequences
`
`449
`
`Synonyms
`
`45]
`
`Oracle Data Types
`
`4S 2
`
`Oracle Resources on the Web
`
`45 3
`
`In the Real World 454
`
`Microsoft SQI. Server and Sybase
`Adaptive Server
`451
`
`458
`Transact—SOL Database Tools
`458
`SQL Enterprise Manager
`SQL Server Query Analyzer
`460
`SQL Central
`461
`[SQL 462
`
`Naming Objects in Transact-SQL 463
`
`SELECT INTO 463
`
`464
`System Functions
`Identifying Users
`465
`Identifying a Database or Server 465
`Data Comparison Functions
`466
`Data Validation Functions
`467
`
`CASE Statements
`
`468
`
`Compound CASE Statements
`CASE and GROUP BY 471
`
`469
`
`Using CASE in UPDATE
`Statements
`47]
`
`472
`System Stored Procedures
`Security-Related Procedures
`Remote Server Procedures
`Data Definition Procedures
`
`472
`476
`476
`
`Transact—SQL Data Types
`
`480
`
`In the Real World
`
`482
`
`20
`
`Microsoft Access
`
`485
`
`Microsoft Database Files
`
`486
`
`ODBC 486
`
`The Access Interface
`
`487
`
`Objects in Microsoft Access
`Tables
`488
`Queries
`489
`Forms
`490
`
`488
`
`Reports
`Macros
`Modules
`
`491
`491
`492
`
`492
`Creating Tables
`Creating Tables in the Datasheet
`View 493
`
`Creating Tables in the Design
`View 494
`
`Writing Data Definition Queries
`
`497
`
`Dataty'pes
`
`49S
`
`Snap's Exhibit No. 1018
`012
`
` Snap's Exhibit No. 1018
`012
`
`

`

`4-99
`Creating and Running Queries
`Building Queries in Design View 5 00
`Writing Queries in the SQL
`Vlew 503
`
`VII
`
`22
`
`Creating an ODBC Damsource
`
`504
`
`Microsoft Access Resources
`
`505
`
`In the Real World
`
`506
`
`21
`
`MSQL and MYSQI.
`
`509
`
`Obtaining MySQL and MSQL 510
`
`Contrasting MySQL and MSQL 511
`
`Using MySQL 5 12
`The MySQL Client
`
`512
`
`MySQL Features
`
`5 14-
`
`MySQL Limitations
`Lack of Subqueries
`No Transactions
`
`517
`517
`518
`
`Stored Procedures, Triggers, and
`Cursors
`518
`
`Foreign Key Relationships
`
`518
`
`MySQL Data Types
`
`519
`
`MySQL Syntax
`Comments
`
`52]
`523
`
`MSQL 523
`Running Queries in MSQL 524
`Creating Database Objects in
`MSQL 524
`
`Dropping Objects in MSQL 526
`Odier MSQL Queries
`526
`
`String Comparisons in MSQL and
`MySQL 527
`Using RLIKE 527
`
`In the Real Werld
`
`529
`
`CONTENTS I XI
`
`Publishing Databases on the Web
`
`Web Programming Fundamentals
`
`533
`
`Basic Web Architecture
`
`534
`
`HTML 534
`
`Creating HTML Forms
`
`5 35
`
`Form Fieids Created Using the <input>
`Tag
`5 3 7
`538
`Text Fields
`Pasmord Fields
`Check boxes
`539
`Radio Buttons
`5 40
`
`5 39
`
`File Uploads
`Hidden Fields
`Submit Buttons
`Reset Buttons
`
`5 40
`541
`541
`54-2
`
`Using Images as Submit Buttons
`
`543
`
`Select Lists
`Text Areas
`
`543
`546
`
`The Common Gateway Interface
`Processing Form Data
`54-7
`Returning Data to a Web
`Browser
`54-8
`
`547
`
`XML 549
`
`550
`XML and Web Publishing
`XML and Relational Databases
`
`551
`
`In the Real World
`
`551
`
`23
`
`Web Application Servers
`
`553
`
`A Survey of Web Application Server
`Categories
`55 4
`
`General Truths About Web Application
`Servers
`5 5 5
`
`Standalone Programs
`
`5 55
`
`Perl/CGI
`
`5 5 6
`
`Storing Form Data in a Database
`Querying a Database from a CGI
`Program 563
`
`5 60
`
`Snap's Exhibit No. 1018
`013
`
` Snap's Exhibit No. 1018
`013
`
`

`

`XII
`
`I SPECIAL EDITION USING SQL
`
`HTML—Embedded Code
`
`565
`
`Allaire’s ColdFusion
`
`566
`
`Querying Databases with
`ColdFusion
`567'
`
`5 70
`Active Server Pages
`Querying a Database Using ASP
`
`572
`
`Java Servlets
`
`573
`
`574
`Templating Systems
`How Templau'ng Application Servers
`Work 5 7 5
`
`591
`Entering a New Movie
`The Validation Component
`The Form Component
`597
`
`596
`
`Updating an Existing Movie
`
`601
`
`Editing the Cast of a Movie
`
`604
`
`Modifying a Movie’s Locations
`
`614
`
`Deleting an Item 618
`
`In the Real World
`
`618
`
`In the Real World
`
`575
`
`
`Appendix
`
`24 A Sample Web Application
`
`579
`
`Movie Application Architecture
`
`5 80
`
`The Movie Listing Page
`
`581
`
`The Movie Detail Page
`
`586
`
`System-Defined Exceptions in
`Pl/SQI.
`6]!)
`
`Index 62]
`
`Snap's Exhibit No. 1018
`014
`
` Snap's Exhibit No. 1018
`014
`
`

`

`ABOUT THE AUTHOR
`
`Rafe Colbum is a consultant with Interpath Communications. His responsibilities include
`application and database deSign and development for large Internet-related projects. Rafe is
`also the author of Téat‘b Ymfl‘sengGI Programming in a ”412.12, for Samsnet, and is the co—
`author of 327mb l’uzmelmempe Web Pztblirbmg in a Wéek, also for Samsnet. Rafe makes his
`home in Apex, North Carolina. He can be reached via email at PafeQrc3.or‘g, and his home
`page is http: Urea . org. More information about this book can be found at
`http:Hr‘c:3.or-gisqlbook.
`
`Snap's Exhibit No. 1018
`015
`
` Snap's Exhibit No. 1018
`015
`
`

`

`DEDICATION
`
`The book is dedicated to myfimigy, the Gotham: and Tbsrkeirons, who have lent me a 12' s-
`time quppaa‘r.
`
`ACKNOWLEDGMENTS
`
`Writing a book is a momentous task, and it’s impossible to thank all the people who helped
`to produce the book in this small space, not to mention all the people who helped out indi—
`rectly with their support and patience. First of all, I’d like to thank the people at Que who
`were with me all the way, Randy Haubner and Sean Dixon. I’d also like to thank my techni—
`cal editor, Michael Ask.
`
`Most importantly, I’d like to thank my wife for months of perseverance as I sequestered
`myself in the study to bring this book into existence. Wthout her patience and love, I never
`could have made it.
`
`TELL Us WHAT You THINK!
`I
`P
`As the reader of this book, or: are our most im ortant critic and commentator. We value
`your opinion and want to know what we’re doing right, what we could do better, what areas
`you’d like to see us publish in, and any other words of wisdom you’re willing to pass our
`way.
`
`As a publisher for Que, I welcome your comments. You can fax, email, or write me directly
`to let me know what you did or didn’t like about this book—as well as what we can do to
`make our books stronger.
`
`Please note flier I mamor help you with tetbnn'alproblems related to the topic afrbi: book, and that
`due to the big]: vol-Irma (from? I receive, 1 might not be able to reply to every message.
`
`When you write, please be sure to include this book’s title and author as well as your name
`and phone or fax number. I will carefully review your comments and share them with the
`author and editors who worked on the book.
`
`Fax:
`
`Email:
`Mail:
`
`317.581.4666
`
`offiee_qua@mcp. com
`Publisher
`Que
`201 West lO3rd Street
`
`Indianapolis, IN 46290 USA
`
`Snap's Exhibit No. 1018
`016
`
` Snap's Exhibit No. 1018
`016
`
`

`

`INTRODUCTION -
`
`Relational databases are some of the most widely used and important computer applications
`available. They provide a powerful and flexible means for storing data of all kinds. Indeed,
`they work behind the scenes of many of the applications you probably use, directly or indie
`rectly, every day. The relational model, which is described in detail in the first chapter of
`Special Edition Using SQL, is extremely scalable. In fact, relational databases are used for
`applications as small as home recipe files and as large as the data storage systems used by
`banks to keep track of all the transactions that they process.
`
`SQL, the Structured Query Language, is the query language that enables database program-
`mers to retrieve data from, to modify data in, and to manage most relational databases.
`Although there are some differences in the way SQL is supported among the various data—
`base vendors, the language is standard enough that after you’ve learned it for one database
`product, you’ll be able to use it with any other database product that supports SQL. SQL
`consists of only a few types of statements, and it is easy to learn well enough to perform
`basic queries. As your needs become more complex and your confidence in your SQL
`querying abilities grows, so too will the complexity of the queries that you write.
`
`One of the nicest things about SQL is that it encourages experimentation. The queries for
`retrieving data are completely separate from those used to modify data, so you can enter any
`type of data retrieval query you like without fear of damaging the data within your database.
`SQL also encourages experimentation because nearly all queries consist of a single state-
`ment, so they’re easy to write. Most databases provide interactive programs that enable you
`to enter queries and immediately view the results, so you can rapidly write and modify your
`queries until they retrieve exactly the data you’re looking for. When your queries are per-
`fected, you can transfer them into your applications.
`
`How THIS BOOK Is ORGANIZED
`
`The first chapter of Special Edition Using SQL, “The Structured Query Language,” provides
`a foundatiOH on which to build your knowledge of SQL. It describes how relational databases
`
`Snap's Exhibit No. 1018
`017
`
` Snap's Exhibit No. 1018
`017
`
`

`

`2 I
`
`[NTRODUCTION
`
`work and some of the history behind relational databases. The basic syntax of SQL is also
`described. Chapter 2, “Database Design,” is devoted to explaining die design of relational
`databases. It discusses topics such as entity-relationship modeling and database normaliza-
`tion to enable you to build well-designed databases that provide the highest possible level of
`database integrity without sacrificing performance.
`
`Chapters 3, “Creating Databases,” and 4, “Inserting, Updating, and Deleting Records,”
`begin to get into the meat of SQL. They are devoted to discussing the creation of tables,
`the basic unit of storage in relational databases, and the SQL statements used to populate
`databases after they’re created. Chapter 3 also contains a general, highelevel discussion of
`the types of data that can be stored in a relational database. I also introduce the sample
`database, which is used to store information about movies, in Chapter 3. That database will
`provide a common thread through nearly all the examples in this book.
`
`Chapters 5 through 9 are devoted to data retrieval. Believe it or not, all five chapters are
`devoted to the SELECT statement, which is the SQL statement used to retrieve data from
`
`databases. The discussion begins with a basic explanation of how to retrieve the data you
`want from a single table and moves on to describe more complex techniques, such as using
`aggregate functions on groups of rows, joining multiple tables in a single query, and using
`subqueries within other queries.
`
`Chapter 10, “Using Views,” describes the use of views to provide customized means of
`accessing the data in a database for particular users. Chapter I], “The SQL Security
`Model,” is devoted to an explanation of the SQL security model and cxplains how you can
`restrict access to certain database resources to specific users.
`
`Chapters 12, “Handling Specific Types of Data,” 13, “Database Performance and Integrity."
`and 14, “Transactions and Cursors,” are devoted to real-world issues that database pro-
`grammers run into every day. Chapter 12 expands on the discussion of data types in
`Chapter 3 by explaining in detail how all the data types in a relational database are used and
`manipulated. Chapter [3 takes on the issue of database performance and explains how
`queries are optimized by database servers before they are executed. It also discusses database
`integrity—how to ensure that your tables contain the data that you think they contain.
`Chapter 14 is devoted to two advanced tools used by database programmers: cursors and
`transactions. Cursors are used to deal with query results one at a time (as opposed to in a
`batch), and transactions are used to maintain dambase integrity and to ensure that a data-
`base can be fully recovered in case of a disaster.
`
`Chapters 15, “Writing Stored Procedures,” 16, “More on Transact-SOL Stored
`Procedures,” and 17, “Writing Oracle PL/SQL Stored Procedures,” are devoted to dis-
`cussing how to write stored procedures. Stored procedures are programs that are written
`and stored within the database so that they can called by most external applications.
`Chapter 15 discusses stored procedures in general, with an emphasis on those written in
`Transact-SQL. Chapter 16 covers stored procedures written in Trmisact-SQL in detail.
`Chapter 17 explains stored procedures written in Oracle PL/SQL, which are completely
`different from stored procedures for any other database.
`
`Snap's Exhibit No. 1018
`018
`
` Snap's Exhibit No. 1018
`018
`
`

`

`SPECIAL FEATURES oF THIS BOOK l 3
`
`Chapters 18 through 21 are devoted to discussing particular databases. Chapter 18,
`“Oracle,” discusses Oracle; Chapter 19, “Microsoft SQL Server and Sybase Adaptive
`Server,” discusses Transact-SQL, encompassing both Sybase databases and Microsoft SQL
`Server. Chapter 20, “Microsoft Access,” is all about Microsoft Access, and Chapter 21,
`“MSQL and MySQL,” discusses Mini-SQL and MySQL, two simple databases that are
`popular on Linux.
`
`Chapters 22 through 24 provide an introduction to Internet programming and, more
`importantly, how to build database-backed Web applications. They explain the basics of
`HTML, how to build forms for the Web, and various application development platforms
`for building Web applications.
`
`Although the foremost objective of this book is to teach you the ins and outs of standard
`SQL, I’ve taken things a bit further than that. The secondary goal of this book is to provide
`you with the tools you need to build database-backed applications. Although this book
`won’t teach you how to use your favorite application development environment to build a
`graphical front end for your applications, it does explain how databases fit into larger appli—
`cation designs and how to build applications so that they use relational databases appropri—
`ately. By marrying information on how to use SQL with information on application design
`and development, I hope to provide you with the tools that you need to not only query
`databases, butto fit databases into a larger application design.
`
`In the last part of this book, I provide a basic discussion of how to build database—backed
`Web applications. As the Web grows in pepularity, both as a means of delivering informa-
`tion to the public and as a full-fledged application development platform, the use of data-
`bases by Web programmers is growing by leaps and bounds. Nearly every major Web site
`that currently exists uses relational databases in some capacity. Some use them for data stor—
`age for their applications, just as a traditional application would; others use them for storing
`all the content that they publish on die Web.
`
`As the Web world and database world converge, I think it’s important to provide some mid-
`dle ground between the two. This book should help Web programmers get up to speed in
`ter