SQL For Dummies, 9th Edition

Book description

Get ready to make SQL easy!

Updated for the latest version of SQL, the new edition of this perennial bestseller shows programmers and web developers how to use SQL to build relational databases and get valuable information from them. Covering everything you need to know to make working with SQL easier than ever, topics include how to use SQL to structure a DBMS and implement a database design; secure a database; and retrieve information from a database; and much more.  

SQL is the international standard database language used to create, access, manipulate, maintain, and store information in relational database management systems (DBMS) such as Access, Oracle, SQL Server, and MySQL. SQL adds powerful data manipulation and retrieval capabilities to conventional languages—and this book shows you how to harness the core element of relational databases with ease.

  • Server platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems
  • Find great examples on the use of temporal data
  • Jump right in—without previous knowledge of database programming or SQL

As database-driven websites continue to grow in popularity—and complexity—SQL For Dummies is the easy-to-understand, go-to resource you need to use it seamlessly.

Table of contents

  1. Cover
  2. Introduction
    1. About This Book
    2. Foolish Assumptions
    3. Icons Used in This Book
    4. Beyond the Book
    5. Where to Go from Here
  3. Part 1: Getting Started with SQL
    1. Chapter 1: Relational Database Fundamentals
      1. Keeping Track of Things
      2. What Is a Database?
      3. Database Size and Complexity
      4. What Is a Database Management System?
      5. Flat Files
      6. Database Models
      7. Database Design Considerations
    2. Chapter 2: SQL Fundamentals
      1. What SQL Is and Isn’t
      2. A (Very) Little History
      3. SQL Statements
      4. Reserved Words
      5. Data Types
      6. Null Values
      7. Constraints
      8. Using SQL in a Client/Server System
      9. Using SQL on the Internet or an Intranet
    3. Chapter 3: The Components of SQL
      1. Data Definition Language
      2. Data Manipulation Language
      3. Data Control Language
  4. Part 2: Using SQL to Build Databases
    1. Chapter 4: Building and Maintaining a Simple Database Structure
      1. Using a RAD Tool to Build a Simple Database
      2. Building POWER with SQL’s DDL
      3. Portability Considerations
    2. Chapter 5: Building a Multi-table Relational Database
      1. Designing a Database
      2. Working with Indexes
      3. Maintaining Data Integrity
      4. Normalizing the Database
  5. Part 3: Storing and Retrieving Data
    1. Chapter 6: Manipulating Database Data
      1. Retrieving Data
      2. Creating Views
      3. Updating Views
      4. Adding New Data
      5. Updating Existing Data
      6. Transferring Data
      7. Deleting Obsolete Data
    2. Chapter 7: Handling Temporal Data
      1. Understanding Times and Periods
      2. Working with Application-Time Period Tables
      3. Working with System-Versioned Tables
      4. Tracking Even More Time Data with Bitemporal Tables
      5. Formatting and Parsing Dates and Times
    3. Chapter 8: Specifying Values
      1. Values
      2. Value Expressions
      3. Functions
    4. Chapter 9: Using Advanced SQL Value Expressions
      1. CASE Conditional Expressions
      2. CAST Data-Type Conversions
      3. Row Value Expressions
    5. Chapter 10: Zeroing In on the Data You Want
      1. Modifying Clauses
      2. FROM Clauses
      3. WHERE Clauses
      4. Logical Connectives
      5. GROUP BY Clauses
      6. HAVING Clauses
      7. ORDER BY Clauses
      8. Limited FETCH
      9. Peering through a Window to Create a Result Set
    6. Chapter 11: Using Relational Operators
      1. UNION
      2. INTERSECT
      3. EXCEPT
      4. Join Operators
      5. ON versus WHERE
    7. Chapter 12: Delving Deep with Nested Queries
      1. What Subqueries Do
    8. Chapter 13: Recursive Queries
      1. What Is Recursion?
      2. What Is a Recursive Query?
      3. Where Might You Use a Recursive Query?
      4. Where Else Might You Use a Recursive Query?
  6. Part 4: Controlling Operations
    1. Chapter 14: Providing Database Security
      1. The SQL Data Control Language
      2. User Access Levels
      3. Granting Privileges to Users
      4. Granting Privileges across Levels
      5. Granting the Power to Grant Privileges
      6. Taking Privileges Away
      7. Using GRANT and REVOKE Together to Save Time and Effort
    2. Chapter 15: Protecting Data
      1. Threats to Data Integrity
      2. Reducing Vulnerability to Data Corruption
      3. Constraints Within Transactions
      4. Avoiding SQL Injection Attacks
    3. Chapter 16: Using SQL within Applications
      1. SQL in an Application
      2. Hooking SQL into Procedural Languages
  7. Part 5: Taking SQL to the Real World
    1. Chapter 17: Accessing Data with ODBC and JDBC
      1. ODBC
      2. ODBC in a Client/Server Environment
      3. ODBC and the Internet
      4. ODBC and an Intranet
      5. JDBC
    2. Chapter 18: Operating on XML Data with SQL
      1. How XML Relates to SQL
      2. The XML Data Type
      3. Mapping SQL to XML and XML to SQL
      4. SQL Functions That Operate on XML Data
      5. Predicates
      6. Transforming XML Data into SQL Tables
      7. Mapping Non-Predefined Data Types to XML
      8. The Marriage of SQL and XML
    3. Chapter 19: SQL and JSON
      1. Using JSON with SQL
      2. The SQL/JSON Data Model
      3. SQL/JSON Functions
      4. SQL/JSON Path Language
      5. There's More
  8. Part 6: Advanced Topics
    1. Chapter 20: Stepping through a Dataset with Cursors
      1. Declaring a Cursor
      2. Opening a Cursor
      3. Fetching Data from a Single Row
      4. Closing a Cursor
    2. Chapter 21: Adding Procedural Capabilities with Persistent Stored Modules
      1. Compound Statements
      2. Flow of Control Statements
      3. Stored Procedures
      4. Stored Functions
      5. Privileges
      6. Stored Modules
    3. Chapter 22: Handling Errors
      1. SQLSTATE
      2. WHENEVER Clause
      3. Diagnostics Areas
      4. Handling Exceptions
    4. Chapter 23: Triggers
      1. Examining Some Applications of Triggers
      2. Creating a Trigger
      3. Firing a Succession of Triggers
      4. Referencing Old Values and New Values
      5. Firing Multiple Triggers on a Single Table
  9. Part 7: The Parts of Tens
    1. Chapter 24: Ten Common Mistakes
      1. Assuming That Your Clients Know What They Need
      2. Ignoring Project Scope
      3. Considering Only Technical Factors
      4. Not Asking for Client Feedback
      5. Always Using Your Favorite Development Environment
      6. Using Your Favorite System Architecture Exclusively
      7. Designing Database Tables in Isolation
      8. Neglecting Design Reviews
      9. Skipping Beta Testing
      10. Not Documenting Your Process
    2. Chapter 25: Ten Retrieval Tips
      1. Verify the Database Structure
      2. Try Queries on a Test Database
      3. Double-Check Queries That Include Joins
      4. Triple-Check Queries with Subselects
      5. Summarize Data with GROUP BY
      6. Watch GROUP BY Clause Restrictions
      7. Use Parentheses with AND, OR, and NOT
      8. Control Retrieval Privileges
      9. Back Up Your Databases Regularly
      10. Handle Error Conditions Gracefully
  10. Appendix: ISO/IEC SQL: 2016 Reserved Words
  11. Index
  12. About the Author
  13. Connect with Dummies
  14. End User License Agreement

Product information

  • Title: SQL For Dummies, 9th Edition
  • Author(s): Allen G. Taylor
  • Release date: December 2018
  • Publisher(s): For Dummies
  • ISBN: 9781119527077