Acknowledgements xxiii
Introduction xxv
Part I SQL Basic Concepts and Principles
Chapter 1: SQL and Relational Database Management Systems 3
Desirable Database Characteristics 3
Sufficient capacity 4
Adequate security and auditing 4
Multiuser environment 4
Effectiveness and searchability 4
Scalability 5
User friendliness 5
Selecting Your Database Software 6
Market share 6
Total cost of ownership 6
Support and persistence 7
Major DBMS Implementations 7
Real-Life Database Examples 9
Order management system database 9
Health care provider database 10
Video sharing and editing database 10
Scientific database 11
Nonprofit organization database 11
Database Legacy 11
Flat file databases 11
Hierarchical databases 12
Network databases 14
Relational Databases 15
Tables 16
Relationships 17
Primary key 17
Foreign key 18
Invasion of RDBMS 18
Other DBMS Models 19
Brief History of SQL and SQL Standards 20
Humble beginnings: RDBMS and SQL evolution 20
A brief history of SQL standards 23
Summary 26
Chapter 2: Fundamental SQL Concepts and Principles 27
Promises and Deliverables 27
SQL: The First Look 32
Database example 32
Getting the data in and out 33
Slice and dice: Same data, different angle 35
Aggregation 37
Data security 38
Accessing data from a client application 39
New developments 40
Any Platform, Any Time 40
Summary 43
Chapter 3: SQL Data Types 45
No Strings Attached 46
Character strings 46
Binary strings 54
In Numbers Strength 56
Exact numbers 56
Approximate numbers 60
Once Upon a Time: Date and Time Data Types 61
Introduction to complex data types 62
Date and time implementations 63
XML Data Type 69
XML data type implementations 69
Constructed and User-Defined Data Types 70
SQL:2003 71
Oracle 11g 73
DB2 9.5 75
Microsoft SQL Server 2008 76
Other Data Types 77
BOOLEAN 77
ROWID 77
UROWID 78
BFILE 78
DATALINK 78
BIT 78
TIMESTAMP 78
SQL_VARIANT 78
Null 79
Summary 79
Part II Creating and Modifying Database Objects
Chapter 4: Creating RDBMS Objects 83
Tables 83
CREATE TABLE Statement 84
Indexes 113
CREATE INDEX statement 116
Views 120
CREATE VIEW statement 121
Creating complex views 127
Aliases and Synonyms 130
SQL:2003 131
Oracle 11 g CREATE SYNONYM statement 131
DB2 9.5 CREATE ALIAS statement 133
Microsoft SQL Server 2008 CREATE SYNONYM statement 133
Schemas 134
CREATE SCHEMA statement 134
Sequences 138
External sequence generators in SQL:2003 139
Sequences in Oracle 11 g 139
DB2 9.5 142
Other SQL:2003 and Implementation-Specific Objects 143
Domains (SQL:2003) 144
Character sets (SQL:2003) 144
Collations (SQL:2003) 144
Tablespaces and filegroups 144
Materialized views (Oracle 11 g) 147
Database links (Oracle 11 g) 149
Directories (Oracle 11 g) 150
CREATE Statement Cross-Reference 151
Summary 154
Chapter 5: Altering and Destroying RDBMS Objects 155
Tables 155
ALTER TABLE statement 156
DROP TABLE statement 166
Indexes 168
ALTER INDEX statement 168
DROP INDEX statement 170
Views 171
ALTER VIEW statement 171
DROP VIEW statement 173
Aliases and Synonyms 173
Oracle 11 g 174
DB2 9.5 174
Microsoft SQL Server 2008 174
Schemas 174
SQL:2003 174
DB2 9.5 175
Microsoft SQL Server 2008 175
Sequences 176
ALTER SEQUENCE statement 176
DROP SEQUENCE statement 177
Other Implementation-Specific Objects 178
Domains (SQL:2003) 178
Character sets (SQL:2003) 178
Collations (SQL:2003) 178
Tablespaces 178
Materialized views (Oracle 11 g) 180
Database Links (Oracle 11 g) 180
Directories (Oracle 11 g) 181
ALTER and DROP Statements Cross-Reference 181
Summary 186
Part III Data Manipulation and Transaction Control
Chapter 6: Data Manipulation Language (DML) 191
INSERT: Populating Tables with Data 191
Common INSERT statement clauses 192
INSERT statement vendor-related specifics 200
UPDATE: Modifying Table Data 208
Common UPDATE statement clauses 209
Vendor-specific UPDATE statement details 213
DELETE: Removing Data from Tables 216
Common DELETE statement clauses 216
Vendor-specific DELETE statement clauses 218
MERGE: Combining INSERT, UPDATE, and DELETE in One Statement 219
Common MERGE statement clauses 219
Vendor-specific MERGE statement clauses 220
TRUNCATE Statement 223
Differences between Oracle and Microsoft SQL Server TRUNCATE statements 224
Summary 225
Chapter 7: Sessions, Transactions, and Locks 227
Sessions 227
Transactions 237
What is a transaction? 237
Transactions COMMIT and ROLLBACK 241
Transaction isolation levels 248
Understanding Locks 251
Locking modes 252
Dealing with deadlocks 256
Summary 257
Part IV Retrieving and Transferring Data
Chapter 8: Understanding the SELECT Statement 261
Single Table SELECT Statement Syntax 261
SELECT Clause: What Do You Select? 262
Single-column select 262
Multicolumn SELECT 263
Using literals, functions, and calculated columns 267
Using subqueries in a SELECT clause 271
FROM Clause: Select from What? 272
Selecting from tables and views 272
Using aliases in a FROM clause 273
Using subqueries in a FROM clause (inline views) 273
WHERE Clause: Setting Horizontal Limits 274
Using comparison operators 275
Compound operators: Using AND and OR 276
Using the BETWEEN operator 277
Using the IN operator: Set membership test 278
The NOT operator 279
Using the IS NULL operator: Special test for NULLS 280
Using subqueries in a WHERE clause 281
GROUP BY and HAVING Clauses: Summarizing Results 285
ORDER BY Clause: Sorting Query Output 288
Combining the Results of Multiple Queries 291
Union 292
Intersect 295
Except (minus) 296
SQL Analytic Functions and Top N Queries 299
Analytic functions and the SQL:2003 standard 299
Designing top N queries 301
Limit N queries 303
Summary 304
Chapter 9: Multitable Queries 307
Inner Joins 307
Two syntaxes for inner joins 308
Equijoin 309
Nonequijoin 312
Self-join 314
Cross join (Cartesian product) 317
Joining more than two tables 318
Outer Joins: Joining Tables on Columns Containing NULL Values 321
Two syntaxes for outer joins 321
Left outer join 323
Right outer join 326
Full outer join 327
Union join 328
Joins Involving Inline Views 329
Multitable Joins with Correlated Queries 331
Improving Efficiency of Multitable Queries 333
Summary 334
Chapter 10: SQL Functions 335
Numeric functions 338
String functions 345
Date and time functions 357
Aggregate functions 368
Conversion functions 376
System Functions 393
Miscellaneous functions 397
User-defined functions 404
Summary 405
Chapter 11: SQL Operators 407
Arithmetic and String Concatenation Operators 407
Logical Operators 411
ALL 412
ANY | SOME 412
BETWEEN AND 413
IN 414
EXISTS 416
LIKE 417
AND 421
NOT 421
OR 422
Operator Precedence 422
Assignment Operator 425
Comparison Operators 425
Bitwise Operators 430
Summary 432
Part V Implementing Security Using the System Catalog
Chapter 12: SQL and RDBMS Security 435
Basic Security Mechanisms 435
Identification and authentication 436
Authorization and access control 436
Encryption 436
Integrity and consistency 436
Auditing 436
Defining a Database User 437
Managing Security with Privileges 443
GRANT statement 444
REVOKE privileges 460
Managing Security with Roles 465
Using Views for Security 472
Using Constraints for Security 475
Using Stored Procedures and Triggers for Security 477
Data encryption 480
Database Auditing 485
Security Standards 489
International security standards 490
Regulatory compliance 491
Summary 493
Chapter 13: The System Catalog and INFORMATION_SCHEMA 495
SQL System Catalogs 495
Oracle 11 g Data Dictionary 500
Oracle data dictionary structure 501
Oracle data dictionary and SQL:2007 standards 504
One level deeper: Data about metadata 505
IBM DB2 9.5 System Catalogs 507
The INFORMATION_SCHEMA objects in DB2 507
Obtaining information about INFORMATION_SCHEMA objects 510
Microsoft SQL Server 2008 System Catalog 511
MS SQL Server 2008 INFORMATION_SCHEMA Views 511
Microsoft SQL Server system stored procedures 515
Microsoft SQL Server 2008 system functions 520
Summary 523
Part VI Beyond SQL: Procedural Programming and Database Access Mechanisms
Chapter 14: Stored Procedures, Triggers, and User-Defined Functions 527
Procedural Extension Uses and Benefits 529
Performance and network traffic 529
Database security 529
Code reusability 530
Key Elements of a SQL Procedural Language 530
Variables and assignment 530
Modularity, subprograms, and block structure 532
Passing parameters 533
Oracle 11 g 536
DB2 9.5 536
Microsoft SQL Server 2008 537
Conditional execution 537
Repeated execution 539
Cursors 540
Error handling 544
Stored Procedures 547
CREATE PROCEDURE syntax 547
Creating a simple stored procedure 548
Removing a stored procedure 554
User-Defined Functions 554
CREATE FUNCTION syntax 554
Creating a simple function 555
Removing a user-defined function 559
Triggers 559
CREATE TRIGGER syntax 559
Removing a trigger 563
.NET Stored Procedures and Functions 563
Summary 565
Chapter 15: SQL and XML 567
Introduction 567
The Structure of XML 567
XML as a data source 573
Encoding XML 574
Presenting XML documents 575
XML and RDBMS 577
Oracle 11 g XML DB 579
IBM DB 9.5 pureXML 586
Microsoft SQL Server 2008 590
Summary 596
Chapter 16: SQL and Procedural Programming 599
SQL Statement Processing Steps 600
Embedded and Dynamic SQL 601
Embedded SQL 601
Dynamic SQL techniques 606
The future of Embedded and Dynamic SQL 614
SQL/CLI Standard 614
Open Database Connectivity and Object Linking and Embedding, Database 619
ODBC 619
OLEDB 621
SQL and Java 621
Java Database Connectivity (JDBC) 622
SQLJ 627
Oracle API options 629
Oracle Call Interface 629
Oracle Objects for OLE 631
Oracle Data Provider for .NET 632
IBM DB2 Call-Level Interface 632
Microsoft Data Access Interfaces 633
ActiveX Data Objects 634
Ado.net 634
Summary 640
Chapter 17: The Future of SQL 643
OLAP and Business Intelligence 644
Oracle 11 g 647
IBM DB2 9.5 647
Microsoft SQL Server 2008 648
LINQ to SQL 649
Objects 650
OOP Paradigm 650
Object Language Bindings (SQL/OLB) in SQL:2003 Standard 652
Oracle 11 g support 652
IBM DB2 9.5 support 653
Microsoft SQL Server 2008 654
Abstract data types 656
Object-oriented databases 656
Summary 658
Appendix A: Accompanying Website 661
Appendix B: The ACME Sample Database 663
General Information and Business Rules 663
Naming Conventions 664
Relationships between Tables 665
Column Constraints and Defaults 665
Indexes 667
Views 667
SQL Scripts to Create ACME Database Objects 667
Appendix C: Basics of Relational Database Design 679
Identifying Entities and Attributes 680
Normalization 681
First normal form 682
Second normal form 683
Third normal form 683
Specifying Constraints 686
OLTP versus OLAP Designs 687
Data warehouses and data marts 687
Star and snowflake schemas 688
Pitfalls of Relational Database Design 688
Appendix D: Installing RDBMS Software 691
Installing Oracle 11g 691
Installing Oracle 11g release 1 (11.1.0.6.0) Enterprise Edition on Linux 692
Installing Oracle 11g release 1 (11.1.0.6.0) Personal Edition on Windows 708
Installing IBM DB2 9.5 710
Installing DB2 9.5 software on Windows 710
Installing Microsoft SQL Server 2008 720
Prerequisites for the Microsoft SQL Server 2008 (CTP, November 2007 release) 720
Installing on Windows Vista Enterprise Edition 721
Appendix E: Accessing RDBMS 731
Using ORACLE 11g Utilities to Access RDBMS 731
SQL∗Plus 731
SQL Developer 736
Oracle Enterprise Manager (SQL Worksheet) 740
Using IBM DB2 9.5 Utilities to Access the RDBMS 741
Command-Line Processor (CLP) 741
Command Editor 744
Using Microsoft SQL Server 2008 Utilities to Access the RDBMS 745
Using SQLCMD 745
Using SQL Server Management Studio 747
Appendix F: Installing the ACME Database 749
Installing the ACME Database on Oracle 11g Using SQL∗Plus 749
Linux / Unix 749
Microsoft Windows 750
Installing the ACME Database on DB2 9.5 (Windows) Using CLP 751
Installing the ACME Database on Microsoft SQL Server 2008
Using the SQLCMD Utility 753
Appendix G: SQL Functions 755
Appendix H: SQL Syntax Reference 795
DDL Statements 796
Tables 796
Indexes 796
Views 796
Schemas 797
Stored procedures 797
User-defined functions 797
Triggers 798
dcl Statements 798
DML Statements 798
DQL Statements 799
Single-table SELECT 799
Multitable SELECT 799
Transactional Control Statements 800
Predicates 800
Appendix I: SQL-Reserved Keywords 803
Future Keywords 813
ODBC Reserved Keywords 815
Appendix J: The Other RDBMSs 819
Appendix K: A Brief Introduction to Number Systems, Boolean Algebra, and Set Theory 825
The Number Systems 825
The RDBMS connection 826
Converting numbers 826
Logic Elements of Boolean Algebra 828
NOT (complement or inverter) 828
AND (Boolean product) 829
OR (Boolean sum) 829
XOR (exclusive OR) 829
NAND (inversed AND) 830
NOR (inversed OR) 830
Rules of precedence 831
Set Theory 832
The listing of sets 832
Subsets 833
Set equality 834
Operations on sets 834
Identities of set algebra 838
Index 839
