Practical SQL a beginner's guide to storytelling with data

Title Page......Page 1
Copyright Page......Page 2
About the Author......Page 4
About the Technical Reviewer......Page 5
Brief Contents......Page 6
Contents in Detail......Page 8
Foreword by Sarah Frostenson......Page 19
Acknowledgments......Page 22
 What Is SQL?......Page 23
 Why Use SQL?......Page 24
 About This Book......Page 25
 Using PostgreSQL......Page 28
 Wrapping Up......Page 37
1. Creating Your First Database and Table......Page 38
 Creating a Database......Page 40
 Creating a Table......Page 43
 Inserting Rows into a Table......Page 47
 Formatting SQL for Readability......Page 49
 Try It Yourself......Page 50
 Basic SELECT Syntax......Page 52
 Sorting Data with ORDER BY......Page 56
 Filtering Rows with WHERE......Page 59
 Putting It All Together......Page 63
 Wrapping Up......Page 64
 Try It Yourself......Page 65
3. Understanding Data Types......Page 66
 Characters......Page 67
 Numbers......Page 69
 Dates and Times......Page 76
 Using the interval Data Type in Calculations......Page 79
 Transforming Values from One Type to Another with CAST......Page 80
 CAST Shortcut Notation......Page 81
 Try It Yourself......Page 82
4. Importing and Exporting Data......Page 84
 Working with Delimited Text Files......Page 85
 Using COPY to Import Data......Page 87
 Importing Census Data Describing Counties......Page 89
 Importing a Subset of Columns with COPY......Page 96
 Adding a Default Value to a Column During Import......Page 97
 Using COPY to Export Data......Page 99
 Importing and Exporting Through pgAdmin......Page 100
 Wrapping Up......Page 101
 Try It Yourself......Page 102
 Math Operators......Page 103
 Doing Math Across Census Table Columns......Page 108
 Aggregate Functions for Averages and Sums......Page 114
 Finding the Median......Page 115
 Wrapping Up......Page 121
 Try It Yourself......Page 122
 Linking Tables Using JOIN......Page 123
 Relating Tables with Key Columns......Page 124
 Querying Multiple Tables Using JOIN......Page 127
 JOIN Types......Page 128
 Using NULL to Find Rows with Missing Values......Page 134
 Three Types of Table Relationships......Page 135
 Selecting Specific Columns in a Join......Page 136
 Joining Multiple Tables......Page 138
 Performing Math on Joined Table Columns......Page 140
 Try It Yourself......Page 143
 Naming Tables, Columns, and Other Identifiers......Page 144
 Controlling Column Values with Constraints......Page 148
 Speeding Up Queries with Indexes......Page 162
 Wrapping Up......Page 166
 Try It Yourself......Page 167
8. Extracting Information by Grouping and Summarizing......Page 169
 Creating the Library Survey Tables......Page 170
 Exploring the Library Data Using Aggregate Functions......Page 173
 Wrapping Up......Page 186
 Try It Yourself......Page 187
 Importing Data on Meat, Poultry, and Egg Producers......Page 189
 Interviewing the Data Set......Page 191
 Modifying Tables, Columns, and Data......Page 197
 Deleting Unnecessary Data......Page 210
 Using Transaction Blocks to Save or Revert Changes......Page 212
 Improving Performance When Updating Large Tables......Page 215
 Wrapping Up......Page 216
 Try It Yourself......Page 217
 Creating a Census Stats Table......Page 218
 Creating Rankings with SQL......Page 229
 Calculating Rates for Meaningful Comparisons......Page 232
 Try It Yourself......Page 235
 Data Types and Functions for Dates and Times......Page 237
 Manipulating Dates and Times......Page 238
 Working with Time Zones......Page 243
 Calculations with Dates and Times......Page 248
 Try It Yourself......Page 259
 Using Subqueries......Page 260
 Common Table Expressions......Page 270
 Cross Tabulations......Page 273
 Reclassifying Values with CASE......Page 279
 Using CASE in a Common Table Expression......Page 281
 Try It Yourself......Page 282
 Formatting Text Using String Functions......Page 284
 Matching Text Patterns with Regular Expressions......Page 287
 Full Text Search in PostgreSQL......Page 308
 Try It Yourself......Page 318
14. Analyzing Spatial Data with PostGIS......Page 320
 Installing PostGIS and Creating a Spatial Database......Page 321
 Two-Dimensional Geometries......Page 322
 PostGIS Data Types......Page 327
 Creating Spatial Objects with PostGIS Functions......Page 328
 Analyzing Farmers’ Markets Data......Page 332
 Working with Census Shapefiles......Page 339
 Performing Spatial Joins......Page 346
 Wrapping Up......Page 349
 Try It Yourself......Page 350
15. Saving Time with Views, Functions, and Triggers......Page 351
 Using Views to Simplify Queries......Page 352
 Programming Your Own Functions......Page 360
 Automating Database Actions with Triggers......Page 369
 Wrapping Up......Page 376
 Try It Yourself......Page 377
16. Using PostgreSQL from the Command Line......Page 378
 Setting Up the Command Line for psql......Page 379
 Working with psql......Page 388
 Additional Command Line Utilities to Expedite Tasks......Page 401
 Wrapping Up......Page 403
 Try It Yourself......Page 404
 Recovering Unused Space with VACUUM......Page 405
 Changing Server Settings......Page 412
 Backing Up and Restoring Your Database......Page 415
 Wrapping Up......Page 417
 Try It Yourself......Page 418
 Start with a Question......Page 419
 Document Your Process......Page 420
 No Data? Build Your Own Database......Page 421
 Assess the Data’s Origins......Page 422
 Consult the Data’s Owner......Page 423
 Identify Key Indicators and Trends over Time......Page 424
 Ask Why......Page 426
 Communicate Your Findings......Page 427
 Try It Yourself......Page 428
 PostgreSQL Development Environments......Page 430
 PostgreSQL Utilities, Tools, and Extensions......Page 431
 PostgreSQL News......Page 432
 Documentation......Page 433
Index......Page 434