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).

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...