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

9 SQL Window Functions

SQL window functions aid in data wrangling by providing a way to perform complex calculations and manipulations on a set of rows related to the current row in the result set without the need for self-joins or multiple subqueries. This makes the process of data wrangling more efficient, as well as easier to understand and maintain. Window functions are comparable to the types of calculation that can be done with an aggregate function. However, we need to keep in mind that window functions can be challenging at times due to their complex syntax, so we need a deep understanding of their underlying concepts to use them properly. They require careful consideration of the window frame and the partitioning clauses, as well as ...

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

9

SQL Window Functions

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.