Contents Listing

Preface



1. Pivot Tables

Using a Pivot Table



2. Sets

Introduction

The Students Example

Implementing Set Difference

Comparing Two Sets for Equality

Implementing Partial Intersection

Implementing Full Intersection

Classifying Subsets

Summarizing Classes of Sets

Aggregating Aggregates

Summarizing Aggregated Classes

Including Nonaggregated Columns

Finding the Top N Values in a Set

Reporting the Size of a Set's Complement

Finding the Complement of a Set

Finding the Complement of a Missing Set

Finding Complements of Sets with Different Universes

Comparing a Set with Its Universe

Dynamic Classification System



3. Data Structures

Types of Data Structures

Working Example

Finding Regions

Reporting Region Boundaries

Limiting Region Size

Ranking Regions by Size

Working with Sequences

Working with Runs

Cumulative Aggregates in Lists

Implementing a Stack

Implementing Queues

Implementing Priority Queues

Comparing Two Rows in an Array

Printing Matrices and Arrays

Transposing a Matrix

Calculating a Matrix Trace

Comparing Two Matrices for Size

Adding and Subtracting Matrices

Multiplying Matrices



4. Hierarchies in SQL

Types of Hierachies

Creating a Permission Hierarchy

Changing Individual Permissions

Adding New Individual Permissions

Centralizing Authorization Logic

Implementing General Hierarchies

Traversing Hierarchies Recursively

Manipulating Hierarchies Recursively

Aggregating Hierarchies

Preparing Multilevel Operations

Aggregating Hierarchies Revised



5. Temporal Data

Introduction

The Schedules Example

Enforcing Granularity Rules

Storing Out-of-Range Temporal Values

Deriving the First and Last Dates of the Month

Printing Calendars

Calculating Durations

Reporting Durations

Querying Periods

Querying Periods and Respecting Granularity

Finding Available Periods

Finding Common Available Periods

Excluding Recurrent Events

Excluding Nonrecurring Events

Finding Continuous Periods

Using Calendar Information with Periods

Using Calendar Information with Durations



6. Audit Logging

Audit Logs

The Warehouse Example

Row-Level Logging

Reporting Log Events

Generating Current Snapshots

Generating Time-Restricted Snapshots

Undoing Table Changes

Minimizing Audit-Log Space Consumption

Online Account Balancing

Activity-Level Logging

Partitioning Large Log Tables

Server Push



7. Importing and Transforming Data

Considerations When Importing Data

Working Examples

Importing External Data

Importing Data into a Live System

Importing with a Single Procedure

Hiding the Import Procedure

Folding Tables

Pivoting Tables

Joining Arrays with Tables

Joining Arrays with Master Tables

Joining Arrays with Multiple Master Records

Extracting Master Records from Tables

Generating Master Records Online

Working with Duplicates



8. Statistics in SQL

Statistical Concepts

The Light-Bulb Factory Example

Calculating a Mean

Calculating a Mode

Calculating a Median

Calculating Standard Deviation, Variance, and Standard Error

Building Confidence Intervals

Calculating Correlation

Exploring Patterns with Autocorrelation

Using a Simple Moving Average

Extending Moving Averages



Appendix: The T-Distribution Table



Index