SQL All-in-One for Dummies

The latest on SQL databases

SQL All -In-One For Dummies, 3rd Edition, is a one-stop shop for everything you need to know about SQL and SQL-based relational databases. Everyone from database administrators to application programmers and the people who manage them will find clear, concise explanations of the SQL language and its many powerful applications.

With the ballooning amount of data out there, more and more businesses, large and small, are moving from spreadsheets to SQL databases like Access, Microsoft SQL Server, Oracle databases, MySQL, and PostgreSQL. This compendium of information covers designing, developing, and maintaining these databases.

• Cope with any issue that arises in SQL database creation and management
• Get current on the newest SQL updates and capabilities
• Reference information on querying SQL-based databases in the SQL language
• Understand relational databases and their importance to today’s organizations

SQL All-In-One For Dummies is a timely update to the popular reference for readers who want detailed information about SQL databases and queries.

Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Allen's activities, check out his online courses (at pioneer-academy1.teachable.com) and his blog (at www.allengtaylor.com). You can contact Allen at allen.taylor@ieee.org.

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 448

Using 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

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.