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 Isnt 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 SQLs 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 Theres More 412 Part 6: Advanced Topics 413 Chapter 20: