SQL For Dummies

Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 40 books, including SQL For Dummies and Crystal Reports For Dummies. He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider.

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