0%

Book Description

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.

Table of Contents

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