SQL All-in-One For Dummies

Cover
Table of Contents
Title Page
Copyright
Introduction
   About This Book
   Foolish Assumptions
   Icons Used in This Book
   Beyond the Book
   Where to Go from Here
Book 1: Getting Started with SQL
   Chapter 1: Understanding Relational Databases
      Understanding Why Today’s Databases Are Better than Early Databases
      Databases, Queries, and Database Applications
      Examining Competing Database Models
      Why the Relational Model Won
   Chapter 2: Modeling a System
      Capturing the Users’ Data Model
      Translating the Users’ Data Model to a Formal Entity-Relationship Model
   Chapter 3: Getting to Know SQL
      Where SQL Came From
      Knowing What SQL Does
      The ISO/IEC SQL Standard
      Knowing What SQL Does Not Do
      Choosing and Using an Available DBMS Implementation
   Chapter 4: SQL and the Relational Model
      Sets, Relations, Multisets, and Tables
      Functional Dependencies
      Keys
      Views
      Users
      Privileges
      Schemas
      Catalogs
      Connections, Sessions, and Transactions
      Routines
      Paths
   Chapter 5: Knowing the Major Components of SQL
      Creating a Database with the Data Definition Language
      Operating on Data with the Data Manipulation Language (DML)
      Maintaining Security in the Data Control Language (DCL)
   Chapter 6: Drilling Down to the SQL Nitty-Gritty
      Executing SQL Statements
      Using Reserved Words Correctly
      SQL’s Data Types
      Handling Null Values
      Applying Constraints
Book 2: Developing Relational Databases
   Chapter 1: System Development Overview
      The Components of a Database System
      The System Development Life Cycle
   Chapter 2: Building a Database Model
      Finding and Listening to Interested Parties
      Building Consensus
      Building a Relational Model
      Being Aware of the Danger of Anomalies
      The Database Integrity versus Performance Tradeoff
   Chapter 3: Balancing Performance and Correctness
      Designing a Sample Database
      Maintaining Integrity
      Avoiding Data Corruption
      Speeding Data Retrievals
      Working with Indexes
      Reading SQL Server Execution Plans
   Chapter 4: Creating a Database with SQL
      First Things First: Planning Your Database
      Building Tables
      Setting Constraints
      Keys and Indexes
      Ensuring Data Validity with Domains
      Establishing Relationships between Tables
      Altering Table Structure
      Deleting Tables
Book 3: Writing SQL Queries
   Chapter 1: Values, Variables, Functions, and Expressions
      Entering Data Values
      Working with Functions
      Using Expressions
   Chapter 2: SELECT Statements and Modifying Clauses
      Finding Needles in Haystacks withthe SELECT Statement
      Modifying Clauses
      Tuning Queries
   Chapter 3: Querying Multiple Tables with Subqueries
      What Is a Subquery?
      What Subqueries Do
      Using Subqueries in INSERT, DELETE, and UPDATE Statements
      Tuning Considerations for Statements Containing Nested Queries
      Tuning Correlated Subqueries
   Chapter 4: Querying Multiple Tables with Relational Operators
      UNION
      INTERSECT
      EXCEPT
      JOINS
      ON versus WHERE
      Join Conditions and Clustering Indexes
   Chapter 5: Cursors
      Declaring a Cursor
      Opening a Cursor
      Operating on a Single Row
      Closing a Cursor
Book 4: Securing Your Data
   Chapter 1: Protecting Against Hardware Failure and External Threats
      What Could Possibly Go Wrong?
      Taking Advantage of RAID
      Backing Up Your System
      Coping with Internet Threats
      Installing Layers of Protection
   Chapter 2: Protecting Against User Errors and Conflicts
      Reducing Data-Entry Errors
      Coping with Errors in Database Design
      Handling Programming Errors
      Solving Concurrent-Operation Conflicts
      Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability
      Operating with Transactions
      Getting Familiar with Locking
      Tuning Locks
      Enforcing Serializability with Timestamps
      Tuning the Recovery System
   Chapter 3: Assigning Access Privileges
      Working with the SQL Data Control Language
      Identifying Authorized Users
      Classifying Users
      Granting Privileges
      Revoking Privileges
      Granting Roles
      Revoking Roles
   Chapter 4: Error Handling
      Identifying Error Conditions
      Getting to Know SQLSTATE
      Handling Conditions
      Dealing with Execution Exceptions: The WHENEVER Clause
      Getting More Information: The Diagnostics Area
      Examining an Example Constraint Violation
      Adding Constraints to an Existing Table
      Interpreting SQLSTATE Information
      Handling Exceptions
Book 5: Programming with SQL
   Chapter 1: Database Development Environments
      Microsoft Access
      Microsoft SQL Server
      IBM Db2
      Oracle 23c
      SQL Anywhere
      PostgreSQL
      MySQL
   Chapter 2: Interfacing SQL to a Procedural Language
      Building an Application with SQL and a Procedural Language
   Chapter 3: Using SQL in an Application Program
      Comparing SQL with Procedural Languages
      Difficulties in Combining SQL with a Procedural Language
      Embedding SQL in an Application
      Using SQL Modules with an Application
   Chapter 4: Designing a Sample Application
      Understanding the Client’s Problem
      Approaching the Problem
      Determining the Deliverables
      Building an Entity-Relationship Model
      Transforming the Model
      Creating Tables
      Changing Table Structure
      Removing Tables
      Designing the User Interface
   Chapter 5: Building an Application
      Designing from the Top Down
      Coding from the Bottom Up
      Testing, Testing, Testing
   Chapter 6: Understanding SQL’s Procedural Capabilities
      Embedding SQL Statements in Your Code
      Introducing Compound Statements
      Following the Flow of Control Statements
      Using Stored Procedures
      Working with Triggers
      Using Stored Functions
      Passing Out Privileges
      Using Stored Modules
   Chapter 7: Connecting SQL to a Remote Database
      Native Drivers
      ODBC and Its Major Components
      What Happens When the Application Makes a Request
Book 6: Working with Advanced Data Types in SQL: XML, JSON, and PGQ
   Chapter 1: Using XML with SQL
      Introducing XML
      Knowing the Parts of an XML Document
      Using XML Schema
      Relating SQL to XML
      Using the XML Data Type
      Mapping SQL to XML
      Operating on XML Data with SQL Functions
      Working with XML Predicates
   Chapter 2: Storing XML Data in SQL Tables
      Inserting XML Data into an SQL Pseudotable
      Creating a Table to Hold XML Data
      Updating XML Documents
      Discovering Oracle’s Tools for Updating XML Data in a Table
      Introducing Microsoft’s Tools for Updating XML Data in a Table
   Chapter 3: Retrieving Data from XML Documents
      XQuery
      FLWOR Expressions
      XQuery versus SQL
   Chapter 4: Using JSON with SQL
      Using JSON with SQL
      The SQL/JSON Data Model
      SQL/JSON Functions
      SQL/JSON Path Language
      SQL:2023 JSON Enhancements
   Chapter 5: Exploring Property Graph Queries
      What Are Property Graph Queries?
      Examining SQL/PGQ
      Working with SQL/PGQ
Book 7: Optimizing Your Database
   Chapter 1: Tuning the Database
      Analyzing the Workload
      Considering the Physical Design
      Choosing the Right Indexes
      Tuning Indexes
      Tuning Queries
      Tuning Transactions
      Separating User Interactions from Transactions
      Minimizing Traffic between Application and Server
      Precompiling Frequently Used Queries
   Chapter 2: Tuning the Environment
      Surviving Failures with Minimum Data Loss
      Tuning the Recovery System
      Tuning the Operating System
      Maximizing the Hardware You Have
      Adding Hardware
      Working in Multiprocessor Environments
   Chapter 3: Finding and Eliminating Performance Bottlenecks
      Pinpointing the Problem
      Determining the Possible Causes of Trouble
      Implementing General Principles: A First Step Toward Improving Performance
      Tracking Down Bottlenecks
      Analyzing Query Efficiency
      Managing Resources Wisely
Book 8: Appendixes
   Appendix A: SQL:2023 Reserved Words
   Appendix B: Glossary
Index
About the Authors
Connect with Dummies
End User License Agreement