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

Cover
Title Page
Copyright and Credits
Dedication
Contributors
Table of Contents
Preface
Part 1:Data Wrangling Introduction
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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 functi
Part 5:Data Science And Wrangling
Chapter 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.
Chapter 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
Chapter 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
Other Books You May Enjoy