Book Image

SQL for Data Analytics

By : Upom Malik, Matt Goldwasser, Benjamin Johnston
3 (1)
Book Image

SQL for Data Analytics

3 (1)
By: Upom Malik, Matt Goldwasser, Benjamin Johnston

Overview of this book

Understanding and finding patterns in data has become one of the most important ways to improve business decisions. If you know the basics of SQL, but don't know how to use it to gain the most effective business insights from data, this book is for you. SQL for Data Analytics helps you build the skills to move beyond basic SQL and instead learn to spot patterns and explain the logic hidden in data. You'll discover how to explore and understand data by identifying trends and unlocking deeper insights. You'll also gain experience working with different types of data in SQL, including time-series, geospatial, and text data. Finally, you'll learn how to increase your productivity with the help of profiling and automation. By the end of this book, you'll be able to use SQL in everyday business scenarios efficiently and look at data with the critical eye of an analytics professional. Please note: if you are having difficulty loading the sample datasets, there are new instructions uploaded to the GitHub repository. The link to the GitHub repository can be found in the book's preface.

Related Content you might be interested in

Current Title:

Small book image
SQL for Data Analytics
Upom Malik | Matt Goldwasser | Benjamin Johnston
Aug, 2019 | 386 pages
Small book image
Data Wrangling with SQL
Raghav Kandarpa | Shivangi Saxena
Jul, 2023 | 350 pages
Small book image
The SQL Workshop
Frank Solomon | Awni Al Saqqa | Prashanth Jayaram
Dec, 2019 | 288 pages
Table of Contents (11 chapters)
Preface
Preface
About the Book
Free Chapter
1
1. Understanding and Describing Data
1. Understanding and Describing Data
Introduction
The World of Data
Methods of Descriptive Statistics
Statistical Significance Testing
Summary
2
2. The Basics of SQL for Analytics
2. The Basics of SQL for Analytics
Introduction
Relational Databases and SQL
Basic Data Types of SQL
Reading Tables: The SELECT Query
Creating Tables
Updating Tables
Deleting Data and Tables
SQL and Analytics
Summary
3
3. SQL for Data Preparation
3. SQL for Data Preparation
Introduction
Assembling Data
Transforming Data
Summary
4
4. Aggregate Functions for Data Analysis
4. Aggregate Functions for Data Analysis
Introduction
Aggregate Functions
Aggregate Functions with GROUP BY
The HAVING Clause
Using Aggregates to Clean Data and Examine Data Quality
Summary
5
5. Window Functions for Data Analysis
5. Window Functions for Data Analysis
Introduction
Window Functions
Statistics with Window Functions
Summary
6
6. Importing and Exporting Data
6. Importing and Exporting Data
Introduction
The COPY Command
Using R with Our Database
Using Python with Our Database
Best Practices for Importing and Exporting Data
Summary
7
7. Analytics Using Complex Data Types
7. Analytics Using Complex Data Types
Introduction
Date and Time Data Types for Analysis
Performing Geospatial Analysis in Postgres
Using Array Data Types in Postgres
Using JSON Data Types in Postgres
Text Analytics Using Postgres
Summary
8
8. Performant SQL
8. Performant SQL
Introduction
Database Scanning Methods
Performant Joins
Functions and Triggers
Summary
9
9. Using SQL to Uncover the Truth – a Case Study
9. Using SQL to Uncover the Truth – a Case Study
Introduction
Case Study
Summary
Appendix
Appendix
1. Understanding and Describing Data
2. The Basics of SQL for Analytics
3. SQL for Data Preparation
4. Aggregate Functions for Data Analysis
5. Window Functions for Data Analysis
6. Importing and Exporting Data
7. Analytics Using Complex Data Types
8. Performant SQL
9. Using SQL to Uncover the Truth – a Case Study

Reading Tables: The SELECT Query

The most common operation in a database is reading data from a database. This is almost exclusively done through the use of the SELECT keyword.

Basic Anatomy and Working of a SELECT Query

Generally speaking, a query can be broken down into five parts:

  • Operation: The first part of a query describes what is going to be done. In this case, this is the word SELECT, followed by the names of columns combined with functions.
  • Data: The next part of the query is the data, which is the FROM keyword followed by one or more tables connected together with reserved keywords indicating what data should be scanned for filtering, selection, and calculation.
  • Conditional: A part of the query that filters the data to only rows that meet a condition usually indicated with WHERE.
  • Grouping: A special clause that takes the rows of a data source, assembles them together using a key created by a GROUP BY clause, and then calculates a value using...
Previous Section
End of Section 5
Next Section