Muutke küpsiste eelistusi

Practical SQL, 2nd Edition [Pehme köide]

4.26/5 (275 hinnangut Goodreads-ist)
  • Formaat: Paperback / softback, 464 pages, kõrgus x laius: 234x177 mm
  • Ilmumisaeg: 25-Jan-2022
  • Kirjastus: No Starch Press,US
  • ISBN-10: 1718501064
  • ISBN-13: 9781718501065
Teised raamatud teemal:
Practical SQL, 2nd EditionSuurem pilt
  • Formaat: Paperback / softback, 464 pages, kõrgus x laius: 234x177 mm
  • Ilmumisaeg: 25-Jan-2022
  • Kirjastus: No Starch Press,US
  • ISBN-10: 1718501064
  • ISBN-13: 9781718501065
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781718501065.html
Märksõnad:
In this newly revised and updated edition to reflect the latest in SQL features, a data analyst provides all the tools necessary to build powerful databases and access information quickly and efficiently. Original.

Analyze data like a pro, even if you’re a beginner.

Practical SQL is an approachable and fast-paced guide to SQL (Structured Query Language), the standard programming language for defining, organizing, and exploring data in relational databases. Anthony DeBarros, a journalist and data analyst, focuses on using SQL to find the story within your data. The examples and code use the open-source database PostgreSQL and its companion pgAdmin interface, and the concepts you learn will apply to most database management systems, including MySQL, Oracle, SQLite, and others.*

You’ll first cover the fundamentals of databases and the SQL language, then build skills by analyzing data from real-world datasets such as US Census demographics, New York City taxi rides, and earthquakes from US Geological Survey. Each chapter includes exercises and examples that teach even those who have never programmed before all the tools necessary to build powerful databases and access information quickly and efficiently.

You’ll learn how to:
  •  Create databases and related tables using your own data

  •  Aggregate, sort, and filter data to find patterns

  •  Use functions for basic math and advanced statistical operations

  •  Identify errors in data and clean them up

  •  Analyze spatial data with a geographic information system (PostGIS)

  •  Create advanced queries and automate tasks


This updated second edition has been thoroughly revised to reflect the latest in SQL features, including additional advanced query techniques for wrangling data. This edition also has two new chapters: an expanded set of instructions on for setting up your system plus a chapter on using PostgreSQL with the popular JSON data interchange format. 

Learning SQL doesn’t have to be dry and complicated. Practical SQL delivers clear examples with an easy-to-follow approach to teach you the tools you need to build and manage your own databases.

* Microsoft SQL Server employs a variant of the language called T-SQL, which is not covered by Practical SQL.

Arvustused

"Even better than the previous edition!" Jakub Romanowski, LearnSQL.com

"This book is comprehensive, well organized, and contains almost everything a new developer needs to learn SQL using Postgres. Besides, the arrangement of the sections is logical, and the writing style is not complicated where information is supported by the required examples and images, in addition to evidence from the writer's practical experiences." Hadi Fadlallah, SQLShack

"[ W]ell-written and informative . . . an extremely enjoyable book that will keep the reader engaged with interesting, thought-provoking exercises . . . I highly recommend Practical SQL, 2nd Edition to anyone wanting to learn PostgreSQL and how to tell stories with data." Joshua Allen Holm, Opensource.com



REVIEWS FOR THE FIRST EDITION:

"This book is comprehensive, well organized, and contains almost everything a new developer needs to learn SQL using Postgres. Besides, the arrangement of the sections is logical, and the writing style is not complicated where information is supported by the required examples and images, in addition to evidence from the writer's practical experiences." Hadi Fadlallah, SQLShack

One of the best-organized travelogues we've seen into the SQL realm. Newstips Bulletin

"More fun and more engaging [ than Learning SQL from O'Reilly]." Giles McMullen-Klein, Python Programmer, YouTuber

Practical SQL will take you from an absolute databases beginner towards becoming a database and SQL master. This book is one of those books that you feel like [ you're] reading a novel, not a technical book. It uses an easy to follow and [ understandable] approach that makes it easy to read for people from all backgrounds. Sara A. Metwalli, Towards Data Science

"I recommend reading Practical SQL by Anthony DeBarros for a more in-depth look on how to implement regression analysis and other analytical tools using SQL." Michael Grogan, Towards Data Science

"One of the most interesting books about SQL I've ever read. At first glance, it looks like a guide for complete rookies. But the author raises topics that will interest advanced users." Jakub Romanowski, LearnSQL.com

Preface to the Second Edition xvii
Acknowledgments xxi
Introduction xxiii
What Is SQL? xxiv
Why SQL? xxv
Who Is This Book For? xxvi
What You'll Learn xxvi
1 Setting up your Coding Environment
1(16)
Installing a Text Editor
2(1)
Downloading Code and Data from GitHub
3(1)
Installing PostgreSQL and pgAdmin
3(1)
Windows Installation
4(4)
macOS Installation
8(1)
Linux Installation
9(1)
Working with pgAdmin
10(1)
Launching pgAdmin and Setting a Master Password
11(1)
Connecting to the Default postgres Database
12(2)
Exploring the Query Tool
14(1)
Customizing pgAdmin
15(1)
Alternatives to pgAdmin
15(1)
Wrapping Up
16(1)
2 Creating Your First Database and Table
17(12)
Understanding Tables
17(2)
Creating a Database
19(1)
Executing SQL in pgAdmin
20(2)
Connecting to the analysis Database
22(1)
Creating a Table
22(1)
Using the CREATE TABLE Statement
22(1)
Making the teachers Table
23(2)
Inserting Rows into a Table
25(1)
Using the INSERT Statement
25(1)
Viewing the Data
26(1)
Getting Help When Code Goes Bad
26(1)
Formatting SQL for Readability
27(1)
Wrapping Up
27(2)
3 Beginning Data Exploration with Select
29(30)
Basic SELECT Syntax
30(1)
Querying a Subset of Columns
31(1)
Sorting Data with ORDER BY
32(1)
Using DISTINCT to Find Unique Values
33(2)
Filtering Rows with WHERE
35(2)
Using LIKE and ILIKE with WHERE
37(1)
Combining Operators with AND and OR
38(1)
Putting It All Together
39(1)
Wrapping Up
39(2)
UNDERSTANDING DATA TYPES
41(1)
Understanding Characters
42(2)
Understanding Numbers
44(1)
Using Integers
45(1)
Auto-Incrementing Integers
46(1)
Using Decimal Numbers
47(3)
Choosing Your Number Data Type
50(1)
Understanding Dates and Times
51(2)
Using the interval Data Type in Calculations
53(1)
Understanding JSON and JSONB
54(1)
Using Miscellaneous Types
55(1)
Transforming Values from One Type to Another with CAST
55(1)
Using CAST Shortcut Notation
56(1)
Wrapping Up
56(3)
5 Importing and Exporting Data
59(18)
Working with Delimited Text Files
60(1)
Handling Header Rows
61(1)
Quoting Columns That Contain Delimiters
61(1)
Using COPY to Import Data
62(1)
Importing Census Data Describing Counties
63(1)
Creating the us_counties_pop_est_2019 Table
64(1)
Understanding Census Columns and Data Types
65(2)
Performing the Census Import with COPY
67(1)
Inspecting the Import
67(1)
Importing a Subset of Columns with COPY
68(2)
Importing a Subset of Rows with COPY
70(1)
Adding a Value to a Column During Import
71(1)
Using COPY to Export Data
72(1)
Exporting All Data
72(1)
Exporting Particular Columns
73(1)
Exporting Query Results
73(1)
Importing and Exporting Through pgAdmin
74(1)
Wrapping Up
75(2)
6 Basic Math and Stats With Sql
77(16)
Understanding Math Operators and Functions
78(1)
Understanding Math and Data Types
78(1)
Adding, Subtracting, and Multiplying
79(1)
Performing Division and Modulo
79(1)
Using Exponents, Roots, and Factorials
80(1)
Minding the Order of Operations
81(1)
Doing Math Across Census Table Columns
82(1)
Adding and Subtracting Columns
82(2)
Finding Percentages of the Whole
84(1)
Tracking Percent Change
85(1)
Using Aggregate Functions for Averages and Sums
86(1)
Finding the Median
87(1)
Finding the Median with Percentile Functions
88(1)
Finding Median and Percentiles with Census Data
89(1)
Finding Other Quantiles with Percentile Functions
90(1)
Finding the Mode
91(1)
Wrapping Up
92(1)
7 Joining Tables in a Relational Database
93(24)
Linking Tables Using JOIN
94(1)
Relating Tables with Key Columns
94(3)
Querying Multiple Tables Using JOIN
97(1)
Understanding JOIN Types
98(2)
JOIN
100(1)
LEFT JOIN and RIGHT JOIN
101(1)
FULL OUTER JOIN
102(1)
CROSS JOIN
103(1)
Using NULL to Find Rows with Missing Values
104(1)
Understanding the Three Types of Table Relationships
105(1)
One-to-One Relationship
105(1)
One-to-Many Relationship
105(1)
Many-to-Many Relationship
105(1)
Selecting Specific Columns in a Join
106(1)
Simplifying JOIN Syntax with Table Aliases
107(1)
Joining Multiple Tables
107(2)
Combining Query Results with Set Operators
109(1)
UNION and UNION ALL
109(2)
INTERSECT and EXCEPT
111(1)
Performing Math on Joined Table Columns
112(2)
Wrapping Up
114(3)
8 Table Design that Works for You
117(22)
Following Naming Conventions
118(1)
Quoting Identifiers Enables Mixed Case
118(1)
Pitfalls with Quoting Identifiers
119(1)
Guidelines for Naming Identifiers
120(1)
Controlling Column Values with Constraints
120(1)
Primary Keys: Natural vs. Surrogate
121(7)
Foreign Keys
128(1)
How to Automatically Delete Related Records with CASCADE
129(1)
The CHECK Constraint
130(1)
The UNIQUE Constraint
131(1)
The NOT NULL Constraint
132(1)
How to Remove Constraints or Add Them Later
132(1)
Speeding Up Queries with Indexes
133(1)
B-Tree: PostgreSQL's Default Index
134(3)
Considerations When Using Indexes
137(1)
Wrapping Up
137(2)
9 Extracting Information By Grouping and Summarizing
139(18)
Creating the Library Survey Tables
140(1)
Creating the 2018 Library Data Table
140(1)
Creating the 2017 and 2016 Library Data Tables
141(1)
Exploring the Library Data Using Aggregate Functions
142(1)
Counting Rows and Values Using count()
143(2)
Finding Maximum and Minimum Values Using max() and min()
145(1)
Aggregating Data Using GROUP BY
146(8)
Wrapping Up
154(3)
10 Inspecting and Modifying Data
157(26)
Importing Data on Meat, Poultry, and Egg Producers
158(1)
Interviewing the Dataset
159(1)
Checking for Missing Values
160(2)
Checking for Inconsistent Data Values
162(1)
Checking for Malformed Values Using length()
162(2)
Modifying Tables, Columns, and Data
164(1)
Modifying Tables with ALTER TABLE
165(1)
Modifying Values with UPDATE
166(1)
Viewing Modified Data with RETURNING
167(1)
Creating Backup Tables
167(1)
Restoring Missing Column Values
168(2)
Updating Values for Consistency
170(2)
Repairing ZIP Codes Using Concatenation
172(1)
Updating Values Across Tables
173(2)
Deleting Unneeded Data
175(1)
Deleting Rows from a Table
175(1)
Deleting a Column from a Table
176(1)
Deleting a Table from a Database
176(1)
Using Transactions to Save or Revert Changes
177(2)
Improving Performance When Updating Large Tables
179(1)
Wrapping Up
180(3)
11 Statistical Functions in Sql
183(20)
Creating a Census Stats Table
184(1)
Measuring Correlation with corr (Y, X)
185(3)
Checking Additional Correlations
188(1)
Predicting Values with Regression Analysis
189(2)
Finding the Effect of an Independent Variable with r-Squared
191(1)
Finding Variance and Standard Deviation
192(1)
Creating Rankings with SQL
193(1)
Ranking with rank() and dense_rank()
193(2)
Ranking Within Subgroups with PARTITION BY
195(1)
Calculating Rates for Meaningful Comparisons
196(1)
Finding Rates of Tourism-Related Businesses
197(1)
Smoothing Uneven Data
198(4)
Wrapping Up
202(1)
12 Working with Dates and Times
203(20)
Understanding Data Types and Functions for Dates and Times
204(1)
Manipulating Dates and Times
204(1)
Extracting the Components of a timestamp Value
205(2)
Creating Datetime Values from timestamp Components
207(1)
Retrieving the Current Date and Time
208(1)
Working with Time Zones
209(1)
Finding Your Time Zone Setting
209(1)
Setting the Time Zone
210(2)
Performing Calculations with Dates and Times
212(1)
Finding Patterns in New York City Taxi Data
213(5)
Finding Patterns in Amtrak Data
218(4)
Wrapping Up
222(1)
13 Advanced Query Techniques
223(22)
Using Subqueries
223(1)
Filtering with Subqueries in a WHERE Clause
224(1)
Creating Derived Tables with Subqueries
225(1)
Joining Derived Tables
226(2)
Generating Columns with Subqueries
228(1)
Understanding Subquery Expressions
229(2)
Using Subqueries with LATERAL
231(3)
Using Common Table Expressions
234(2)
Performing Cross Tabulations
236(1)
Installing the crosstab() Function
237(1)
Tabulating Survey Results
237(2)
Tabulating City Temperature Readings
239(2)
Reclassifying Values with CASE
241(1)
Using CASE in a Common Table Expression
242(2)
Wrapping Up
244(1)
14 Mining Text to Find Meaningful Data
245(30)
Formatting Text Using String Functions
246(1)
Case Formatting
246(1)
Character Information
246(1)
Removing Characters
247(1)
Extracting and Replacing Characters
247(1)
Matching Text Patterns with Regular Expressions
247(1)
Regular Expression Notation
248(2)
Using Regular Expressions with WHERE
250(1)
Regular Expression Functions to Replace or Split Text
251(2)
Turning Text to Data with Regular Expression Functions
253(12)
Full-Text Search in PostgreSQL
265(1)
Text Search Data Types
265(2)
Creating a Table for Full-Text Search
267(1)
Searching Speech Text
268(3)
Ranking Query Matches by Relevance
271(2)
Wrapping Up
273(2)
15 Analyzing Spatial Data with Postgis
275(30)
Enabling PostGIS and Creating a Spatial Database
276(1)
Understanding the Building Blocks of Spatial Data
276(1)
Understanding Two-Dimensional Geometries
277(1)
Well-Known Text Formats
278(1)
Projections and Coordinate Systems
279(1)
Spatial Reference System Identifier
279(1)
Understanding PostGIS Data Types
280(1)
Creating Spatial Objects with PostGIS Functions
281(1)
Creating a Geometry Type from Well-Known Text
281(2)
Creating a Geography Type from Well-Known Text
283(1)
Using Point Functions
283(1)
Using LineString Functions
284(1)
Using Polygon Functions
284(1)
Analyzing Farmers' Markets Data
285(1)
Creating and Filling a Geography Column
285(1)
Adding a Spatial Index
286(1)
Finding Geographies Within a Given Distance
287(2)
Finding the Distance Between Geographies
289(1)
Finding the Nearest Geographies
290(1)
Working with Census Shapefiles
291(1)
Understanding the Contents of a Shapefile
292(1)
Loading Shapefiles
292(3)
Exploring the Census 2019 Counties Shapefile
295(3)
Examining Demographics Within a Distance
298(2)
Performing Spatial Joins
300(1)
Exploring Roads and Waterways Data
300(1)
Joining the Census Roads and Water Tables
300(1)
Finding the Location Where Objects Intersect
301(1)
Wrapping Up
302(3)
16 Working with Json Data
305(32)
Understanding JSON Structure
306(1)
Considering When to Use JSON with SQL
307(1)
Using json and jsonb Data Types
308(1)
Importing and Indexing JSON Data
309(1)
Using json and jsonb Extraction Operators
310(1)
Key Value Extraction
311(1)
Array Element Extraction
312(2)
Path Extraction
314(1)
Containment and Existence
315(3)
Analyzing Earthquake Data
318(1)
Exploring and Loading the Earthquake Data
318(1)
Working with Earthquake Times
319(2)
Finding the Largest and Most-Reported Earthquakes
321(2)
Converting Earthquake JSON to Spatial Data
323(4)
Generating and Manipulating JSON
327(1)
Turning Query Results into JSON
327(2)
Adding, Updating, and Deleting Keys and Values
329(2)
Using JSON Processing Functions
331(1)
Finding the Length of an Array
331(1)
Returning Array Elements as Rows
332(1)
Wrapping Up
333(4)
17 Saving Time With Views, Functions, and Triggers
337(26)
Using Views to Simplify Queries
338(1)
Creating and Querying Views
338(3)
Creating and Refreshing a Materialized View
341(1)
Inserting, Updating, and Deleting Data Using a View
342(4)
Creating Your Own Functions and Procedures
346(1)
Creating the percent_change() Function
347(1)
Using the percent_change() Function
348(1)
Updating Data with a Procedure
349(3)
Using the Python Language in a Function
352(2)
Automating Database Actions with Triggers
354(1)
Logging Grade Updates to a Table
354(4)
Automatically Classifying Temperatures
358(2)
Wrapping Up
360(3)
18 Using Postgresql from the Command Line
363(24)
Setting Up the Command Line for psql
364(1)
Windows psql Setup
364(4)
macOS psql Setup
368(2)
Linux psql Setup
370(1)
Working with psql
370(1)
Launching psql and Connecting to a Database
370(4)
Running SQL Queries on psql
374(1)
Navigating and Formatting Results
375(4)
Meta-Commands for Database Information
379(1)
Importing, Exporting, and Using Files
380(3)
Additional Command Line Utilities to Expedite Tasks
383(1)
Adding a Database with createdb
383(1)
Loading Shapefiles with shp2pgsql
383(1)
Wrapping Up
384(3)
19 Maintaining Your Database
387(12)
Recovering Unused Space with VACUUM
388(1)
Tracking Table Size
388(2)
Monitoring the Autovacuum Process
390(2)
Running VACUUM Manually
392(1)
Reducing Table Size with VACUUM FULL
392(1)
Changing Server Settings
392(1)
Locating and Editing postgresql.conf
393(2)
Reloading Settings with pg_ctl
395(1)
Backing Up and Restoring Your Database
395(1)
Using pg_dump to Export a Database or Table
395(1)
Restoring a Database Export with pg_restore
396(1)
Exploring Additional Backup and Restore Options
397(1)
Wrapping Up
397(2)
20 Telling Your Data's Story
399(8)
Start with a Question
400(1)
Document Your Process
400(1)
Gather Your Data
400(1)
No Data? Build Your Own Database
401(1)
Assess the Data's Origins
402(1)
Interview the Data with Queries
402(1)
Consult the Data's Owner
403(1)
Identify Key Indicators and Trends over Time
403(2)
Ask Why
405(1)
Communicate Your Findings
405(1)
Wrapping Up
406(1)
APPENDIX: ADDITIONAL POSTGRESQL RESOURCES
407(4)
PostgreSQL Development Environments
407(1)
PostgreSQL Utilities, Tools, and Extensions
408(1)
PostgreSQL News and Community
409(1)
Documentation
410(1)
Index 411
Anthony DeBarros is a longtime journalist who has combined his interests in data analysis, coding, software product development, and storytelling for much of his career. He is a data news editor for The Wall Street Journal, focusing on topics such as the economy, trade, the U.S. Census, and politics. He previously held positions with USA TODAY, the Poughkeepsie Journal, and the DocumentCloud project for Investigative Reporters and Editors. His work has received multiple honors, including a prestigious Silver Baton from the Alfred I. duPont-Columbia University Awards.