SQL for Data Science: Data Cleaning, Wrangling and Analytics with Relational Databases

 Cover

Table of Contents

Change History

Beta 9—June 9,....2021

Beta 8—April 27,....2021

Beta 7—March 11,....2021

Beta 6—August 11,....2020

Beta 5—June 5,....2020

Beta 4—March 23,....2020

Beta 3—February 9,....2020

Beta 2—December 4,....2019

Beta 1—October 30,....2019

Acknowledgments

So You Want to Write Some Client-Side Code

Basic Assumptions

The Tools We’ll Use

How This Book Is Organized

Let’s Build an App

The Sample Code

Part I—Getting Started

1. Getting Started with Client-Side Rails

Managing State and Front-End Development

Configuring Webpacker

Using Webpacker

What’s Next

2. Hotwire and Turbo

The Hotwire Way

Installing Turbo

What Is Turbo Drive?

Adding Interactivity with Turbo Frames

Navigating Outside a Turbo Frame

Extending Our Page with Turbo Streams

Turbo Frames vs. Turbo Streams

Lazy Loading a Turbo Frame

What’s Next

3. Stimulus

What Is Stimulus?

Installing Stimulus

Adding Our First Controller

Creating an Action

Adding a Target

Using Values

Automating Value Changes

Stimulus Has Class

Going Generic

Stimulus Quick Reference

What’s Next

4. React

What Is React?

Installing React

Adding Our First Component

Composing Components

Connecting to the Page

Interactivity, State, and Hooks

Sharing State

What’s Next

5. Cascading Style Sheets

Building CSS in webpack

Adding CSS and Assets to webpack

Animating CSS

Adding CSS Transitions

Animating Turbo Streams with Animate.css

Using CSS and React Components

What’s Next

Part II—Going Deeper

6. TypeScript

Using TypeScript

Understanding Basic TypeScript Types

Static vs. Dynamic Typing

Adding Type Annotations to Variables

Adding Type Annotations to Functions

Adding Type Annotations to Classes

Defining Interfaces

Type Checking Classes and Interfaces

Getting Type Knowledge to TypeScript

What’s Next

7. webpack

Understanding Why webpack Exists

Managing Dependencies with Yarn

Understanding webpack Configuration

What’s Next

8. Webpacker

Webpacker Basics

Writing Code Using Webpacker

Integrating Webpacker with Frameworks

Running webpack

Deploying Webpacker in Production

Customizing Webpacker

What’s Next

Part III—Managing Servers and State

9. Talking to the Server

Using Stimulus to Manage Forms

Stimulus and Ajax

Using Data in Stimulus

Acquiring Data in React with useState

What’s Next

10. Immediate Communication with ActionCable

Installing ActionCable

Turbo Streams and ActionCable

Stimulus and ActionCable

React and ActionCable

What’s Next

11. Managing State in Stimulus Code

Using Data Values for Logic

Observing and Responding to DOM Changes

Rendering CSS with Data Attributes

What’s Next

12. Managing State in React

Using Reducers

Using Context to Share State

Adding Asynchronous Events to Contexts

What’s Next

13. Using Redux to Manage State

Installing and Using Redux

Adding Asynchronous Actions to Redux

What’s Next

Part IV—Validating Your Code

14. Validating Code with Advanced TypeScript

Creating Union Types

Specifying Types with Literal Types

Using Enums and Literal Types

Building Mapped Types and Utility Types

TypeScript Configuration Options

Dealing with Strictness

What’s Next

15. Testing with Cypress

Why Cypress?

Installing Cypress

Configuring Cypress and Rails

Writing Our First Test

Understanding How Cypress Works

What’s Next

16. More Testing and Troubleshooting

Writing More Cypress Tests

Testing the Schedule Filter

Cypress and React

Cypress Utilities and API

Troubleshooting

What’s Next

A1. Framework Swap

The All-Hotwire App

The All-React App

Comparison

Index

This textbook explains SQL within the contextof data science and introduces the different parts of SQL as they are needed for the tasks usually carried out during data analysis. Using the framework of the data life cycle, it focuses on the steps that are very often given the short shift in traditional textbooks, like data loading, cleaning and pre-processing.

The book is organized as follows. 

• Chapter 1 describes the data life cycle, i.e. the sequence of stages from data acquisition to archiving, that data goes through as it is prepared and then actually analyzed, together with the different activities that take place at each stage.
• Chapter 2 gets into databases proper, explaining how relational databases organize data. Non-traditional data, like XML and text, are also covered.
• Chapter 3 introduces SQL queries, but unlike traditional textbooks, queries and their parts are described around typical data analysis tasks like data exploration, cleaning and transformation.
• Chapter 4 introduces some basic techniques for data analysis and shows how SQL can be used for some simple analyses without too much complication.
• Chapter 5 introduces additional SQL constructs that are important in a variety of situations and thus completes the coverage of SQL queries. Lastly, chapter 6 briefly explains how to use SQL from within R and from within Python programs. It focuses on how these languages can interact with a database, and how what has been learned about SQL can be leveraged to make life easier when using R or Python. All chapters contain a lot of examples and exercises on the way, and readers are encouraged to install the two open-source database systems (MySQL and Postgres) that are used throughout the book in order to practice and work on the exercises, because simply reading the book is much less useful than actually usingit.

This book is for anyone interested in data science and/or databases. It just demands a bit of computer fluency, butno specific background on databases or data analysis. All concepts are introduced intuitively and with a minimum of specialized jargon. After going through this book, readers should be able to profitably learn more about data mining, machine learning, and database management from more advanced textbooks and courses.