SQL for Data Scientists

Introduction xix Chapter 1 Data Sources 1 Data Sources 1 Tools for Connecting to Data Sources and Editing SQL 2 Relational Databases 3 Dimensional Data Warehouses 7 Asking Questions About the Data Source 9 Introduction to the Farmer's Market Database 11 A Note on Machine Learning Dataset Terminology 12 Exercises 13 Chapter 2 The SELECT Statement 15 The SELECT Statement 15 The Fundamental Syntax Structure of a SELECT Query 16 Selecting Columns and Limiting the Number of Rows Returned 16 The ORDER BY Clause: Sorting Results 18 Introduction to Simple Inline Calculations 20 More Inline Calculation Examples: Rounding 22 More Inline Calculation Examples: Concatenating Strings 24 Evaluating Query Output 26 SELECT Statement Summary 29 Exercises Using the Included Database 30 Chapter 3 The WHERE Clause 31 The WHERE Clause 31 Filtering SELECT Statement Results 32 Filtering on Multiple Conditions 34 Multi-Column Conditional Filtering 40 More Ways to Filter 41 BETWEEN 41 IN 42 LIKE 43 IS NULL 44 A Warning About Null Comparisons 44 Filtering Using Subqueries 46 Exercises Using the Included Database 47 Chapter 4 CASE Statements 49 CASE Statement Syntax 50 Creating Binary Flags Using CASE 52 Grouping or Binning Continuous Values Using CASE 53 Categorical Encoding Using CASE 56 CASE Statement Summary 59 Exercises Using the Included Database 60 Chapter 5 SQL JOINs 61 Database Relationships and SQL JOINs 61 A Common Pitfall when Filtering Joined Data 71 JOINs with More than Two Tables 74 Exercises Using the Included Database 76 Chapter 6 Aggregating Results for Analysis 79 GROUP BY Syntax 79 Displaying Group Summaries 80 Performing Calculations Inside Aggregate Functions 84 MIN and MAX 88 COUNT and COUNT DISTINCT 90 Average 91 Filtering with HAVING 93 CASE Statements Inside Aggregate Functions 94 Exercises Using the Included Database 96 Chapter 7 Window Functions and Subqueries 97 ROW NUMBER 98 RANK and DENSE RANK 101 NTILE 102 Aggregate Window Functions 103 LAG and LEAD 108

RENÉE M. P. TEATE is the Director of Data Science at HelioCampus, a higher ed tech startup based in the Washington, DC area. She prepares datasets with SQL, develops predictive models with Python, and designs interactive dashboards in Tableau for university decision-makers. She created the "Becoming a Data Scientist" podcast, helped build the data science learning community on Twitter, and is a sought-after speaker at industry conferences.