SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

by John L. Viescas
Released February 2018
Publisher(s): Addison-Wesley Professional
ISBN: 9780134858432

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The #1 Easy, Common-Sense Guide to SQL Queries—Updated with More Advanced Techniques and Solutions

Foreword by Keith W. Hare, Vice Chair, USA SQL Standards Committee

SQL Queries for Mere Mortals has earned worldwide praise as the clearest, simplest tutorial on writing effective queries with the latest SQL standards and database applications. Now, author John L. Viescas has updated this hands-on classic with even more advanced and valuable techniques.

Step by step, Viescas guides you through creating reliable queries for virtually any current SQL-based database. He demystifies all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.

Building on the basics, Viescas shows how to solve challenging real-world problems, including applying multiple complex conditions on one table, performing sophisticated logical evaluations, and using unlinked tables to think “outside the box.”

In two brand-new chapters, you learn how to perform complex calculations on groups for sophisticated reporting, and how to partition data into windows for more flexible aggregation.

Practice all you want with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL and PostgreSQL databases. Whether you’re a DBA, developer, user, or student, there’s no better way to master SQL.

Coverage includes:

  • Getting started: understanding what relational databases are, and ensuring that your database structures are sound

  • SQL basics: using SELECT statements, creating expressions, sorting information with ORDER BY, and filtering data using WHERE

  • Summarizing and grouping data with GROUP BY and HAVING clauses

  • Drawing data from multiple tables: using INNER JOIN, OUTER JOIN, and UNION operators, and working with subqueries

  • Modifying data sets with UPDATE, INSERT, and DELETE statements

  • Advanced queries: complex NOT and AND, conditions, if-then-else using CASE, unlinked tables, driver tables, and more

  • NEW! Using advanced GROUP BY keywords to create subtotals, roll-ups, and more

  • NEW! Applying window functions to answer more sophisticated questions, and gain deeper insight into your data

Software-Independent Approach!

If you work with database software such as Access, MS SQL Server, Oracle, DB2, MySQL, Ingres, or any other SQL-based program, this book could save you hours of time and aggravation—before you write a single query!

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Contents
  6. Foreword
  7. Preface
  8. Acknowledgments
  9. About the Author
  10. Introduction
    1. Are You a Mere Mortal?
    2. About This Book
    3. What This Book Is Not
    4. How to Use This Book
    5. Reading the Diagrams Used in This Book
    6. Sample Databases Used in This Book
      1. “Follow the Yellow Brick Road”
  11. PART I: RELATIONAL DATABASES AND SQL
    1. Chapter 1: What Is Relational?
      1. Topics Covered in This Chapter
      2. Types of Databases
      3. A Brief History of the Relational Model
        1. In the Beginning …
        2. Relational Database Systems
      4. Anatomy of a Relational Database
        1. Tables
        2. Columns
        3. Rows
        4. Keys
        5. Views
        6. Relationships
      5. What’s in It for You?
        1. Where Do You Go from Here?
      6. Summary
    2. Chapter 2: Ensuring Your Database Structure Is Sound
      1. Topics Covered in This Chapter
      2. Why Is this Chapter Here?
      3. Why Worry about Sound Structures?
      4. Fine-Tuning Columns
        1. What’s in a Name? (Part One)
        2. Smoothing Out the Rough Edges
        3. Resolving Multipart Columns
        4. Resolving Multivalued Columns
      5. Fine-Tuning Tables
        1. What’s in a Name? (Part Two)
        2. Ensuring a Sound Structure
        3. Resolving Unnecessary Duplicate Columns
        4. Identification Is the Key
      6. Establishing Solid Relationships
        1. Establishing a Deletion Rule
        2. Setting the Type of Participation
        3. Setting the Degree of Participation
      7. Is That All?
      8. Summary
    3. Chapter 3: A Concise History of SQL
      1. Topics Covered in This Chapter
      2. The Origins of SQL
      3. Early Vendor Implementations
      4. “… And Then There Was a Standard”
      5. Evolution of the ANSI/ISO Standard
        1. Other SQL Standards
      6. Commercial Implementations
      7. What the Future Holds
      8. Why Should You Learn SQL?
      9. Which Version of SQL Does this Book Cover?
      10. Summary
  12. PART II: SQL BASICS
    1. Chapter 4: Creating a Simple Query
      1. Topics Covered in This Chapter
      2. Introducing SELECT
      3. The SELECT Statement
      4. A Quick Aside: Data versus Information
      5. Translating Your Request into SQL
        1. Expanding the Field of Vision
        2. Using a Shortcut to Request All Columns
      6. Eliminating Duplicate Rows
      7. Sorting Information
        1. First Things First: Collating Sequences
        2. Let’s Now Come to Order
      8. Saving Your Work
      9. Sample Statements
      10. Summary
      11. Problems for You to Solve
    2. Chapter 5: Getting More Than Simple Columns
      1. Topics Covered in This Chapter
      2. What Is an Expression?
      3. What Type of Data Are You Trying to Express?
      4. Changing Data Types: The CAST Function
      5. Specifying Explicit Values
        1. Character String Literals
        2. Numeric Literals
        3. Datetime Literals
      6. Types of Expressions
        1. Concatenation
        2. Mathematical Expressions
        3. Date and Time Arithmetic
      7. Using Expressions in a SELECT Clause
        1. Working with a Concatenation Expression
        2. Naming the Expression
        3. Working with a Mathematical Expression
        4. Working with a Date Expression
        5. A Brief Digression: Value Expressions
      8. That “Nothing” Value: Null
        1. Introducing Null
        2. The Problem with Nulls
      9. Sample Statements
      10. Summary
      11. Problems for You to Solve
    3. Chapter 6: Filtering Your Data
      1. Topics Covered in This Chapter
      2. Refining What You See Using WHERE
        1. The WHERE Clause
        2. Using a WHERE Clause
      3. Defining Search Conditions
        1. Comparison
        2. Range
        3. Set Membership
        4. Pattern Match
        5. Null
        6. Excluding Rows with NOT
      4. Using Multiple Conditions
        1. Introducing AND and OR
        2. Excluding Rows: Take Two
        3. Order of Precedence
        4. Checking for Overlapping Ranges
      5. Nulls Revisited: A Cautionary Note
      6. Expressing Conditions in Different Ways
      7. Sample Statements
      8. Summary
      9. Problems for You to Solve
  13. PART III: WORKING WITH MULTIPLE TABLES
    1. Chapter 7: Thinking in Sets
      1. Topics Covered in This Chapter
      2. What Is a Set, Anyway?
      3. Operations on Sets
      4. Intersection
        1. Intersection in Set Theory
        2. Intersection between Result Sets
        3. Problems You Can Solve with an Intersection
      5. Difference
        1. Difference in Set Theory
        2. Difference between Result Sets
        3. Problems You Can Solve with Difference
      6. Union
        1. Union in Set Theory
        2. Combining Result Sets Using a Union
        3. Problems You Can Solve with Union
      7. SQL Set Operations
        1. Classic Set Operations versus SQL
        2. Finding Common Values: INTERSECT
        3. Finding Missing Values: EXCEPT (DIFFERENCE)
        4. Combining Sets: UNION
      8. Summary
    2. Chapter 8: INNER JOINs
      1. Topics Covered in This Chapter
      2. What Is a JOIN?
      3. The INNER JOIN
        1. What’s “Legal” to JOIN?
        2. Column References
        3. Syntax
        4. Check Those Relationships!
      4. Uses for INNER JOINs
        1. Find Related Rows
        2. Find Matching Values
      5. Sample Statements
        1. Two Tables
        2. More Than Two Tables
        3. Looking for Matching Values
      6. Summary
      7. Problems for You to Solve
    3. Chapter 9: OUTER JOINs
      1. Topics Covered in This Chapter
      2. What Is an OUTER JOIN?
      3. The LEFT/RIGHT OUTER JOIN
        1. Syntax
      4. The FULL OUTER JOIN
        1. Syntax
        2. FULL OUTER JOIN on Non-Key Values
        3. UNION JOIN
      5. Uses for OUTER JOINs
        1. Find Missing Values
        2. Find Partially Matched Information
      6. Sample Statements
      7. Summary
      8. Problems for You to Solve
    4. Chapter 10: UNIONs
      1. Topics Covered in This Chapter
      2. What Is a UNION?
      3. Writing Requests with UNION
        1. Using Simple SELECT Statements
        2. Combining Complex SELECT Statements
        3. Using UNION More Than Once
        4. Sorting a UNION
      4. Uses for UNION
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
    5. Chapter 11: Subqueries
      1. Topics Covered in This Chapter
      2. What Is a Subquery?
        1. Row Subqueries
        2. Table Subqueries
        3. Scalar Subqueries
      3. Subqueries as Column Expressions
        1. Syntax
        2. An Introduction to Aggregate Functions: COUNT and MAX
      4. Subqueries as Filters
        1. Syntax
        2. Special Predicate Keywords for Subqueries
      5. Uses for Subqueries
        1. Build Subqueries as Column Expressions
        2. Use Subqueries as Filters
      6. Sample Statements
        1. Subqueries in Expressions
        2. Subqueries in Filters
      7. Summary
      8. Problems for You to Solve
  14. PART IV: SUMMARIZING AND GROUPING DATA
    1. Chapter 12: Simple Totals
      1. Topics Covered in This Chapter
      2. Aggregate Functions
        1. Counting Rows and Values with COUNT
        2. Computing a Total with SUM
        3. Calculating a Mean Value with AVG
        4. Finding the Largest Value with MAX
        5. Finding the Smallest Value with MIN
        6. Using More Than One Function
      3. Using Aggregate Functions in Filters
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    2. Chapter 13: Grouping Data
      1. Topics Covered in This Chapter
      2. Why Group Data?
      3. The GROUP BY Clause
        1. Syntax
        2. Mixing Columns and Expressions
        3. Using GROUP BY in a Subquery in a WHERE Clause
        4. Simulating a SELECT DISTINCT Statement
      4. “Some Restrictions Apply”
        1. Column Restrictions
        2. Grouping on Expressions
      5. Uses for GROUP BY
      6. Sample Statements
      7. Summary
      8. Problems for You to Solve
    3. Chapter 14: Filtering Grouped Data
      1. Topics Covered in This Chapter
      2. A New Meaning for “Focus Groups”
      3. Where You Filter Makes a Difference
        1. Should You Filter in WHERE or in HAVING?
        2. Avoiding the HAVING COUNT Trap
      4. Uses for HAVING
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
  15. PART V: MODIFYING SETS OF DATA
    1. Chapter 15: Updating Sets of Data
      1. Topics Covered in This Chapter
      2. What Is an UPDATE?
      3. The UPDATE Statement
        1. Using a Simple UPDATE Expression
        2. A Brief Aside: Transactions
        3. Updating Multiple Columns
        4. Using a Subquery to Filter Rows
      4. Some Database Systems Allow a JOIN in the UPDATE Clause
        1. Using a Subquery UPDATE Expression
      5. Uses for UPDATE
      6. Sample Statements
      7. Summary
      8. Problems for You to Solve
    2. Chapter 16: Inserting Sets of Data
      1. Topics Covered in This Chapter
      2. What Is an INSERT?
      3. The INSERT Statement
        1. Inserting Values
        2. Generating the Next Primary Key Value
        3. Inserting Data by Using SELECT
      4. Uses for INSERT
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
    3. Chapter 17: Deleting Sets of Data
      1. Topics Covered in This Chapter
      2. What Is a DELETE?
      3. The DELETE Statement
        1. Deleting All Rows
        2. Deleting Some Rows
      4. Uses for DELETE
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
  16. PART VI: INTRODUCTION TO SOLVING TOUGH PROBLEMS
    1. Chapter 18: “NOT” and “AND” Problems
      1. Topics Covered in This Chapter
      2. A Short Review of Sets
        1. Sets with Multiple AND Criteria
        2. Sets with Multiple NOT Criteria
        3. Sets Including Some Criteria but Excluding Others
      3. Finding Out the “Not” Case
        1. Using OUTER JOIN
        2. Using NOT IN
        3. Using NOT EXISTS
        4. Using GROUP BY/HAVING
      4. Finding Multiple Matches in the Same Table
        1. Using INNER JOIN
        2. Using IN
        3. Using EXISTS
        4. Using GROUP BY/HAVING
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
    2. Chapter 19: Condition Testing
      1. Topics Covered in This Chapter
      2. Conditional Expressions (CASE)
        1. Why Use CASE?
        2. Syntax
      3. Solving Problems with CASE
        1. Solving Problems with Simple CASE
        2. Solving Problems with Searched CASE
        3. Using CASE in a WHERE Clause
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    3. Chapter 20: Using Unlinked Data and “Driver” Tables
      1. Topics Covered in This Chapter
      2. What Is Unlinked Data?
        1. Deciding When to Use a CROSS JOIN
      3. Solving Problems with Unlinked Data
      4. Solving Problems Using “Driver” Tables
        1. Setting Up a Driver Table
        2. Using a Driver Table
      5. Sample Statements
        1. Examples Using Unlinked Tables
        2. Examples Using Driver Tables
      6. Summary
      7. Problems for You to Solve
    4. Chapter 21: Performing Complex Calculations on Groups
      1. Topics in this Chapter
      2. Grouping in Sub-Groups
      3. Extending the GROUP BY Clause
        1. Syntax
      4. Getting Totals in a Hierarchy Using Rollup
      5. Calculating Totals on Combinations Using CUBE
      6. Creating a Union of Totals with GROUPING SETS
      7. Variations on Grouping Techniques
      8. Sample Statements
        1. Examples using ROLLUP
        2. Examples using CUBE
        3. Examples using GROUPING SETS
      9. Summary
      10. Problems for You to Solve
    5. Chapter 22: Partitioning Data into Windows
      1. Topics in this Chapter
      2. What You Can Do With a “Window” into Your Data
        1. Syntax
      3. Calculating a Row Number
      4. Ranking Data
      5. Splitting Data into Quintiles
      6. Using Windows with Aggregate Functions
      7. Sample Statements
        1. Examples Using ROW_NUMBER
        2. Examples Using RANK, DENSE_RANK, and PERCENT_RANK
        3. Examples Using NTILE
        4. Examples Using Aggregate Functions
      8. Summary
      9. Problems for You to Solve
    6. In Closing
  17. PART VII: APPENDICES
    1. Appendix A: SQL Standard Diagrams
    2. Appendix B: Schema for the Sample Databases
      1. Sales Orders Example Database
      2. Sales Orders Modify Database
      3. Entertainment Agency Example Database
      4. Entertainment Agency Modify Database
      5. School Scheduling Example Database
      6. School Scheduling Modify Database
      7. Bowling League Example Database
      8. Bowling League Modify Database
      9. Recipes Database
      10. “Driver” Tables
    3. Appendix C: Date and Time Types, Operations, and Functions
      1. IBM DB2
      2. Microsoft Access
      3. Microsoft SQL Server
      4. MySQL
      5. Oracle
      6. PostgreSQL
    4. Appendix D: Suggested Reading
      1. Database Books
      2. Books on SQL
  18. Index

Product information

  • Title: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition
  • Author(s): John L. Viescas
  • Release date: February 2018
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 9780134858432