SQL Queries for Mere Mortals Course

Introduction

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

What Is Relational?

• Types of Databases
• A Brief History of the Relational Model
• Anatomy of a Relational Database
• What’s in It for You?
• Summary

Ensuring Your Database Structure Is Sound

• Why Is this Lesson Here?
• Why Worry about Sound Structures?
• Fine-Tuning Columns
• Fine-Tuning Tables
• Establishing Solid Relationships
• Is That All?
• Summary

A Concise History of SQL

• The Origins of SQL
• Early Vendor Implementations
• “… And Then There Was a Standard”
• Evolution of the ANSI/ISO Standard
• Commercial Implementations
• What the Future Holds
• Why Should You Learn SQL?
• Which Version of SQL Does this Course Cover?
• Summary

Creating a Simple Query

• Introducing SELECT
• The SELECT Statement
• A Quick Aside: Data versus Information
• Translating Your Request into SQL
• Eliminating Duplicate Rows
• Sorting Information
• Saving Your Work
• Sample Statements
• Summary
• Problems for You to Solve

Getting More Than Simple Columns

• What Is an Expression?
• What Type of Data Are You Trying to Express?
• Changing Data Types: The CAST Function
• Specifying Explicit Values
• Types of Expressions
• Using Expressions in a SELECT Clause
• That “Nothing” Value: Null
• Sample Statements
• Summary
• Problems for You to Solve

Filtering Your Data

• Refining What You See Using WHERE
• Defining Search Conditions
• Using Multiple Conditions
• Nulls Revisited: A Cautionary Note
• Expressing Conditions in Different Ways
• Sample Statements
• Summary
• Problems for You to Solve

Thinking in Sets

• What Is a Set, Anyway?
• Operations on Sets
• Intersection
• Difference
• Union
• SQL Set Operations
• Summary

INNER JOINs

• What Is a JOIN?
• The INNER JOIN
• Uses for INNER JOINs
• Sample Statements
• Summary
• Problems for You to Solve

OUTER JOINs

• What Is an OUTER JOIN?
• The LEFT/RIGHT OUTER JOIN
• The FULL OUTER JOIN
• Uses for OUTER JOINs
• Sample Statements
• Summary
• Problems for You to Solve

UNIONs

• What Is a UNION?
• Writing Requests with UNION
• Uses for UNION
• Sample Statements
• Summary
• Problems for You to Solve

Subqueries

• What Is a Subquery?
• Subqueries as Column Expressions
• Subqueries as Filters
• Uses for Subqueries
• Sample Statements
• Summary
• Problems for You to Solve

Simple Totals

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

Grouping Data

• Why Group Data?
• The GROUP BY Clause
• “Some Restrictions Apply”
• Uses for GROUP BY
• Sample Statements
• Summary
• Problems for You to Solve

Filtering Grouped Data

• A New Meaning for “Focus Groups”
• Where You Filter Makes a Difference
• Uses for HAVING
• Sample Statements
• Summary
• Problems for You to Solve

Updating Sets of Data

• What Is an UPDATE?
• The UPDATE Statement
• Some Database Systems Allow a JOIN in the UPDATE Clause
• Uses for UPDATE
• Sample Statements
• Summary
• Problems for You to Solve

Inserting Sets of Data

• What Is an INSERT?
• The INSERT Statement
• Uses for INSERT
• Sample Statements
• Summary
• Problems for You to Solve

Deleting Sets of Data

• What Is a DELETE?
• The DELETE Statement
• Uses for DELETE
• Sample Statements
• Summary
• Problems for You to Solve

“NOT” and “AND” Problems

• A Short Review of Sets
• Finding Out the “Not” Case
• Finding Multiple Matches in the Same Table
• Sample Statements
• Summary
• Problems for You to Solve

Condition Testing

• Conditional Expressions (CASE)
• Solving Problems with CASE
• Sample Statements
• Summary
• Problems for You to Solve

Using Unlinked Data and “Driver” Tables

• What Is Unlinked Data?
• Solving Problems with Unlinked Data
• Solving Problems Using “Driver” Tables
• Sample Statements
• Summary
• Problems for You to Solve

Performing Complex Calculations on Groups

• Grouping in Sub-Groups
• Extending the GROUP BY Clause
• 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
• Summary
• Problems for You to Solve

Partitioning Data into Windows

• What You Can Do With a “Window” into Your Data
• Calculating a Row Number
• Ranking Data
• Splitting Data into Quintiles
• Using Windows with Aggregate Functions
• Sample Statements
• Summary
• Problems for You to Solve

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