Learn How Wiley is Aiding the Global Community in Response to COVID-19



SQL All-In-One For Dummies, 3ed

Allen G. Taylor

ISBN: 9788126534494

776 pages

INR 899

Description

SQL is the international standard language for creating and maintaining relational databases. This book is a compendium of information about SQL and relational database design, development, and maintenance. The eight mini-books cover the full spectrum of issues that arise in building, using, and maintaining relational database systems. 

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