book

Practical SQL, 2nd Edition

Name: Practical SQL, 2nd Edition
Author: Anthony DeBarros
ISBN: 9781718501065

by Anthony DeBarros

March 2022

Beginner to intermediate

448 pages

12h 26m

English

No Starch Press

Read now

Unlock full access

Title Page
Copyright
About the Author
Preface to the Second Edition
Acknowledgments
Introduction
What Is SQL?Why SQL?Who Is This Book For?What You’ll Learn
Chapter 1: Setting Up Your Coding Environment
Installing a Text EditorDownloading Code and Data from GitHubInstalling PostgreSQL and pgAdminWindows InstallationmacOS InstallationLinux InstallationWorking with pgAdminLaunching pgAdmin and Setting a Master PasswordConnecting to the Default postgres DatabaseExploring the Query ToolCustomizing pgAdminAlternatives to pgAdminWrapping Up
Chapter 2: Creating Your First Database and Table
Understanding TablesCreating a DatabaseExecuting SQL in pgAdminConnecting to the analysis DatabaseCreating a TableUsing the CREATE TABLE StatementMaking the teachers TableInserting Rows into a TableUsing the INSERT StatementViewing the DataGetting Help When Code Goes BadFormatting SQL for ReadabilityWrapping Up
Chapter 3: Beginning Data Exploration with SELECT
Basic SELECT SyntaxQuerying a Subset of ColumnsSorting Data with ORDER BYUsing DISTINCT to Find Unique ValuesFiltering Rows with WHEREUsing LIKE and ILIKE with WHERECombining Operators with AND and ORPutting It All TogetherWrapping Up
Chapter 4: Understanding Data Types
Understanding CharactersUnderstanding NumbersUsing IntegersAuto-Incrementing IntegersUsing Decimal NumbersChoosing Your Number Data TypeUnderstanding Dates and TimesUsing the interval Data Type in CalculationsUnderstanding JSON and JSONBUsing Miscellaneous TypesTransforming Values from One Type to Another with CASTUsing CAST Shortcut NotationWrapping Up

Chapter 5: Importing and Exporting Data
Working with Delimited Text FilesHandling Header RowsQuoting Columns That Contain DelimitersUsing COPY to Import Data Importing Census Data Describing CountiesCreating the us_counties_pop_est_2019 TableUnderstanding Census Columns and Data TypesPerforming the Census Import with COPYInspecting the ImportImporting a Subset of Columns with COPYImporting a Subset of Rows with COPYAdding a Value to a Column During ImportUsing COPY to Export DataExporting All DataExporting Particular ColumnsExporting Query ResultsImporting and Exporting Through pgAdminWrapping Up
Chapter 6: Basic Math and Stats with SQL
Understanding Math Operators and FunctionsUnderstanding Math and Data TypesAdding, Subtracting, and MultiplyingPerforming Division and ModuloUsing Exponents, Roots, and FactorialsMinding the Order of OperationsDoing Math Across Census Table ColumnsAdding and Subtracting ColumnsFinding Percentages of the WholeTracking Percent ChangeUsing Aggregate Functions for Averages and SumsFinding the MedianFinding the Median with Percentile FunctionsFinding Median and Percentiles with Census DataFinding Other Quantiles with Percentile FunctionsFinding the ModeWrapping Up
Chapter 7: Joining Tables in a Relational Database
Linking Tables Using JOINRelating Tables with Key ColumnsQuerying Multiple Tables Using JOINUnderstanding JOIN TypesJOINLEFT JOIN and RIGHT JOINFULL OUTER JOINCROSS JOINUsing NULL to Find Rows with Missing ValuesUnderstanding the Three Types of Table RelationshipsOne-to-One RelationshipOne-to-Many RelationshipMany-to-Many RelationshipSelecting Specific Columns in a JoinSimplifying JOIN Syntax with Table AliasesJoining Multiple TablesCombining Query Results with Set OperatorsUNION and UNION ALLINTERSECT and EXCEPTPerforming Math on Joined Table ColumnsWrapping Up
Chapter 8: Table Design That Works for You
Following Naming ConventionsQuoting Identifiers Enables Mixed CasePitfalls with Quoting IdentifiersGuidelines for Naming IdentifiersControlling Column Values with ConstraintsPrimary Keys: Natural vs. SurrogateForeign KeysHow to Automatically Delete Related Records with CASCADEThe CHECK ConstraintThe UNIQUE ConstraintThe NOT NULL ConstraintHow to Remove Constraints or Add Them LaterSpeeding Up Queries with IndexesB-Tree: PostgreSQL’s Default IndexConsiderations When Using IndexesWrapping Up
Chapter 9: Extracting Information by Grouping and Summarizing
Creating the Library Survey TablesCreating the 2018 Library Data TableCreating the 2017 and 2016 Library Data TablesExploring the Library Data Using Aggregate FunctionsCounting Rows and Values Using count()Finding Maximum and Minimum Values Using max() and min()Aggregating Data Using GROUP BYWrapping Up
Chapter 10: Inspecting and Modifying Data
Importing Data on Meat, Poultry, and Egg ProducersInterviewing the DatasetChecking for Missing ValuesChecking for Inconsistent Data ValuesChecking for Malformed Values Using length()Modifying Tables, Columns, and DataModifying Tables with ALTER TABLEModifying Values with UPDATEViewing Modified Data with RETURNINGCreating Backup TablesRestoring Missing Column ValuesUpdating Values for ConsistencyRepairing ZIP Codes Using ConcatenationUpdating Values Across TablesDeleting Unneeded DataDeleting Rows from a TableDeleting a Column from a TableDeleting a Table from a DatabaseUsing Transactions to Save or Revert ChangesImproving Performance When Updating Large TablesWrapping Up
Chapter 11: Statistical Functions in SQL
Creating a Census Stats TableMeasuring Correlation with corr(Y, X)Checking Additional CorrelationsPredicting Values with Regression AnalysisFinding the Effect of an Independent Variable with r-SquaredFinding Variance and Standard DeviationCreating Rankings with SQLRanking with rank() and dense_rank()Ranking Within Subgroups with PARTITION BYCalculating Rates for Meaningful ComparisonsFinding Rates of Tourism-Related BusinessesSmoothing Uneven DataWrapping Up
Chapter 12: Working with Dates and Times
Understanding Data Types and Functions for Dates and TimesManipulating Dates and TimesExtracting the Components of a timestamp ValueCreating Datetime Values from timestamp ComponentsRetrieving the Current Date and TimeWorking with Time ZonesFinding Your Time Zone SettingSetting the Time ZonePerforming Calculations with Dates and TimesFinding Patterns in New York City Taxi DataFinding Patterns in Amtrak DataWrapping Up
Chapter 13: Advanced Query Techniques
Using SubqueriesFiltering with Subqueries in a WHERE ClauseCreating Derived Tables with SubqueriesJoining Derived TablesGenerating Columns with SubqueriesUnderstanding Subquery ExpressionsUsing Subqueries with LATERALUsing Common Table ExpressionsPerforming Cross TabulationsInstalling the crosstab() FunctionTabulating Survey ResultsTabulating City Temperature ReadingsReclassifying Values with CASEUsing CASE in a Common Table ExpressionWrapping Up
Chapter 14: Mining Text to Find Meaningful Data
Formatting Text Using String FunctionsCase FormattingCharacter InformationRemoving CharactersExtracting and Replacing CharactersMatching Text Patterns with Regular ExpressionsRegular Expression NotationUsing Regular Expressions with WHERERegular Expression Functions to Replace or Split TextTurning Text to Data with Regular Expression FunctionsFull-Text Search in PostgreSQLText Search Data TypesCreating a Table for Full-Text SearchSearching Speech TextRanking Query Matches by RelevanceWrapping Up
Chapter 15: Analyzing Spatial Data with PostGIS
Enabling PostGIS and Creating a Spatial DatabaseUnderstanding the Building Blocks of Spatial DataUnderstanding Two-Dimensional GeometriesWell-Known Text FormatsProjections and Coordinate SystemsSpatial Reference System IdentifierUnderstanding PostGIS Data TypesCreating Spatial Objects with PostGIS FunctionsCreating a Geometry Type from Well-Known TextCreating a Geography Type from Well-Known TextUsing Point FunctionsUsing LineString FunctionsUsing Polygon FunctionsAnalyzing Farmers’ Markets DataCreating and Filling a Geography ColumnAdding a Spatial IndexFinding Geographies Within a Given DistanceFinding the Distance Between GeographiesFinding the Nearest GeographiesWorking with Census ShapefilesUnderstanding the Contents of a ShapefileLoading ShapefilesExploring the Census 2019 Counties ShapefileExamining Demographics Within a DistancePerforming Spatial JoinsExploring Roads and Waterways DataJoining the Census Roads and Water TablesFinding the Location Where Objects IntersectWrapping Up
Chapter 16: Working with JSON Data
Understanding JSON StructureConsidering When to Use JSON with SQLUsing json and jsonb Data TypesImporting and Indexing JSON DataUsing json and jsonb Extraction OperatorsKey Value ExtractionArray Element ExtractionPath ExtractionContainment and ExistenceAnalyzing Earthquake DataExploring and Loading the Earthquake DataWorking with Earthquake TimesFinding the Largest and Most-Reported EarthquakesConverting Earthquake JSON to Spatial DataGenerating and Manipulating JSONTurning Query Results into JSONAdding, Updating, and Deleting Keys and ValuesUsing JSON Processing FunctionsFinding the Length of an ArrayReturning Array Elements as RowsWrapping Up
Chapter 17: Saving Time with Views, Functions, and Triggers
Using Views to Simplify QueriesCreating and Querying ViewsCreating and Refreshing a Materialized ViewInserting, Updating, and Deleting Data Using a ViewCreating Your Own Functions and ProceduresCreating the percent_change() FunctionUsing the percent_change() FunctionUpdating Data with a ProcedureUsing the Python Language in a FunctionAutomating Database Actions with TriggersLogging Grade Updates to a TableAutomatically Classifying TemperaturesWrapping Up
Chapter 18: Using PostgreSQL from the Command Line
Setting Up the Command Line for psqlWindows psql Setup macOS psql SetupLinux psql SetupWorking with psql Launching psql and Connecting to a DatabaseRunning SQL Queries on psqlNavigating and Formatting ResultsMeta-Commands for Database InformationImporting, Exporting, and Using FilesAdditional Command Line Utilities to Expedite TasksAdding a Database with createdbLoading Shapefiles with shp2pgsqlWrapping Up
Chapter 19: Maintaining Your Database
Recovering Unused Space with VACUUMTracking Table SizeMonitoring the Autovacuum ProcessRunning VACUUM ManuallyReducing Table Size with VACUUM FULLChanging Server SettingsLocating and Editing postgresql.confReloading Settings with pg_ctlBacking Up and Restoring Your DatabaseUsing pg_dump to Export a Database or TableRestoring a Database Export with pg_restoreExploring Additional Backup and Restore OptionsWrapping Up
Chapter 20: Telling Your Data’s Story
Start with a QuestionDocument Your ProcessGather Your DataNo Data? Build Your Own DatabaseAssess the Data’s OriginsInterview the Data with QueriesConsult the Data’s OwnerIdentify Key Indicators and Trends over TimeAsk WhyCommunicate Your FindingsWrapping Up
Appendix: Additional PostgreSQL Resources
PostgreSQL Development EnvironmentsPostgreSQL Utilities, Tools, and ExtensionsPostgreSQL News and CommunityDocumentation
Index

Content preview from Practical SQL, 2nd Edition

16 Working with JSON Data

JavaScript Object Notation (JSON) is a widely used text format for storing data in a platform-independent way so it can be shared between computer systems. In this chapter, you’ll learn the structure of JSON as well as how to store and query JSON data types in PostgreSQL. After we explore PostgreSQL’s JSON query operators, we’ll analyze a month’s worth of data about earthquakes.

The American National Standards Institute (ANSI) SQL standard added syntax definitions for JSON and specified functions for creating and accessing JSON objects in 2016. Major database systems have added JSON support in recent years as well, ...

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: 9781098129866

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Practical SQL, 2nd Edition

by Anthony DeBarros

16 Working with JSON Data

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.