book

Practical SQL

by Anthony DeBarros

May 2018

Intermediate to advanced

392 pages

10h 22m

English

No Starch Press

Read now

Unlock full access

What Is SQL?Why Use SQL?About This BookUsing the Book’s Code ExamplesUsing PostgreSQLWrapping Up

Creating a DatabaseCreating a TableInserting Rows into a TableWhen Code Goes BadFormatting SQL for ReadabilityWrapping UpTry It Yourself
Basic SELECT SyntaxSorting Data with ORDER BYFiltering Rows with WHEREPutting It All TogetherWrapping UpTry It Yourself
CharactersNumbersDates and TimesUsing the interval Data Type in CalculationsMiscellaneous TypesTransforming Values from One Type to Another with CASTCAST Shortcut NotationWrapping UpTry It Yourself
Working with Delimited Text FilesUsing COPY to Import DataImporting Census Data Describing CountiesImporting a Subset of Columns with COPYAdding a Default Value to a Column During ImportUsing COPY to Export DataImporting and Exporting Through pgAdminWrapping UpTry It Yourself
Math OperatorsDoing Math Across Census Table ColumnsAggregate Functions for Averages and SumsFinding the MedianFinding the ModeWrapping UpTry It Yourself
Linking Tables Using JOINRelating Tables with Key ColumnsQuerying Multiple Tables Using JOINJOIN TypesUsing NULL to Find Rows with Missing ValuesThree Types of Table RelationshipsSelecting Specific Columns in a JoinSimplifying JOIN Syntax with Table AliasesJoining Multiple TablesPerforming Math on Joined Table ColumnsWrapping UpTry It Yourself
Naming Tables, Columns, and Other IdentifiersControlling Column Values with ConstraintsSpeeding Up Queries with IndexesWrapping UpTry It Yourself
Creating the Library Survey TablesExploring the Library Data Using Aggregate FunctionsWrapping UpTry It Yourself
Importing Data on Meat, Poultry, and Egg ProducersInterviewing the Data SetModifying Tables, Columns, and DataDeleting Unnecessary DataUsing Transaction Blocks to Save or Revert ChangesImproving Performance When Updating Large TablesWrapping UpTry It Yourself
Creating a Census Stats TableCreating Rankings with SQLCalculating Rates for Meaningful ComparisonsWrapping UpTry It Yourself
Data Types and Functions for Dates and TimesManipulating Dates and TimesWorking with Time ZonesCalculations with Dates and TimesWrapping UpTry It Yourself
Using SubqueriesCommon Table ExpressionsCross TabulationsReclassifying Values with CASEUsing CASE in a Common Table ExpressionWrapping UpTry It Yourself
Formatting Text Using String FunctionsMatching Text Patterns with Regular ExpressionsFull Text Search in PostgreSQLWrapping UpTry It Yourself
Installing PostGIS and Creating a Spatial DatabaseThe Building Blocks of Spatial DataTwo-Dimensional GeometriesPostGIS Data TypesCreating Spatial Objects with PostGIS FunctionsAnalyzing Farmers’ Markets DataWorking with Census ShapefilesPerforming Spatial JoinsWrapping UpTry It Yourself
Using Views to Simplify QueriesProgramming Your Own FunctionsAutomating Database Actions with TriggersWrapping UpTry It Yourself
Setting Up the Command Line for psqlWorking with psqlAdditional Command Line Utilities to Expedite TasksWrapping UpTry It Yourself
Recovering Unused Space with VACUUMChanging Server SettingsBacking Up and Restoring Your DatabaseWrapping UpTry It Yourself
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 UpTry It Yourself
PostgreSQL Development EnvironmentsPostgreSQL Utilities, Tools, and ExtensionsPostgreSQL NewsDocumentation

Content preview from Practical SQL

INTRODUCTION

Shortly after joining the staff of USA TODAY I received a data set I would analyze almost every week for the next decade. It was the weekly Best-Selling Books list, which ranked the nation’s top-selling books based on confidential sales data. The list not only produced an endless stream of story ideas to pitch, but it also captured the zeitgeist of America in a singular way.

For example, did you know that cookbooks sell a bit more during the week of Mother’s Day, or that Oprah Winfrey turned many obscure writers into number one best-selling authors just by having them on her show? Week after week, the book list editor and I pored ...