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

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

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

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

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

About the Author

Alex Kriegel , PMP, MCSD/MCTS, works as a Systems Architect for the State of Oregon; prior to this he worked for Pope and Talbot, Inc., Psion Teklogix International, Inc., and for Belorussian Academy of Sciences. He received his MSc. in Physics of Metals from Belarus State Polytechnic Institute in 1988, discovered PC programming and relational databases in 1992, and has never looked back since. Alex is certified as a Project Management Professional (PMP) by the Project Management Institute, and also holds various Microsoft certifications. He is also the author of Microsoft SQL Server 2000 Weekend Crash Course (Wiley, 2001), SQL Functions (Wrox, 2005), and co-author of Introduction to Database Management (Wiley, 2007), and the first edition of this book. Boris M. Trukhnov , OCP, has been working as Senior Technical Analyst/Oracle DBA for Pope & Talbot, Inc., in Portland, Oregon, since 1998. His previous job titles include Senior Programmer Analyst, Senior Software Developer, and Senior Operations Analyst. He has been working with SQL and relational databases since 1994. Boris holds a B.S. in Computer Science from the University of Minnesota. He is a co-author of Introduction to Database Management (Wiley, 2007).

Table of Contents

About the Author