Book Image

SQL for Data Analytics - Third Edition

By : Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston
Book Image

SQL for Data Analytics - Third Edition

By: Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston

Overview of this book

Every day, businesses operate around the clock, and a huge amount of data is generated at a rapid pace. This book helps you analyze this data and identify key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. SQL for Data Analytics, Third Edition is a great way to get started with data analysis, showing how to effectively sort and process information from raw data, even without any prior experience. You will begin by learning how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you will learn how to write SQL queries to aggregate, calculate, and combine SQL data from sources outside of your current dataset. You will also discover how to work with advanced data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you will be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of this book, you will be proficient in the efficient application of SQL techniques in everyday business scenarios and looking at data with the critical eye of analytics professional.
Table of Contents (11 chapters)
9
9. Using SQL to Uncover the Truth: A Case Study

Assembling Data

In Chapter 2, The Basics of SQL for Analytics, you learned how to perform operations with a single table. But what if you need data from two or more tables? In this section, you will assemble data in multiple tables using joins and unions.

Connecting Tables Using JOIN

Most of the time, the data you are interested in is spread across multiple tables. A simple SELECT statement over one table will not be enough to get you what you need. Fortunately, SQL has methods for bringing related tables together using the JOIN keyword.

To illustrate, look at two tables in the ZoomZoom database—dealerships and salespeople.

Figure 3.1: Structure of dealerships table

And the salespeople table looks like this:

Figure 3.2: Structure of salespeople table

In the salespeople table, you can observe that there is a column called dealership_id. This dealership_id column is a direct reference to the dealership_id column...