Data Analysis Using SQL and Excel

Data Analysis Using SQL and Excel

by Gordon S. Linoff
Released October 2007
Publisher(s): Wiley
ISBN: 9780470099513

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

  • One of the leading experts on business data mining shows managers how to leverage SQL and Excel to perform sophisticated types of business analysis without the expense of data mining tools and consultants

  • Explains how to use the relatively simple tools of SQL and Excel to extract useful business information from relational databases

  • Each chapter discusses why and when to perform a particular type of business analysis to obtain a useful business result, how to design and perform the analysis using SQL and Excel, and what the results look like in SQL and Excel

  • Presents hints, warnings, and technical asides about Excel, SQL, and data analysis/mining

Table of contents

  1. Copyright
  2. Dedication
  3. About the Author
  4. Foreword
  5. Acknowledgments
  6. Introduction
  7. 1. A Data Miner Looks at SQL
    1. Picturing the Structure of the Data
      1. What Is a Data Model?
      2. What Is a Table?
        1. Allowing NULL Values
        2. Column Types
      3. What Is an Entity‐Relationship Diagram?
      4. The Zip Code Tables
      5. Subscription Dataset
      6. Purchases Dataset
    2. Picturing Data Analysis Using Dataflows
      1. What Is a Dataflow?
      2. Dataflow Nodes (Operators)
        1. READ: Reading a Database Table
        2. OUTPUT: Outputting a Table (or Chart)
        3. SELECT: Selecting Various Columns in the Table
        4. FILTER: Filtering Rows Based on a Condition
        5. APPEND: Appending New Calculated Columns
        6. UNION: Combining Multiple Datasets into One
        7. AGGREGATE: Aggregating Values
        8. LOOKUP: Looking Up Values in One Table in Another
        9. CROSSJOIN: General Join of Two Tables
        10. JOIN: Join Two Tables Together Using a Key Column
        11. SORT: Ordering the Results of a Dataset
      3. Dataflows, SQL, and Relational Algebra
    3. SQL Queries
      1. What to Do, Not How to Do It
      2. A Basic SQL Query
      3. A Basic Summary SQL Query
      4. What it Means to Join Tables
        1. Cross‐Joins: The Most General Joins
        2. Lookup: A Useful Join
        3. Equijoins
        4. Nonequijoins
        5. Outer Joins
      5. Other Important Capabilities in SQL
        1. UNION ALL
        2. CASE
        3. IN
    4. Subqueries Are Our Friend
      1. Subqueries for Naming Variables
      2. Subqueries for Handling Summaries
      3. Subqueries and IN
        1. Rewriting the “IN” as a JOIN
        2. Correlated Subqueries
        3. The NOT IN Operator
      4. Subqueries for UNION ALL
    5. Lessons Learned
  8. 2. What's In a Table? Getting Started with Data Exploration
    1. What Is Data Exploration?
    2. Excel for Charting
      1. A Basic Chart: Column Charts
        1. Inserting the Data
        2. Creating the Column Chart
        3. Formatting the Column Chart
          1. Resize the Chart in the Chart Window
          2. Format the Legend
          3. Change the Fonts
          4. Change Chart Colors
          5. Adjust the Grid Lines
          6. Adjust the Horizontal Scale
      2. Useful Variations on the Column Chart
        1. A New Query
        2. Side‐by‐Side Columns
        3. Stacked Columns
        4. Stacked and Normalized Columns
        5. Number of Orders and Revenue
      3. Other Types of Charts
        1. Line Charts
        2. Area Charts
        3. X‐Y Charts (Scatter Plots)
    3. What Values Are in the Columns?
      1. Histograms
      2. Histograms of Counts
      3. Cumulative Histograms of Counts
      4. Histograms (Frequencies) for Numeric Values
        1. Ranges Based on the Number of Digits, Using Numeric Techniques
        2. Ranges Based on the Number of Digits, Using String Techniques
        3. More Refined Ranges: First Digit Plus Number of Digits
        4. Breaking Numerics into Equal‐Sized Groups
    4. More Values to Explore — Min, Max, and Mode
      1. Minimum and Maximum Values
      2. The Most Common Value (Mode)
        1. Calculating Mode Using Standard SQL
        2. Calculating Mode Using SQL Extensions
        3. Calculating Mode Using String Operations
    5. Exploring String Values
      1. Histogram of Length
      2. Strings Starting or Ending with Spaces
      3. Handling Upper‐ and Lowercase
      4. What Characters Are in a String?
    6. Exploring Values in Two Columns
      1. What Are Average Sales By State?
      2. How Often Are Products Repeated within a Single Order?
        1. Direct Counting Approach
        2. Comparison of Distinct Counts to Overall Counts
      3. Which State Has the Most American Express Users?
    7. From Summarizing One Column to Summarizing All Columns
      1. Good Summary for One Column
      2. Query to Get All Columns in a Table
      3. Using SQL to Generate Summary Code
    8. Lessons Learned
  9. 3. How Different Is Different?
    1. Basic Statistical Concepts
      1. The Null Hypothesis
      2. Confidence and Probability
      3. Normal Distribution
    2. How Different Are the Averages?
      1. The Approach
      2. Standard Deviation for Subset Averages
      3. Three Approaches
        1. Estimation Based on Two Samples
        2. Estimation Based on Difference
    3. Counting Possibilities
      1. How Many Men?
      2. How Many Californians?
      3. Null Hypothesis and Confidence
      4. How Many Customers Are Still Active?
        1. Given the Count, What Is the Probability?
        2. Given the Probability, What Is the Number of Stops?
      5. The Rate or the Number?
    4. Ratios, and Their Statistics
      1. Standard Error of a Proportion
      2. Confidence Interval on Proportions
      3. Difference of Proportions
      4. Conservative Lower Bounds
    5. Chi‐Square
      1. Expected Values
      2. Chi‐Square Calculation
      3. Chi‐Square Distribution
      4. Chi‐Square in SQL
      5. What States Have Unusual Affinities for Which Types of Products?
        1. Data Investigation
        2. SQL to Calculate Chi‐Square Values
        3. Affinity Results
    6. Lessons Learned
  10. 4. Where Is It All Happening? Location, Location, Location
    1. Latitude and Longitude
      1. Definition of Latitude and Longitude
      2. Degrees, Minutes, Seconds, and All That
      3. Distance between Two Locations
        1. Euclidian Method
        2. Accurate Method
        3. Finding All Zip Codes within a Given Distance
        4. Finding Nearest Zip Code in Excel
      4. Pictures with Zip Codes
        1. The Scatter Plot Map
        2. Who Uses Solar Power for Heating?
        3. Where Are the Customers?
    2. Census Demographics
      1. The Extremes: Richest and Poorest
        1. Median Income
        2. Proportion of Wealthy and Poor
        3. Income Similarity and Dissimilarity Using Chi‐Square
      2. Comparison of Zip Codes with and without Orders
        1. Zip Codes Not in Census File
        2. Profiles of Zip Codes with and without Orders
        3. Classifying and Comparing Zip Codes
    3. Geographic Hierarchies
      1. Wealthiest Zip Code in a State?
      2. Zip Code with the Most Orders in Each State
      3. Interesting Hierarchies in Geographic Data
        1. Counties
        2. Designated Marketing Areas (DMAs)
        3. Census Hierarchies
        4. Other Geographic Subdivisions
          1. Zip+2 and Zip+4
          2. Electoral Districts
          3. School Districts
          4. Catchment Areas
      4. Calculating County Wealth
        1. Identifying Counties
        2. Measuring Wealth
      5. Distribution of Values of Wealth
      6. Which Zip Code Is Wealthiest Relative to Its County?
      7. County with Highest Relative Order Penetration
    4. Mapping in Excel
      1. Why Create Maps?
      2. It Can't Be Done
      3. Mapping on the Web
      4. State Boundaries on Scatter Plots of Zip Codes
        1. Plotting State Boundaries
        2. Pictures of State Boundaries
    5. Lessons Learned
  11. 5. It's a Matter of Time
    1. Dates and Times in Databases
      1. Some Fundamentals of Dates and Times in Databases
        1. Extracting Components of Dates and Times
        2. Converting to Standard Formats
        3. Intervals (Durations)
        4. Time Zones
        5. Calendar Table
    2. Starting to Investigate Dates
      1. Verifying that Dates Have No Times
      2. Comparing Counts by Date
        1. Orderlines Shipped and Billed
        2. Customers Shipped and Billed
        3. Number of Different Bill and Ship Dates per Order
      3. Counts of Orders and Order Sizes
        1. Items as Measured by Number of Units
        2. Items as Measured by Distinct Products
        3. Size as Measured by Dollars
      4. Days of the Week
        1. Billing Date by Day of the Week
        2. Changes in Day of the Week by Year
        3. Comparison of Days of the Week for Two Dates
    3. How Long between Two Dates?
      1. Duration in Days
      2. Duration in Weeks
      3. Duration in Months
      4. How Many Mondays?
        1. A Business Problem about Days of the Week
        2. Outline of a Solution
        3. Solving It in SQL
        4. Using a Calendar Table Instead
    4. Year‐over‐Year Comparisons
      1. Comparisons by Day
        1. Adding a Moving Average Trend Line
        2. Comparisons by Week
      2. Comparisons by Month
        1. Month‐to‐Date Comparison
        2. Extrapolation by Days in Month
        3. Estimation Based on Day of Week
        4. Estimation Based on Previous Year
    5. Counting Active Customers by Day
      1. How Many Customers on a Given Day?
      2. How Many Customers Every Day?
      3. How Many Customers of Different Types?
      4. How Many Customers by Tenure Segment?
    6. Simple Chart Animation in Excel
      1. Order Date to Ship Date
      2. Order Date to Ship Date by Year
        1. Querying the Data
        2. Creating the One‐Year Excel Table
        3. Creating and Customizing the Chart
    7. Lessons Learned
  12. 6. How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
    1. Background on Survival Analysis
      1. Life Expectancy
      2. Medical Research
      3. Examples of Hazards
    2. The Hazard Calculation
      1. Data Investigation
        1. Stop Flag
        2. Tenure
      2. Hazard Probability
      3. Visualizing Customers: Time versus Tenure
      4. Censoring
    3. Survival and Retention
      1. Point Estimate for Survival
      2. Calculating Survival for All Tenures
      3. Calculating Survival in SQL
        1. Step 1. Create the Survival Table
        2. Step 2: Load POPT and STOPT
        3. Step 3: Calculate Cumulative Population
        4. Step 4: Calculate the Hazard
        5. Step 5: Calculate the Survival
        6. Step 6: Fix ENDTENURE and NUMDAYS in Last Row
        7. Generalizing the SQL
      4. A Simple Customer Retention Calculation
      5. Comparison between Retention and Survival
      6. Simple Example of Hazard and Survival
        1. Constant Hazard
        2. What Happens to a Mixture
        3. Constant Hazard Corresponding to Survival
    4. Comparing Different Groups of Customers
      1. Summarizing the Markets
      2. Stratifying by Market
      3. Survival Ratio
      4. Conditional Survival
    5. Comparing Survival over Time
      1. How Has a Particular Hazard Changed over Time?
      2. What Is Customer Survival by Year of Start?
      3. What Did Survival Look Like in the Past?
    6. Important Measures Derived from Survival
      1. Point Estimate of Survival
      2. Median Customer Tenure
      3. Average Customer Lifetime
      4. Confidence in the Hazards
    7. Using Survival for Customer Value Calculations
      1. Estimated Revenue
      2. Estimating Future Revenue for One Future Start
        1. SQL Day‐by‐Day Approach
        2. SQL Summary Approach
      3. Estimated Revenue for a Simple Group of Existing Customers
        1. Estimated Second Year Revenue for a Homogenous Group
        2. Pre‐calculating Yearly Revenue by Tenure
      4. Estimated Future Revenue for All Customers
    8. Lessons Learned
  13. 7. Factors Affecting Survival: The What and Why of Customer Tenure
    1. What Factors Are Important and When
      1. Explanation of the Approach
      2. Using Averages to Compare Numeric Variables
        1. The Answer
        2. Answering the Question in SQL
        3. Extension to Include Confidence Bounds
      3. Hazard Ratios
        1. Interpreting Hazard Ratios
        2. Calculating Hazard Ratios
        3. Why the Hazard Ratio
    2. Left Truncation
      1. Recognizing Left Truncation
      2. Effect of Left Truncation
      3. How to Fix Left Truncation, Conceptually
      4. Estimating Hazard Probability for One Tenure
      5. Estimating Hazard Probabilities for All Tenures
    3. Time Windowing
      1. A Business Problem
      2. Time Windows = Left Truncation + Right Censoring
        1. Calculating One Hazard Probability Using a Time Window
        2. All Hazard Probabilities for a Time Window
        3. Comparison of Hazards by Stops in Year
    4. Competing Risks
      1. Examples of Competing Risks
        1. I=Involuntary Churn
        2. V=Voluntary Churn
        3. M=Migration
        4. Other
      2. Competing Risk “Hazard Probability”
      3. Competing Risk “Survival”
      4. What Happens to Customers over Time
        1. Example
        2. A Cohort‐Based Approach
        3. The Survival Analysis Approach
    5. Before and After
      1. Three Scenarios
        1. A Billing Mistake
        2. A Loyalty Program
        3. Raising Prices
      2. Using Survival Forecasts
        1. Forecasting Identified Customers Who Stopped
        2. Estimating Excess Stops
      3. Before and After Comparison
      4. Cohort‐Based Approach
      5. Direct Estimation of Event Effect
        1. Approach to the Calculation
        2. Time‐Varying Covariate Survival Using SQL and Excel
    6. Lessons Learned
  14. 8. Customer Purchases and Other Repeated Events
    1. Identifying Customers
      1. Who Is the Customer?
        1. How Many?
        2. How Many Genders in a Household
        3. Investigating First Names
      2. Other Customer Information
        1. First and Last Names
        2. Addresses
        3. Other Identifying Information
      3. How Many New Customers Appear Each Year?
        1. Counting Customers
        2. Span of Time Making Purchases
        3. Average Time between Orders
        4. Purchase Intervals
    2. RFM Analysis
      1. The Dimensions
        1. Recency
        2. Frequency
        3. Monetary
      2. Calculating the RFM Cell
      3. Utility of RFM
        1. A Methodology for Marketing Experiments
        2. Customer Migration
        3. RFM Limits
    3. Which Households Are Increasing Purchase Amounts Over Time?
      1. Comparison of Earliest and Latest Values
        1. Calculating the Earliest and Latest Values
          1. “Find‐the‐Transaction” (Standard SQL Approach)
          2. Clever Aggregation
        2. Comparing the First and Last Values
          1. What Happens as Customer Span Increases
          2. What Happens as Customer Order Amounts Vary
      2. Comparison of First Year Values and Last Year Values
      3. Trend from the Best Fit Line
        1. Using the Slope
        2. Calculating the Slope
    4. Time to Next Event
      1. Idea behind the Calculation
      2. Calculating Next Purchase Date Using SQL
      3. From Next Purchase Date to Time‐to‐Event
      4. Stratifying Time‐to‐Event
    5. Lessons Learned
  15. 9. What's in a Shopping Cart? Market Basket Analysis and Association Rules
    1. Exploratory Market Basket Analysis
      1. Scatter Plot of Products
      2. Duplicate Products in Orders
      3. Histogram of Number of Units
      4. Products Associated with One‐Time Customers
      5. Products Associated with the Best Customers
      6. Changes in Price
    2. Combinations (Item Sets)
      1. Combinations of Two Products
        1. Number of Two‐Way Combinations
        2. Generating All Two‐Way Combinations
        3. Examples of Combinations
      2. Variations on Combinations
        1. Combinations of Product Groups
        2. Multi‐Way Combinations
      3. Households Not Orders
        1. Combinations within a Household
        2. Investigating Products within Households but Not within Orders
        3. Multiple Purchases of the Same Product
    3. The Simplest Association Rules
      1. Associations and Rules
      2. Zero‐Way Association Rules
      3. What Is the Distribution of Probabilities?
      4. What Do Zero‐Way Associations Tell Us?
    4. One‐Way Association Rules
      1. Example of One‐Way Association Rules
      2. Generating All One‐Way Rules
      3. One‐Way Rules with Evaluation Information
      4. One‐Way Rules on Product Groups
        1. Calculating Product Group Rules Using an Intermediate Table
        2. Calculating Product Group Rules Using Window Functions
    5. Two‐Way Associations
      1. Calculating Two‐Way Associations
      2. Using Chi‐Square to Find the Best Rules
        1. Applying Chi‐Square to Rules
        2. Applying Chi‐Square to Rules in SQL
        3. Comparing Chi‐Square Rules to Lift
        4. Chi‐Square for Negative Rules
      3. Heterogeneous Associations
        1. Rules of the Form “State Plus Product”
        2. Rules Mixing Different Types of Products
    6. Extending Association Rules
      1. Multi‐Way Associations
      2. Rules Using Attributes of Products
      3. Rules with Different Left‐ and Right‐Hand Sides
      4. Before and After: Sequential Associations
    7. Lessons Learned
  16. 10. Data Mining Models in SQL
    1. Introduction to Directed Data Mining
      1. Directed Models
      2. The Data in Modeling
        1. Model Set
        2. Score Set
        3. Prediction Model Sets versus Profiling Model Sets
      3. Examples of Modeling Tasks
        1. Similarity Models
        2. Yes‐or‐No Models (Binary Response Classification)
        3. Yes‐or‐No Models with Propensity Scores
        4. Multiple Categories
        5. Estimating Numeric Values
      4. Model Evaluation
    2. Look‐Alike Models
      1. What Is the Model?
      2. What Is the Best Zip Code?
      3. A Basic Look‐Alike Model
      4. Look‐Alike Using Z‐Scores
      5. Example of Nearest Neighbor Model
    3. Lookup Model for Most Popular Product
      1. Most Popular Product
      2. Calculating Most Popular Product Group
      3. Evaluating the Lookup Model
      4. Using a Profiling Lookup Model for Prediction
      5. Using Binary Classification Instead
    4. Lookup Model for Order Size
      1. Most Basic Example: No Dimensions
      2. Adding One Dimension
      3. Adding More Dimensions
      4. Examining Nonstationarity
      5. Evaluating the Model Using an Average Value Chart
    5. Lookup Model for Probability of Response
      1. The Overall Probability as a Model
      2. Exploring Different Dimensions
      3. How Accurate Are the Models?
      4. Adding More Dimensions
    6. Naïve Bayesian Models (Evidence Models)
      1. Some Ideas in Probability
        1. Probabilities
        2. Odds
        3. Likelihood
      2. Calculating the Naïve Bayesian Model
        1. An Intriguing Observation
        2. Bayesian Model of One Variable
        3. Bayesian Model of One Variable in SQL
        4. The “Naïve” Generalization
      3. Naïve Bayesian Model: Scoring and Lift
        1. Scoring with More Attributes
        2. Creating a Cumulative Gains Chart
      4. Comparison of Naïve Bayesian and Lookup Models
    7. Lessons Learned
  17. 11. The Best‐Fit Line: Linear Regression Models
    1. The Best‐Fit Line
      1. Tenure and Amount Paid
      2. Properties of the Best‐fit Line
        1. What Does Best‐Fit Mean?
        2. Formula for Line
        3. Expected Value
        4. Error (Residuals)
        5. Preserving the Averages
        6. Inverse Model
      3. Beware of the Data
      4. Trend Lines in Charts
        1. Best‐fit Line in Scatter Plots
        2. Logarithmic, Power, and Exponential Trend Curves
        3. Polynomial Trend Curves
        4. Moving Average
      5. Best‐fit Using LINEST() Function
        1. Returning Values in Multiple Cells
        2. Calculating Expected Values
        3. LINEST() for Logarithmic, Exponential, and Power Curves
    2. Measuring Goodness of Fit Using R2
      1. The R2 Value
      2. Limitations of R2
      3. What R2 Really Means
    3. Direct Calculation of Best‐Fit Line Coefficients
      1. Doing the Calculation
      2. Calculating the Best‐Fit Line in SQL
      3. Price Elasticity
        1. Price Frequency
        2. Price Frequency for $20 Books
        3. Price Elasticity Model in SQL
        4. Price Elasticity Average Value Chart
    4. Weighted Linear Regression
      1. Customer Stops during the First Year
      2. Weighted Best Fit
      3. Weighted Best‐Fit Line in a Chart
      4. Weighted Best‐Fit in SQL
      5. Weighted Best‐Fit Using Solver
        1. The Weighted Best‐Fit Line
        2. Solver Is Better Than Guessing
    5. More Than One Input Variable
      1. Multiple Regression in Excel
        1. Getting the Data
        2. Investigating Each Variable Separately
      2. Building a Model with Three Input Variables
      3. Using Solver for Multiple Regression
      4. Choosing Input Variables One‐By‐One
      5. Multiple Regression in SQL
    6. Lessons Learned
  18. 12. Building Customer Signatures for Further Analysis
    1. What Is a Customer Signature?
      1. What Is a Customer?
      2. Sources of Data for the Customer Signature
        1. Current Customer Snapshot
        2. Initial Customer Information
        3. Self‐Reported Information
        4. External Data (Demographic and So On)
        5. About Their Neighbors
        6. Transaction Summaries
      3. Using Customer Signatures
        1. Predictive and Profile Modeling
        2. Ad Hoc Analysis
        3. Repository of Customer‐Centric Business Metrics
    2. Designing Customer Signatures
      1. Column Roles
        1. Identification Columns
        2. Input Columns
        3. Target Columns
        4. Foreign Key Columns
        5. Cutoff Date
      2. Profiling versus Prediction
      3. Time Frames
        1. Naming of Columns
        2. Eliminating Seasonality
        3. Adding Seasonality Back In
        4. Multiple Time Frames
    3. Operations to Build a Customer Signature
      1. Driving Table
        1. Using an Existing Table as the Driving Table
        2. Derived Table as the Driving Table
      2. Looking Up Data
        1. Fixed Lookup Tables
        2. Customer Dimension Lookup Tables
      3. Initial Transaction
        1. Without Window Functions
        2. With Window Functions
      4. Pivoting
        1. Payment Type Pivot
        2. Channel Pivot
        3. Year Pivot
        4. Order Line Information Pivot
      5. Summarizing
        1. Basic Summaries
        2. More Complex Summaries
    4. Extracting Features
      1. Geographic Location Information
      2. Date Time Columns
      3. Patterns in Strings
        1. Email Addresses
        2. Addresses
        3. Product Descriptions
        4. Credit Card Numbers
    5. Summarizing Customer Behaviors
      1. Calculating Slope for Time Series
        1. Calculating Slope from Pivoted Time Series
        2. Calculating Slope for a Regular Time Series
        3. Calculating Slope for an Irregular Time Series
      2. Weekend Shoppers
      3. Declining Usage Behavior
    6. Lessons Learned
  19. 1. Appendix: Equivalent Constructs Among Databases
    1. String Functions
      1. Searching for Position of One String within Another
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      2. String Concatenation
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      3. String Length Function
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      4. Substring Function
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      5. Replace One Substring with Another
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      6. Remove Leading and Trailing Blanks
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      7. RIGHT Function
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      8. LEFT Function
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      9. ASCII Function
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
    2. Date Time Functions
      1. Date Constant
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      2. Current Date and Time
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      3. Convert to YYYYMMDD String
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      4. Year, Month, and Day of Month
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      5. Day of Week (Integer and String)
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      6. Adding (or Subtracting) Days from a Date
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      7. Adding (or Subtracting) Months from a Date
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      8. Difference between Two Dates in Days
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      9. Difference between Two Dates in Months
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      10. Extracting Date from Date Time
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
    3. Mathematical Functions
      1. Remainder/Modulo
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      2. Power
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc SQL
      3. Floor
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      4. “Random” Numbers
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      5. Left Padding an Integer with Zeros
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      6. Conversion from Number to String
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
    4. Other Functions and Features
      1. Least and Greatest
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      2. Return Result with One Row
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      3. Return a Handful of Rows
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      4. Get List of Columns in a Table
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      5. ORDER BY in Subqueries
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      6. Window Functions
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql
      7. Average of Integers
        1. IBM
        2. Microsoft
        3. mysql
        4. Oracle
        5. SAS proc sql

Product information

  • Title: Data Analysis Using SQL and Excel
  • Author(s): Gordon S. Linoff
  • Release date: October 2007
  • Publisher(s): Wiley
  • ISBN: 9780470099513