Taylor A.G. SQL All-in-One For Dummies

3rd Edition. — John Wiley & Sons, 2019. — 816 p. — ISBN: 1119569613.The latest on SQL databasesSQL 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.About This BookFoolish Assumptions
Conventions Used in This Book
What You Don’t Have to Read
How This Book Is Organized
Icons Used in This Book
Where to Go from HereSQL ConceptsUnderstanding 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 WonModeling a System
Capturing the Users’ Data Model
Translating the Users’ Data Model to a Formal Entity-Relationship ModelGetting 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 ImplementationSQL and the Relational Model
Sets, Relations, Multisets, and Tables
Functional Dependencies
Keys
Views
Users
Privileges
Schemas
Catalogs
Connections, Sessions, and Transactions
Routines
PathsKnowing 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)Drilling Down to the SQL Nitty-Gritty
Executing SQL Statements
Using Reserved Words Correctly
SQL’s Data Types
Handling Null Values
Applying ConstraintsRelational Database DevelopmentSystem Development Overview
The Components of a Database System
The System Development Life CycleBuilding 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 TradeoffBalancing Performance and Correctness
Designing a Sample Database
Maintaining Integrity
Avoiding Data Corruption
Speeding Data Retrievals
Working with Indexes
Reading SQL Server Execution PlansCreating 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 TablesSQL QueriesValues, Variables, Functions, and
Expressions
Entering Data Values
Working with Functions
Using ExpressionsSELECT Statements and Modifying Clauses
Finding Needles in Haystacks with the SELECT Statement
Modifying Clauses
Tuning QueriesQuerying 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 SubqueriesQuerying Multiple Tables with Relational
Operators
UNION
INTERSECT
EXCEPT
JOINS
ON versus WHERE
Join Conditions and Clustering IndexesCursors
Declaring a Cursor
Opening a Cursor
Operating on a Single Row
Closing a CursorData SecurityProtecting 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 ProtectionProtecting 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 SystemAssigning Access Privileges
Working with the SQL Data Control Language
Identifying Authorized Users
Classifying Users
Granting Privileges
Revoking Privileges
Granting Roles
Revoking RolesError 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 ExceptionsSQL and ProgrammingDatabase Development Environments
Microsoft Access
Microsoft SQL Server
IBM Db2
Oracle 18c
SQL Anywhere
PostgreSQL
MySQLInterfacing SQL to a Procedural Language
Building an Application with SQL and a Procedural LanguageUsing 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 ApplicationDesigning 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 InterfaceBuilding an Application
Designing from the Top Down
Coding from the Bottom Up
Testing, Testing, TestingUnderstanding 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 ModulesConnecting SQL to a Remote Database
Native Drivers
ODBC and Its Major Components
What Happens When the Application Makes a RequestSQL, XML, and JSONUsing 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 PredicatesStoring 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 TableRetrieving Data from XML Documents
XQuery
FLWOR Expressions
XQuery versus SQLUsing JSON with SQL
Using JSON with SQL
The SQL/JSON Data Model
SQL/JSON Functions
SQL/JSON Path LanguageDatabase Tuning OverviewTuning 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 QueriesTuning 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 EnvironmentsFinding 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 WiselySQL: 2016 Reserved WordsAdvertisement Page
Connect with Dummies
End User License Agreement