Introduction 1
About This Book 1
Foolish Assumptions 2
Icons Used in This Book 2
Beyond the Book 3
Where to Go from Here 3
Part 1: Getting Started with SQL 5
Chapter 1: Relational Database Fundamentals 7
Keeping Track of Things 8
What Is a Database? 9
Database Size and Complexity 10
What Is a Database Management System? 10
Flat Files 12
Database Models 13
Database Design Considerations 20
Chapter 2: SQL Fundamentals 23
What SQL Is and Isn’t 23
A (Very) Little History 25
SQL Statements 26
Reserved Words 28
Data Types 28
Null Values 49
Constraints 50
Using SQL in a Client/Server System 50
Using SQL on the Internet or an Intranet 52
Chapter 3: The Components of SQL 55
Data Definition Language 56
Data Manipulation Language 68
Data Control Language 76
Part 2: Using SQL to Build Databases 83
Chapter 4: Building and Maintaining a Simple Database Structure 85
Using a RAD Tool to Build a Simple Database 86
Building POWER with SQL’s DDL 98
Portability Considerations 107
Chapter 5: Building a Multi-table Relational Database 109
Designing a Database 110
Working with Indexes 119
Maintaining Data Integrity 122
Normalizing the Database 134
Part 3: Storing and Retrieving Data 141
Chapter 6: Manipulating Database Data 143
Retrieving Data 144
Creating Views 145
Updating Views 149
Adding New Data 150
Chapter 7: Handling Temporal Data 163
Understanding Times and Periods 164
Working with Application-Time Period Tables 165
Working with System-Versioned Tables 171
Tracking Even More Time Data with Bitemporal Tables 175
Formatting and Parsing Dates and Times 176
Chapter 8: Specifying Values 179
Values 179
Value Expressions 186
Functions 189
Chapter 9: Using Advanced SQL Value Expressions 209
CASE Conditional Expressions 210
CAST Data-Type Conversions 217
Row Value Expressions 221
Chapter 10: Zeroing In on the Data You Want 223
Modifying Clauses 224
FROM Clauses 225
WHERE Clauses 226
Logical Connectives 243
GROUP BY Clauses 245
HAVING Clauses 247
ORDER BY Clauses 248
Limited FETCH 250
Peering through a Window to Create a Result Set 251
Chapter 11: Using Relational Operators 259
UNION 259
INTERSECT 262
EXCEPT 264
Join Operators 265
ON versus WHERE 282
Chapter 12: Delving Deep with Nested Queries 283
What Subqueries Do 285
Chapter 13: Recursive Queries 303
What Is Recursion? 303
What Is a Recursive Query? 306
Where Might You Use a Recursive Query? 306
Where Else Might You Use a Recursive Query? 311
Part 4: Controlling Operations 313
Chapter 14: Providing Database Security 315
The SQL Data Control Language 316
User Access Levels 316
Granting Privileges to Users 318
Granting Privileges across Levels 325
Granting the Power to Grant Privileges 327
Taking Privileges Away 328
Using GRANT and REVOKE Together to Save Time and Effort 329
Chapter 15: Protecting Data 331
Threats to Data Integrity 332
Reducing Vulnerability to Data Corruption 336
Constraints Within Transactions 345
Avoiding SQL Injection Attacks 350
Chapter 16: Using SQL within Applications 351
SQL in an Application 352
Hooking SQL into Procedural Languages 354
Part 5: Taking SQL to the Real World 365
Chapter 17: Accessing Data with ODBC and JDBC 367
ODBC 368
ODBC in a Client/Server Environment 370
ODBC and the Internet 370
ODBC and an Intranet 373
JDBC 373
Chapter 18: Operating on XML Data with SQL 377
How XML Relates to SQL 377
The XML Data Type 378
Mapping SQL to XML and XML to SQL 380
SQL Functions That Operate on XML Data 385
Predicates 390
Transforming XML Data into SQL Tables 392
Mapping Non-Predefined Data Types to XML 393
The Marriage of SQL and XML 398
Chapter 19: SQL and JSON 399
Using JSON with SQL 400
The SQL/JSON Data Model 401
SQL/JSON Functions 403
SQL/JSON Path Language 411
There’s More 412
Part 6: Advanced Topics 413
Chapter 20: Stepping through a Dataset with Cursors 415
Declaring a Cursor 416
Opening a Cursor 421
Fetching Data from a Single Row 422
Closing a Cursor 425
Chapter 21: Adding Procedural Capabilities with Persistent Stored Modules 427
Compound Statements 428
Flow of Control Statements 435
Stored Procedures 440
Stored Functions 442
Privileges 442
Stored Modules 443
Chapter 22: Handling Errors 445
SQLSTATE 445
WHENEVER Clause 447
Diagnostics Areas 448
Handling Exceptions 455
Chapter 23: Triggers 457
Examining Some Applications of Triggers 457
Creating a Trigger 458
Firing a Succession of Triggers 460
Referencing Old Values and New Values 461
Firing Multiple Triggers on a Single Table 462
Part 7: The Parts of Tens 463
Chapter 24: Ten Common Mistakes 465
Assuming That Your Clients Know What They Need 465
Ignoring Project Scope 466
Considering Only Technical Factors 466
Not Asking for Client Feedback 466
Always Using Your Favorite Development Environment 467
Using Your Favorite System Architecture Exclusively 467
Designing Database Tables in Isolation 467
Neglecting Design Reviews 468
Skipping Beta Testing 468
Not Documenting Your Process 468
Chapter 25: Ten Retrieval Tips 469
Verify the Database Structure 470
Try Queries on a Test Database 470
Double-Check Queries That Include Joins 470
Triple-Check Queries with Subselects 470
Summarize Data with GROUP BY 471
Watch GROUP BY Clause Restrictions 471
Use Parentheses with AND, OR, and NOT 471
Control Retrieval Privileges 472
Back Up Your Databases Regularly 472
Handle Error Conditions Gracefully 472
Appendix: ISO/IEC SQL: 2016 Reserved Words 473
Index 479
