Introduction 1
About This Book 1
Foolish Assumptions 2
Conventions Used in This Book 3
What You Don't Have to Read 3
How This Book is Organized 3
Book 1: SQL Concepts 3
Book 2: Relational Database Development 4
Book 3: SQL Queries 4
Book 4: Data Security 4
Book 5: SQL and Programming 5
Book 6: SQL and XML 5
Book 7: Database Tuning Overview 5
Book 8: Appendices 5
Icons Used in This Book 6
Where to Go from Here 6
Book 1: SQL Concepts 9
Chapter 1: Understanding Relational Databases 11
Understanding Why Today's Databases are Better than Early Databases 12
Databases, Queries, and Database Applications 16
Examining Competing Database Models 18
Why the Relational Model Won 29
Chapter 2: Modeling a System 31
Capturing the Users' Data Model 31
Translating the Users' Data Model to a Formal Entity-Relationship Model 34
Entity-Relationship modeling techniques 35
Chapter 3: Getting to Know SQL 55
Where SQL Came From 55
Knowing What SQL Does 56
The ISO/IEC SQL Standard 57
Knowing What SQL Does Not Do 57
Choosing and Using an Available DBMS Implementation 58
Chapter 4: SQL and the Relational Model 67
Sets, Relations, Multisets, and Tables 68
Functional Dependencies 69
Keys 70
Views 71
Users 72
Privileges 72
Schemas 73
Catalogs 74
Connections, Sessions, and Transactions 74
Routines 75
Paths 75
Chapter 5: Knowing the Major Components of SQL 77
Creating a Database with the Data Definition Language 77
Operating on Data with the Data Manipulation Language (DML) 88
Maintaining Security in the Data Control Language (DCL) 97
Chapter 6: Drilling Down to the SQL Nitty-Gritty 99
Executing SQL Statements 99
Module language 104
Using Reserved Words Correctly 105
SQL's Data Types 105
Handling Null Values 123
Applying Constraints 124
Book 2: Relational Database Development 131
Chapter 1: System Development Overview 133
The Components of a Database System 133
The System Development Life Cycle 136
Chapter 2: Building a Database Model 149
Finding and Listening to Interested Parties 150
Building Consensus 152
Building a Relational Model 154
Being Aware of the Danger of Anomalies 157
The Database Integrity versus Performance Tradeoff 164
Chapter 3: Balancing Performance and Correctness 167
Designing a Sample Database 168
Maintaining Integrity 179
Avoiding Data Corruption 183
Speeding Data Retrievals 185
Working with Indexes 187
Reading SQL Server Execution Plans 194
Chapter 4: Creating a Database with SQL 199
First Things First: Planning Your Database 199
Building Tables 200
Setting Constraints 204
Keys and Indexes 205
Ensuring Data Validity with Domains 205
Establishing Relationships between Tables 206
Altering Table Structure 210
Deleting Tables 210
Book 3: SQL Queries 211
Chapter 1: Values, Variables, Functions, and Expressions 213
Entering Data Values 213
Working with Functions 217
Using Expressions 229
Chapter 2: SELECT Statements and Modifying Clauses 239
Finding Needles in Haystacks with the SELECT Statement 239
Modifying Clauses 240
Tuning Queries 265
Chapter 3: Querying Multiple Tables with Subqueries 281
What is a Subquery? 281
What Subqueries Do 282
Using Subqueries in INSERT, DELETE, and UPDATE Statements 295
Tuning Considerations for Statements Containing Nested Queries 298
Tuning Correlated Subqueries 304
Chapter 4: Querying Multiple Tables with Relational Operators 309
UNION 310
INTERSECT 313
EXCEPT 315
JOINS 315
ON versus WHERE 327
Join Conditions and Clustering Indexes 327
Chapter 5: Cursors 329
Declaring a Cursor 330
Opening a Cursor 336
Operating on a Single Row 337
Closing a Cursor 340
Book 4: Data Security 341
Chapter 1: Protecting Against Hardware Failure and External Threats 343
What Could Possibly Go Wrong? 344
Taking Advantage of RAID 347
Backing Up Your System 351
Coping with Internet Threats 354
Installing Layers of Protection 371
Chapter 2: Protecting Against User Errors and Conflicts 373
Reducing Data-Entry Errors 374
Coping with Errors in Database Design 375
Handling Programming Errors 376
Solving Concurrent-Operation Conflicts 376
Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability 378
Operating with Transactions 379
Getting Familiar with Locking 391
Tuning Locks 393
Enforcing Serializability with Timestamps 397
Tuning the Recovery System 400
Chapter 3: Assigning Access Privileges 401
Working with the SQL Data Control Language 401
Identifying Authorized Users 402
Classifying Users 404
Granting Privileges 404
Revoking Privileges 410
Granting Roles 411
Revoking Roles 412
Chapter 4: Error Handling 413
Identifying Error Conditions 414
Getting to Know SQLSTATE 414
Handling Conditions 416
Dealing with Execution Exceptions: The WHENEVER Clause 419
Getting More Information: The Diagnostics Area 420
Examining an Example Constraint Violation 424
Adding Constraints to an Existing Table 426
Interpreting SQLSTATE Information 426
Handling Exceptions 427
Book 5: SQL and Programming 429
Chapter 1: Database Development Environments 431
Microsoft Access 431
Microsoft SQL Server 433
IBM Db2 434
Oracle 18c 434
SQL Anywhere 435
PostgreSQL 435
MySQL 435
Chapter 2: Interfacing SQL to a Procedural Language 437
Building an Application with SQL and a Procedural Language 437
Chapter 3: Using SQL in an Application Program 443
Comparing SQL with Procedural Languages 444
Difficulties in Combining SQL with a Procedural Language 446
Embedding SQL in an Application 448
Using SQL Modules with an Application 453
Chapter 4: Designing a Sample Application 457
Understanding the Client's Problem 458
Approaching the Problem 458
Determining the Deliverables 460
Building an Entity-Relationship Model 463
Transforming the Model 467
Creating Tables 471
Changing Table Structure 475
Removing Tables 475
Designing the User Interface 475
Chapter 5: Building an Application 477
Designing from the Top Down 477
Coding from the Bottom Up 481
Testing, Testing, Testing 490
Chapter 6: Understanding SQL's Procedural Capabilities 493
Embedding SQL Statements in Your Code 494
Introducing Compound Statements 494
Following the Flow of Control Statements 497
Using Stored Procedures 502
Working with Triggers 503
Using Stored Functions 506
Passing Out Privileges 507
Using Stored Modules 508
Chapter 7: Connecting SQL to a Remote Database 509
Native Drivers 510
ODBC and Its Major Components 511
What Happens When the Application Makes a Request 515
Book 6: SQL, XML, AND JSON 523
Chapter 1: Using XML with SQL 525
Introducing XML 526
Knowing the Parts of an XML Document 527
Using XML Schema 531
Relating SQL to XML 532
Using the XML Data Type 533
Mapping SQL to XML 535
Operating on XML Data with SQL Functions 544
Working with XML Predicates 549
Chapter 2: Storing XML Data in SQL Tables 553
Inserting XML Data into an SQL Pseudotable 553
Creating a Table to Hold XML Data 555
Updating XML Documents 556
Discovering Oracle's Tools for Updating XML Data in a Table 557
Introducing Microsoft's Tools for Updating XML Data in a Table 562
Chapter 3: Retrieving Data from XML Documents 577
XQuery 578
FLWOR Expressions 584
XQuery versus SQL 590
Chapter 4: Using JSON with SQL 595
Using JSON with SQL 595
The SQL/JSON Data Model 596
SQL/JSON Functions 598
SQL/JSON Path Language 607
Book 7: Database Tuning Overview 609
Chapter 1: Tuning the Database 611
Analyzing the Workload 612
Considering the Physical Design 613
Choosing the Right Indexes 614
Tuning Indexes 619
Tuning Queries 620
Tuning Transactions 621
Separating User Interactions from Transactions 622
Minimizing Traffic between Application and Server 622
Precompiling Frequently Used Queries 622
Chapter 2: Tuning the Environment 623
Surviving Failures with Minimum Data Loss 624
Tuning the Recovery System 625
Tuning the Operating System 634
Maximizing the Hardware You Have 639
Adding Hardware 641
Working in Multiprocessor Environments 643
Chapter 3: Finding and Eliminating Bottlenecks 645
Pinpointing the Problem 645
Determining the Possible Causes of Trouble 647
Implementing General Principles: A First Step Toward Improving Performance 651
Tracking Down Bottlenecks 653
Analyzing Query Efficiency 657
Managing Resources Wisely 671
Book 8: Appendices 675
Appendix A: SQL: 2016 Reserved Words 677
Appendix B: Glossary 683
Index 691
