Data Wrangling with SQL: A hands-on guide to manipulating, wrangling, and engineering data using SQL

Data Wrangling with SQL
Acknowledgements
Dedication
Contributors
About the authors
About the reviewers
Preface
   Who this book is for
   What this book covers
   To get the most out of this book
   Download the example code files
   Conventions used
   Get in touch
   Share Your Thoughts
   Download a free PDF copy of this book
Part 1:Data Wrangling Introduction
1
Database Introduction
   Getting started
      Establishing the foundation
      Efficient data organization
      Data integrity and consistency
   Technical requirements
   Decoding database structures – relational and non-relational
      What is a database?
      Types of databases
   Tables and relationships
      The SQL CREATE DATABASE statement
      The SQL CREATE TABLE statement
      SQL DROP TABLE versus TRUNCATE TABLE
      SQL ALTER TABLE
      SQL constraints
      SQL keys
      Database relationships
   Comparing database normalization and denormalization
      Normalization
      Types of normalization
      Denormalization
      When to apply denormalization
      Disadvantages of denormalization
   Summary
   Practical exercises
      Practical exercise 1
      Practical exercise 2
      Practical exercise 3
      Practical exercise 4
2
Data Profiling and Preparation before Data Wrangling
   What is data wrangling?
      Data wrangling steps
      The importance of data wrangling
      Benefits of data wrangling
      Data wrangling use cases
      Business use cases
   Data capture
      How does data get captured?
      Data-capturing techniques
      Web scraping
      Structured versus unstructured data
   Paid-for versus free data-wrangling tools
   Data profiling
      Data profiling types
      Data profiling techniques
   Practical exercise
      Step 1 – Discovery
      Step 2 – Structuring
      Step 3 – Cleaning
      Step 4 – Enriching
      Step 5 – Validating
      Step 6 – Publishing
   Summary
Part 2:Data Wrangling Techniques Using SQL
3
Data Wrangling on String Data Types
   SQL data types
      Numeric data types
      Date and time data types
      String data type
   SQL string functions
      RIGHT()
      LEFT()
      LEN()
      TRIM()
      RTRIM()
      LTRIM()
      RPAD()
      LPAD()
      REPLACE()
      REVERSE()
      SUBSTRING()
      CAST()
      CONCATENATE()
      CONCATENATE_WS()
      UPPER function
      LOWER function
      INITCAP function
      INSTR function
   Summary
   Practical exercises
      Practical exercise 1
      Practical exercise 2
      Practical exercise 3
      Practical exercise 4
4
Data Wrangling on the DATE Data Type
   SQL DATE data type functions
      EXTRACT
      DATEDIFF()
      TIMEDIFF()
      DATE_ADD()
      DATE_SUB()
      DATE_FORMAT()
      STR_TO_DATE()
      Extracting the current date and time
   Summary
5
Handling NULL Values
   The impact of missing data and NULL values on data analysis
   Understanding the importance of data validation and cleaning before analyzing data
   Identifying NULL/missing values
   NULL values versus zero values
   Using the IS NULL and IS NOT NULL operators to filter and select data with NULL values
      IS NULL() and IS NOT NULL() – scenario
   Using the COALESCE and IFNULL functions to replace NULL values with a default value
      IFNULL()
      COALESCE()
      IS NULL versus = NULL
   Summary
6
Pivoting Data Using SQL
   SQL Transpose – rows to columns
      Use case scenario
   SQL Cross Tab – columns to rows
      Use case scenario
      Unpivoting data in SQL
      Analytical workflow – from SQL to business intelligence – transforming data into actionable insights
   Summary
Part 3:SQL Subqueries, Aggregate And Window Functions
7
Subqueries and CTEs
   Introduction to subqueries
      Simple subqueries
      Correlated subqueries
      Using subqueries in SELECT statements
      Using subqueries in FROM statements
      Using subqueries in WHERE statements
      Nested subqueries
      Correlated subqueries
      Using subqueries in INSERT, UPDATE, and DELETE statements
   Managing and maintaining subqueries
      Common table expressions
      Performance considerations for subqueries and CTEs
      Subquery versus CTEs
   Summary
8
Aggregate Functions
   Overview of aggregate functions in SQL
      Using GROUP BY
      COUNT()
      SUM()
      AVG()
      MIN() and MAX()
      COUNT(DISTINCT)
      Case scenario – using all aggregate functions
   Summary
9
SQL Window Functions
   The importance of SQL window functions
   SQL aggregate functions
   SQL window functions versus aggregate functions
      Window functions versus aggregate functions – an example to illustrate the differences
   Window functions
      SUM()
      COUNT()
      AVG()
      ROW_NUMBER()
      RANK() and DENSE_RANK()
      Lead() and Lag()
      NTILE()
   Summary
Part 4:Optimizing Query Performance
10
Optimizing Query Performance
   Introduction to query optimization
   Query execution plan
   Query optimization techniques
      Example
      Caching
      Normalization
   Query monitoring and troubleshooting
      Query profiling
      Query logging
      Database monitoring
   Tips and tricks for writing efficient queries
   Summary
   In the next chapter, we will learn about descriptive statistics using SQL, which will provide us with insights into the distribution, central tendency, and variability of data, which can, in turn, help us identify outliers and anomalies. Common SQL functions and statements used for descriptive statistics include COUNT, AVG, MIN, MAX, and GROUP BY. By using SQL to analyze data, researchers and analysts can efficiently extract and summarize information from large datasets.
Part 5:Data Science And Wrangling
11
Descriptive Statistics with SQL
   Calculating descriptive statistics with SQL
      Mean
      Median
      Mode
      Standard deviation
      Variance
      Variability
   Summary
   In the next chapter, we will learn how SQL can be used for time series analysis.
12
Time Series with SQL
   Running totals
      Case scenario
   Lead and lag for time series analysis
      Case scenario
      Key KPIs
   Percentage change
      Case scenario
      Key KPIs
   Moving averages
      Case scenario
      Key KPIs
   Rank for time series analysis
      Case scenario
      Key KPIs
   CTE for time series analysis
      Importance of using CTEs while performing time series analysis
   Forecasting with linear regression
      Case scenario
      Key KPIs
   Summary
   In the next chapter, we will learn different methods to find outliers in the data easily. Outlier detection is an important aspect of data analysis as it helps determine if the data is correct, looks at the skewness of the data, and removes any unexpected values.
13
Outlier Detection
   Measures of central tendency and dispersion
      Case scenario
      Key KPIs
   Methods for detecting outliers
      Box plot method
   Handling outliers
      Case scenario
      Key points to keep in mind while handling outliers
   Applying outlier detection
      Challenges and limitations
      Best practices
   Summary
Index
   Why subscribe?
Other Books You May Enjoy
   Packt is searching for authors like you
   Share Your Thoughts
   Download a free PDF copy of this book