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