SQL all-in-one for dummies - Taylor, Allen G. (Indianapolis, Indiana John Wiley & Sons, 2019)
Introduction 1 About This Book 1 Foolish Assumptions 2 Conventions Used in This Book 3 What You Don't Have to Read 3 How This Book is Organized 3 Book 1: SQL Concepts 3 Book 2: Relational Database Development 4 Book 3: SQL Queries 4 Book 4: Data Security 4 Book 5: SQL and Programming 5 Book 6: SQL and XML 5 Book 7: Database Tuning Overview 5 Book 8: Appendices 5 Icons Used in This Book 6 Where to Go from Here 6 Book 1: SQL Concepts 9 Chapter 1: Understanding Relational Databases 11 Understanding Why Today's Databases are Better than Early Databases 12 Databases, Queries, and Database Applications 16 Examining Competing Database Models 18 Why the Relational Model Won 29 Chapter 2: Modeling a System 31 Capturing the Users' Data Model 31 Translating the Users' Data Model to a Formal Entity-Relationship Model 34 Entity-Relationship modeling techniques 35 Chapter 3: Getting to Know SQL 55 Where SQL Came From 55 Knowing What SQL Does 56 The ISO/IEC SQL Standard 57 Knowing What SQL Does Not Do 57 Choosing and Using an Available DBMS Implementation 58 Chapter 4: SQL and the Relational Model 67 Sets, Relations, Multisets, and Tables 68 Functional Dependencies 69 Keys 70 Views 71 Users 72 Privileges 72 Schemas 73 Catalogs 74 Connections, Sessions, and Transactions 74 Routines 75 Paths 75 Chapter 5: Knowing the Major Components of SQL 77 Creating a Database with the Data Definition Language 77 Operating on Data with the Data Manipulation Language (DML) 88 Maintaining Security in the Data Control Language (DCL) 97 Chapter 6: Drilling Down to the SQL Nitty-Gritty 99 Executing SQL Statements 99 Module language 104 Using Reserved Words Correctly 105 SQL's Data Types 105 Handling Null Values 123 Applying Constraints 124 Book 2: Relational Database Development 131 Chapter 1: System Development Overview 133 The Components of a Database System 133 The System Development Life Cycle 136 Chapter 2: Building a Database Model 149 Finding and Listening to Interested Parties 150 Building Consensus 152 Building a Relational Model 154 Being Aware of the Danger of Anomalies 157 The Database Integrity versus Performance Tradeoff 164 Chapter 3: Balancing Performance and Correctness 167 Designing a Sample Database 168 Maintaining Integrity 179 Avoiding Data Corruption 183 Speeding Data Retrievals 185 Working with Indexes 187 Reading SQL Server Execution Plans 194 Chapter 4: Creating a Database with SQL 199 First Things First: Planning Your Database 199 Building Tables 200 Setting Constraints 204 Keys and Indexes 205 Ensuring Data Validity with Domains 205 Establishing Relationships between Tables 206 Altering Table Structure 210 Deleting Tables 210 Book 3: SQL Queries 211 Chapter 1: Values, Variables, Functions, and Expressions 213 Entering Data Values 213 Working with Functions 217 Using Expressions 229 Chapter 2: SELECT Statements and Modifying Clauses 239 Finding Needles in Haystacks with the SELECT Statement 239 Modifying Clauses 240 Tuning Queries 265 Chapter 3: Querying Multiple Tables with Subqueries 281 What is a Subquery? 281 What Subqueries Do 282 Using Subqueries in INSERT, DELETE, and UPDATE Statements 295 Tuning Considerations for Statements Containing Nested Queries 298 Tuning Correlated Subqueries 304 Chapter 4: Querying Multiple Tables with Relational Operators 309 UNION 310 INTERSECT 313 EXCEPT 315 JOINS 315 ON versus WHERE 327 Join Conditions and Clustering Indexes 327 Chapter 5: Cursors 329 Declaring a Cursor 330 Opening a Cursor 336 Operating on a Single Row 337 Closing a Cursor 340 Book 4: Data Security 341 Chapter 1: Protecting Against Hardware Failure and External Threats 343 What Could Possibly Go Wrong? 344 Taking Advantage of RAID 347 Backing Up Your System 351 Coping with Internet Threats 354 Installing Layers of Protection 371 Chapter 2: Protecting Against User Errors and Conflicts 373 Reducing Data-Entry Errors 374 Coping with Errors in Database Design 375 Handling Programming Errors 376 Solving Concurrent-Operation Conflicts 376 Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability 378 Operating with Transactions 379 Getting Familiar with Locking 391 Tuning Locks 393 Enforcing Serializability with Timestamps 397 Tuning the Recovery System 400 Chapter 3: Assigning Access Privileges 401 Working with the SQL Data Control Language 401 Identifying Authorized Users 402 Classifying Users 404 Granting Privileges 404 Revoking Privileges 410 Granting Roles 411 Revoking Roles 412 Chapter 4: Error Handling 413 Identifying Error Conditions 414 Getting to Know SQLSTATE 414 Handling Conditions 416 Dealing with Execution Exceptions: The WHENEVER Clause 419 Getting More Information: The Diagnostics Area 420 Examining an Example Constraint Violation 424 Adding Constraints to an Existing Table 426 Interpreting SQLSTATE Information 426 Handling Exceptions 427 Book 5: SQL and Programming 429 Chapter 1: Database Development Environments 431 Microsoft Access 431 Microsoft SQL Server 433 IBM Db2 434 Oracle 18c 434 SQL Anywhere 435 PostgreSQL 435 MySQL 435 Chapter 2: Interfacing SQL to a Procedural Language 437 Building an Application with SQL and a Procedural Language 437 Chapter 3: Using SQL in an Application Program 443 Comparing SQL with Procedural Languages 444 Difficulties in Combining SQL with a Procedural Language 446 Embedding SQL in an Application 448Using SQL Modules with an Application 453 Chapter 4: Designing a Sample Application 457 Understanding the Client's Problem 458 Approaching the Problem 458 Determining the Deliverables 460 Building an Entity-Relationship Model 463 Transforming the Model 467 Creating Tables 471 Changing Table Structure 475 Removing Tables 475 Designing the User Interface 475 Chapter 5: Building an Application 477 Designing from the Top Down 477 Coding from the Bottom Up 481 Testing, Testing, Testing 490 Chapter 6: Understanding SQL's Procedural Capabilities 493 Embedding SQL Statements in Your Code 494 Introducing Compound Statements 494 Following the Flow of Control Statements 497 Using Stored Procedures 502 Working with Triggers 503 Using Stored Functions 506 Passing Out Privileges 507 Using Stored Modules 508 Chapter 7: Connecting SQL to a Remote Database 509 Native Drivers 510 ODBC and Its Major Components 511 What Happens When the Application Makes a Request 515 Book 6: SQL, XML, AND JSON 523 Chapter 1: Using XML with SQL 525 Introducing XML 526 Knowing the Parts of an XML Document 527 Using XML Schema 531 Relating SQL to XML 532 Using the XML Data Type 533 Mapping SQL to XML 535 Operating on XML Data with SQL Functions 544 Working with XML Predicates 549 Chapter 2: Storing XML Data in SQL Tables 553 Inserting XML Data into an SQL Pseudotable 553 Creating a Table to Hold XML Data 555 Updating XML Documents 556 Discovering Oracle's Tools for Updating XML Data in a Table 557 Introducing Microsoft's Tools for Updating XML Data in a Table 562 Chapter 3: Retrieving Data from XML Documents 577 XQuery 578 FLWOR Expressions 584 XQuery versus SQL 590 Chapter 4: Using JSON with SQL 595 Using JSON with SQL 595 The SQL/JSON Data Model 596 SQL/JSON Functions 598 SQL/JSON Path Language 607 Book 7: Database Tuning Overview 609 Chapter 1: Tuning the Database 611 Analyzing the Workload 612 Considering the Physical Design 613 Choosing the Right Indexes 614 Tuning Indexes 619 Tuning Queries 620 Tuning Transactions 621 Separating User Interactions from Transactions 622 Minimizing Traffic between Application and Server 622 Precompiling Frequently Used Queries 622 Chapter 2: Tuning the Environment 623 Surviving Failures with Minimum Data Loss 624 Tuning the Recovery System 625 Tuning the Operating System 634 Maximizing the Hardware You Have 639 Adding Hardware 641 Working in Multiprocessor Environments 643 Chapter 3: Finding and Eliminating Bottlenecks 645 Pinpointing the Problem 645 Determining the Possible Causes of Trouble 647 Implementing General Principles: A First Step Toward Improving Performance 651 Tracking Down Bottlenecks 653 Analyzing Query Efficiency 657 Managing Resources Wisely 671 Book 8: Appendices 675 Appendix A: SQL: 2016 Reserved Words 677 Appendix B: Glossary 683 Index 691
SQL is the internationally recognized standard language for dealing with data in relational databases. So whether you're a database administrator, a programmer, or someone who manages these functions, this handy one-stop guide belongs on your shelf! It covers SQL basics, the latest upgrades, database development, security, SQL with XML, and much more.
Keywords: SQL (Computer program language); Relational databases; Database management