SQL Queries for Mere Mortals · A Hands-On Guide to Data Manipulation in SQL by Viescas, John L. -- Read -- Imperial Library of Trantor

Index

Cover Page Title Page Copyright Page Contents at a Glance Contents Foreword Preface Acknowledgments About the Author Introduction

Are You a Mere Mortal? About This Book What This Book Is Not How to Use This Book Reading the Diagrams Used in This Book Sample Databases Used in This Book

“Follow the Yellow Brick Road”

PART I: RELATIONAL DATABASES AND SQL

Chapter 1: What Is Relational?

Topics Covered in This Chapter Types of Databases A Brief History of the Relational Model

In the Beginning … Relational Database Systems

Anatomy of a Relational Database

Tables Columns Rows Keys Views Relationships

What’s in It for You?

Where Do You Go from Here?

Summary

Chapter 2: Ensuring Your Database Structure Is Sound

Topics Covered in This Chapter Why Is this Chapter Here? Why Worry about Sound Structures? Fine-Tuning Columns

What’s in a Name? (Part One) Smoothing Out the Rough Edges Resolving Multipart Columns Resolving Multivalued Columns

Fine-Tuning Tables

What’s in a Name? (Part Two) Ensuring a Sound Structure Resolving Unnecessary Duplicate Columns Identification Is the Key

Establishing Solid Relationships

Establishing a Deletion Rule Setting the Type of Participation Setting the Degree of Participation

Is That All? Summary

Chapter 3: A Concise History of SQL

Topics Covered in This Chapter The Origins of SQL Early Vendor Implementations “… And Then There Was a Standard” Evolution of the ANSI/ISO Standard

Other SQL Standards

Commercial Implementations What the Future Holds Why Should You Learn SQL? Which Version of SQL Does this Book Cover? Summary

PART II: SQL BASICS

Chapter 4: Creating a Simple Query

Topics Covered in This Chapter Introducing SELECT The SELECT Statement A Quick Aside: Data versus Information Translating Your Request into SQL

Expanding the Field of Vision Using a Shortcut to Request All Columns

Eliminating Duplicate Rows Sorting Information

First Things First: Collating Sequences Let’s Now Come to Order

Saving Your Work Sample Statements Summary Problems for You to Solve

Chapter 5: Getting More Than Simple Columns

Topics Covered in This Chapter What Is an Expression? What Type of Data Are You Trying to Express? Changing Data Types: The CAST Function Specifying Explicit Values

Character String Literals Numeric Literals Datetime Literals

Types of Expressions

Concatenation Mathematical Expressions Date and Time Arithmetic

Using Expressions in a SELECT Clause

Working with a Concatenation Expression Naming the Expression Working with a Mathematical Expression Working with a Date Expression A Brief Digression: Value Expressions

That “Nothing” Value: Null

Introducing Null The Problem with Nulls

Sample Statements Summary Problems for You to Solve

Chapter 6: Filtering Your Data

Topics Covered in This Chapter Refining What You See Using WHERE

The WHERE Clause Using a WHERE Clause

Defining Search Conditions

Comparison Range Set Membership Pattern Match Null Excluding Rows with NOT

Using Multiple Conditions

Introducing AND and OR Excluding Rows: Take Two Order of Precedence Checking for Overlapping Ranges

Nulls Revisited: A Cautionary Note Expressing Conditions in Different Ways Sample Statements Summary Problems for You to Solve

PART III: WORKING WITH MULTIPLE TABLES

Chapter 7: Thinking in Sets

Topics Covered in This Chapter What Is a Set, Anyway? Operations on Sets Intersection

Intersection in Set Theory Intersection between Result Sets Problems You Can Solve with an Intersection

Difference

Difference in Set Theory Difference between Result Sets Problems You Can Solve with Difference

Union

Union in Set Theory Combining Result Sets Using a Union Problems You Can Solve with Union

SQL Set Operations

Classic Set Operations versus SQL Finding Common Values: INTERSECT Finding Missing Values: EXCEPT (DIFFERENCE) Combining Sets: UNION

Summary

Chapter 8: INNER JOINs

Topics Covered in This Chapter What Is a JOIN? The INNER JOIN

What’s “Legal” to JOIN? Column References Syntax Check Those Relationships!

Uses for INNER JOINs

Find Related Rows Find Matching Values

Sample Statements

Two Tables More Than Two Tables Looking for Matching Values

Summary Problems for You to Solve

Chapter 9: OUTER JOINs

Topics Covered in This Chapter What Is an OUTER JOIN? The LEFT/RIGHT OUTER JOIN

Syntax

The FULL OUTER JOIN

Syntax FULL OUTER JOIN on Non-Key Values UNION JOIN

Uses for OUTER JOINs

Find Missing Values Find Partially Matched Information

Sample Statements Summary Problems for You to Solve

Chapter 10: UNIONs

Topics Covered in This Chapter What Is a UNION? Writing Requests with UNION

Using Simple SELECT Statements Combining Complex SELECT Statements Using UNION More Than Once Sorting a UNION

Uses for UNION Sample Statements Summary Problems for You to Solve

Chapter 11: Subqueries

Topics Covered in This Chapter What Is a Subquery?

Row Subqueries Table Subqueries Scalar Subqueries

Subqueries as Column Expressions

Syntax An Introduction to Aggregate Functions: COUNT and MAX

Subqueries as Filters

Syntax Special Predicate Keywords for Subqueries

Uses for Subqueries

Build Subqueries as Column Expressions Use Subqueries as Filters

Sample Statements

Subqueries in Expressions Subqueries in Filters

Summary Problems for You to Solve

PART IV: SUMMARIZING AND GROUPING DATA

Chapter 12: Simple Totals

Topics Covered in This Chapter Aggregate Functions

Counting Rows and Values with COUNT Computing a Total with SUM Calculating a Mean Value with AVG Finding the Largest Value with MAX Finding the Smallest Value with MIN Using More Than One Function

Using Aggregate Functions in Filters Sample Statements Summary Problems for You to Solve

Chapter 13: Grouping Data

Topics Covered in This Chapter Why Group Data? The GROUP BY Clause

Syntax Mixing Columns and Expressions Using GROUP BY in a Subquery in a WHERE Clause Simulating a SELECT DISTINCT Statement

“Some Restrictions Apply”

Column Restrictions Grouping on Expressions

Uses for GROUP BY Sample Statements Summary Problems for You to Solve

Chapter 14: Filtering Grouped Data

Topics Covered in This Chapter A New Meaning for “Focus Groups” Where You Filter Makes a Difference

Should You Filter in WHERE or in HAVING? Avoiding the HAVING COUNT Trap

Uses for HAVING Sample Statements Summary Problems for You to Solve

PART V: MODIFYING SETS OF DATA

Chapter 15: Updating Sets of Data

Topics Covered in This Chapter What Is an UPDATE? The UPDATE Statement

Using a Simple UPDATE Expression A Brief Aside: Transactions Updating Multiple Columns Using a Subquery to Filter Rows

Some Database Systems Allow a JOIN in the UPDATE Clause

Using a Subquery UPDATE Expression

Uses for UPDATE Sample Statements Summary Problems for You to Solve

Chapter 16: Inserting Sets of Data

Topics Covered in This Chapter What Is an INSERT? The INSERT Statement

Inserting Values Generating the Next Primary Key Value Inserting Data by Using SELECT

Uses for INSERT Sample Statements Summary Problems for You to Solve

Chapter 17: Deleting Sets of Data

Topics Covered in This Chapter What Is a DELETE? The DELETE Statement

Deleting All Rows Deleting Some Rows

Uses for DELETE Sample Statements Summary Problems for You to Solve

PART VI: INTRODUCTION TO SOLVING TOUGH PROBLEMS

Chapter 18: “NOT” and “AND” Problems

Topics Covered in This Chapter A Short Review of Sets

Sets with Multiple AND Criteria Sets with Multiple NOT Criteria Sets Including Some Criteria but Excluding Others

Finding Out the “Not” Case

Using OUTER JOIN Using NOT IN Using NOT EXISTS Using GROUP BY/HAVING

Finding Multiple Matches in the Same Table

Using INNER JOIN Using IN Using EXISTS Using GROUP BY/HAVING

Sample Statements Summary Problems for You to Solve

Chapter 19: Condition Testing

Topics Covered in This Chapter Conditional Expressions (CASE)

Why Use CASE? Syntax

Solving Problems with CASE

Solving Problems with Simple CASE Solving Problems with Searched CASE Using CASE in a WHERE Clause

Sample Statements Summary Problems for You to Solve

Chapter 20: Using Unlinked Data and “Driver” Tables

Topics Covered in This Chapter What Is Unlinked Data?

Deciding When to Use a CROSS JOIN

Solving Problems with Unlinked Data Solving Problems Using “Driver” Tables

Setting Up a Driver Table Using a Driver Table

Sample Statements

Examples Using Unlinked Tables Examples Using Driver Tables

Summary Problems for You to Solve

Chapter 21: Performing Complex Calculations on Groups

Topics in this Chapter Grouping in Sub-Groups Extending the GROUP BY Clause

Syntax

Getting Totals in a Hierarchy Using Rollup Calculating Totals on Combinations Using CUBE Creating a Union of Totals with GROUPING SETS Variations on Grouping Techniques Sample Statements

Examples using ROLLUP Examples using CUBE Examples using GROUPING SETS

Summary Problems for You to Solve

Chapter 22: Partitioning Data into Windows

Topics in this Chapter What You Can Do With a “Window” into Your Data

Syntax

Calculating a Row Number Ranking Data Splitting Data into Quintiles Using Windows with Aggregate Functions Sample Statements

Examples Using ROW_NUMBER Examples Using RANK, DENSE_RANK, and PERCENT_RANK Examples Using NTILE Examples Using Aggregate Functions

Summary Problems for You to Solve

In Closing

PART VII: APPENDICES

Appendix A: SQL Standard Diagrams Appendix B: Schema for the Sample Databases

Sales Orders Example Database Sales Orders Modify Database Entertainment Agency Example Database Entertainment Agency Modify Database School Scheduling Example Database School Scheduling Modify Database Bowling League Example Database Bowling League Modify Database Recipes Database “Driver” Tables

Appendix C: Date and Time Types, Operations, and Functions

IBM DB2 Microsoft Access Microsoft SQL Server MySQL Oracle PostgreSQL

Appendix D: Suggested Reading

Database Books Books on SQL

Index Code Snippets

← Prev
Back
Next →

← Prev
Back
Next →