Hjem

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