Practical SQL: A Beginner’s Guide to Storytelling with Data

388 Pages·2018·4.5 MB·English

F I N D T H E This book uses PostgreSQL
but is applicable to MySQL,
S T O R Y I N Microsoft SQL Server, and
PR AC TICA L
YO U R D A T A other database systems.
SQL
SQL (Structured Query Language) is a popular You’ll also learn how to: A B E G I N N E R ’ S G U I D E T O
programming language used to create, manage, S T O R Y T E L L I N G W I T H D A T A
• Define the right data types for your information
and query databases. Whether you’re a marketing
analyst, a journalist, or a researcher mapping neurons • Aggregate, sort, and filter data to find patterns
in the brain of a fruit fly, you’ll benefit from using SQL
to tel the story hidden in your data. • Identify and clean up any errors in your data A N T H O N Y D E B A R R O S
• Search text for meaningful data
Practical SQL is a fast-paced, plain-English introduc-
tion to programming with SQL. Following a primer on • Create advanced queries and automate tedious
SQL language basics and database fundamentals, tasks
you’ll learn how to use the pgAdmin interface and
Organizing and analyzing data doesn’t have to be
PostgreSQL database system to define, organize, and
dry and complicated. Find the story in your data with
analyze real-world data sets, such as crime statistics
Practical SQL.
and U.S. Census demographics.
A B O U T T H E A U T H O R
Next, you’ll learn how to create databases using
your own data, write queries to perform calculations, Anthony DeBarros is an award-winning data journalist
and handle common roadblocks when dealing with whose career spans 30 years at news organizations
public data. With the help of easy-to-follow exercises including USA TODAY and Gannett’s Poughkeepsie
in each chapter, you’ll discover how to build powerful Journal. He holds a master’s degree in information
databases and find meaning in your data sets. systems from Marist College.
TH E F I N EST I N G E E K E NTE RTA I N M E NT ™
www.nostarch.com
$39.95 ($53.95 CDN)
D E B A R R O S
P R A C T I C A L S Q L
SHELVE IN:
PROGRAMMING LANGUAGES/SQL
practical sql
p r a c t i c a l
s q l
a B e g i n n e r ’ s G u i d e t o
s t o r y t e l l i n g w i t h D a t a
by Anthony DeBarros
San Francisco
practical sql. Copyright © 2018 by Anthony DeBarros.
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-10: 1-59327-827-6
ISBN-13: 978-1-59327-827-4
Publisher: William Pollock
Production Editor: Janelle Ludowise
Cover Illustration: Josh Ellingson
Interior Design: Octopod Studios
Developmental Editors: Liz Chadwick and Annie Choi
Technical Reviewer: Josh Berkus
Copyeditor: Anne Marie Walker
Compositor: Janelle Ludowise
Proofreader: James Fraleigh
For information on distribution, translations, or bulk sales, please contact No Starch Press, Inc. directly:
No Starch Press, Inc.
245 8th Street, San Francisco, CA 94103
phone: [phone];
about the author
Anthony DeBarros is an award-winning journalist who has combined avid
interests in data analysis, coding, and storytelling for much of his career. He
spent more than 25 years with the Gannett company, including the Pough-
keepsie Journal, USA TODAY, and Gannett Digital. He is currently senior vice
president for content and product development for a publishing and events
firm and lives and works in the Washington, D.C., area.
about the technical reviewer
Josh Berkus is a “hacker emeritus” for the PostgreSQL Project, where he
served on the Core Team for 13 years. He was also a database consultant
for 15 years, working with PostgreSQL, MySQL, CitusDB, Redis, CouchDB,
Hadoop, and Microsoft SQL Server. Josh currently works as a Kubernetes
community manager at Red Hat, Inc.
B r i e f c o n t e n t s
Foreword by Sarah Frostenson xvii
Acknowledgments xxi
Introduction xxiii
Chapter 1: Creating Your First Database and Table 1
Chapter 2: Beginning Data Exploration with SELECT 11
Chapter 3: Understanding Data Types 23
Chapter 4: Importing and Exporting Data 39
Chapter 5: Basic Math and Stats with SQL 55
Chapter 6: Joining Tables in a Relational Database 73
Chapter 7: Table Design That Works for You 93
Chapter 8: Extracting Information by Grouping and Summarizing 113
Chapter 9: Inspecting and Modifying Data 129
Chapter 10: Statistical Functions in SQL 155
Chapter 11: Working with Dates and Times 171
Chapter 12: Advanced Query Techniques 191
Chapter 13: Mining Text to Find Meaningful Data 211
Chapter 14: Analyzing Spatial Data with PostGIS 241
Chapter 15: Saving Time with Views, Functions, and Triggers 267
Chapter 16: Using PostgreSQL from the Command Line 291
Chapter 17: Maintaining Your Database 313
Chapter 18: Identifying and Telling the Story Behind Your Data 325
Appendix: Additional PostgreSQL Resources 333
Index 337
c o n t e n t s i n D e t a i l
Foreword by sarah Frostenson xvii
acknowledgments xxi
introduction xxiii
What Is SQL? xxiv
Why Use SQL? xxiv
About This Book xxv
Using the Book’s Code Examples xxvii
Using PostgreSQL xxviii
Installing PostgreSQL xxviii
Working with pgAdmin xxxi
Alternatives to pgAdmin xxxiii
Wrapping Up xxxiii
1
creating Your First database and table 1
Creating a Database 3
Executing SQL in pgAdmin 3
Connecting to the Analysis Database 5
Creating a Table 5
The CREATE TABLE Statement 6
Making the teachers Table 7
Inserting Rows into a Table 8
The INSERT Statement 8
Viewing the Data 9
When Code Goes Bad 9
Formatting SQL for Readability 10
Wrapping Up 10
Try It Yourself 10
2
beginning data exploration with select 11
Basic SELECT Syntax 12
Querying a Subset of Columns 13
Using DISTINCT to Find Unique Values 14
Sorting Data with ORDER BY 15
Filtering Rows with WHERE 17
Using LIKE and ILIKE with WHERE 19
Combining Operators with AND and OR 20
Putting It All Together 21
Wrapping Up 21
Try It Yourself 22