book

Data Wrangling with SQL

Name: Data Wrangling with SQL
ISBN: 9781837630028

by Raghav Kandarpa, Shivangi Saxena

July 2023

Beginner to intermediate

350 pages

9h 39m

English

Packt Publishing

Read now

Unlock full access

Data Wrangling with SQL
AcknowledgementsDedicationContributorsAbout the authorsAbout the reviewers
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchShare Your ThoughtsDownload a free PDF copy of this book
Part 1:Data Wrangling Introduction
Chapter 1: Database Introduction
Getting startedEstablishing the foundationEfficient data organizationData integrity and consistencyTechnical requirementsDecoding database structures – relational and non-relationalWhat is a database?Types of databasesTables and relationshipsThe SQL CREATE DATABASE statementThe SQL CREATE TABLE statementSQL DROP TABLE versus TRUNCATE TABLESQL ALTER TABLESQL constraintsSQL keysDatabase relationshipsComparing database normalization and denormalizationNormalizationTypes of normalizationDenormalizationWhen to apply denormalizationDisadvantages of denormalizationSummaryPractical exercisesPractical exercise 1Practical exercise 2Practical exercise 3Practical exercise 4
Chapter 2: Data Profiling and Preparation before Data Wrangling
What is data wrangling?Data wrangling stepsThe importance of data wranglingBenefits of data wranglingData wrangling use casesBusiness use casesData captureHow does data get captured?Data-capturing techniquesWeb scrapingStructured versus unstructured dataPaid-for versus free data-wrangling toolsData profilingData profiling typesData profiling techniquesPractical exerciseStep 1 – DiscoveryStep 2 – StructuringStep 3 – CleaningStep 4 – EnrichingStep 5 – ValidatingStep 6 – PublishingSummary
Part 2:Data Wrangling Techniques Using SQL
Chapter 3: Data Wrangling on String Data Types
SQL data typesNumeric data typesDate and time data typesString data typeSQL string functionsRIGHT()LEFT()LEN()TRIM()RTRIM()LTRIM()RPAD()LPAD()REPLACE()REVERSE()SUBSTRING()CAST()CONCATENATE()CONCATENATE_WS()UPPER functionLOWER functionINITCAP functionINSTR functionSummaryPractical exercisesPractical exercise 1Practical exercise 2Practical exercise 3Practical exercise 4
Chapter 4: Data Wrangling on the DATE Data Type
SQL DATE data type functionsEXTRACTDATEDIFF()TIMEDIFF()DATE_ADD()DATE_SUB()DATE_FORMAT()STR_TO_DATE()Extracting the current date and timeSummary
Chapter 5: Handling NULL Values
The impact of missing data and NULL values on data analysisUnderstanding the importance of data validation and cleaning before analyzing dataIdentifying NULL/missing valuesNULL values versus zero valuesUsing the IS NULL and IS NOT NULL operators to filter and select data with NULL valuesIS NULL() and IS NOT NULL() – scenarioUsing the COALESCE and IFNULL functions to replace NULL values with a default valueIFNULL()COALESCE()IS NULL versus = NULLSummary
Chapter 6: Pivoting Data Using SQL
SQL Transpose – rows to columnsUse case scenarioSQL Cross Tab – columns to rowsUse case scenarioUnpivoting data in SQLAnalytical workflow – from SQL to business intelligence – transforming data into actionable insightsSummary

Part 3:SQL Subqueries, Aggregate And Window Functions
Chapter 7: Subqueries and CTEs
Introduction to subqueriesSimple subqueriesCorrelated subqueriesUsing subqueries in SELECT statementsUsing subqueries in FROM statementsUsing subqueries in WHERE statementsNested subqueriesCorrelated subqueriesUsing subqueries in INSERT, UPDATE, and DELETE statementsManaging and maintaining subqueriesCommon table expressionsPerformance considerations for subqueries and CTEsSubquery versus CTEsSummary
Chapter 8: Aggregate Functions
Overview of aggregate functions in SQLUsing GROUP BYCOUNT()SUM()AVG()MIN() and MAX()COUNT(DISTINCT)Case scenario – using all aggregate functionsSummary
Chapter 9: SQL Window Functions
The importance of SQL window functionsSQL aggregate functionsSQL window functions versus aggregate functionsWindow functions versus aggregate functions – an example to illustrate the differencesWindow functionsSUM()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 optimizationQuery execution planQuery optimization techniquesExampleCachingNormalizationQuery monitoring and troubleshootingQuery profilingQuery loggingDatabase monitoringTips and tricks for writing efficient queriesSummaryIn 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
Chapter 11: Descriptive Statistics with SQL
Calculating descriptive statistics with SQLMeanMedianModeStandard deviationVarianceVariabilitySummaryIn the next chapter, we will learn how SQL can be used for time series analysis.
Chapter 12: Time Series with SQL
Running totalsCase scenarioLead and lag for time series analysisCase scenarioKey KPIsPercentage changeCase scenarioKey KPIsMoving averagesCase scenarioKey KPIsRank for time series analysisCase scenarioKey KPIsCTE for time series analysisImportance of using CTEs while performing time series analysisForecasting with linear regressionCase scenarioKey KPIsSummaryIn 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.
Chapter 13: Outlier Detection
Measures of central tendency and dispersionCase scenarioKey KPIsMethods for detecting outliersBox plot methodHandling outliersCase scenarioKey points to keep in mind while handling outliersApplying outlier detectionChallenges and limitationsBest practicesSummary
Index
Why subscribe?
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Content preview from Data Wrangling with SQL

Index

As this ebook edition doesn't have fixed pagination, the page numbers below are hyperlinked for reference only, based on the printed edition of this book.

aggregate functions 187, 188, 219-221

AVG 188, 201, 202, 219

case scenario 214, 215

COUNT 188, 192, 193, 219

MAX 188, 206, 207, 219

MIN 188, 206, 207, 219

SUM 188, 196, 197, 219

alternate key 12

Alteryx 48

analytical workflow

SQL to business intelligence (BI) 153

analytic function 222

approximate data types

DOUBLE 64

FLOAT 63

average 272

AVG() function 188, 201, 202, 229, 230

case scenario 202-205

scenario 230, 231

box plot method 312

used, for outlier detection 306, 307

Boyce-Codd Normal Form (BCNF) 25

super key 25, 26

super key, fixing 26, 27

candidate key 11

cascading actions ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781837630028

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Data Wrangling with SQL

by Raghav Kandarpa, Shivangi Saxena

Index

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.