SQL All-In-One For Dummies, 3ed

Introduction

About This Book

Foolish Assumptions  

Conventions Used in This Book

What You Don't Have to Read

How This Book is Organized

Book 1: SQL Concepts

Book 2: Relational Database Development

Book 3: SQL Queries

Book 4: Data Security

Book 5: SQL and Programming

Book 6: SQL and XML

Book 7: Database Tuning Overview

Book 8: Appendices

Icons Used in This Book

Where to Go from Here

Book 1: SQL Concepts

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
• Entity-Relationship modeling techniques

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
• Module language
• Using Reserved Words Correctly
• SQL's Data Types
• Handling Null Values
• Applying Constraints

Book 2: Relational Database Development

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: 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 with the 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: Data Security

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: SQL and Programming

Chapter 1: Database Development Environments

• Microsoft Access
• Microsoft SQL Server
• IBM Db2
• Oracle 18c
• 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: SQL, XML, AND JSON

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

Book 7: Database Tuning Overview

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 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: Appendices

Appendix A: SQL: 2016 Reserved Words

Appendix B: Glossary

Index