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

10STATISTICAL FUNCTIONS IN SQL

A SQL database isn’t usually the first tool a data analyst chooses when performing statistical analysis that requires more than just calculating sums and averages. Typically, the software of choice would be full-featured statistics packages, such as SPSS or SAS, the programming languages R or Python, or even Excel. However, standard ANSI SQL, including PostgreSQL’s implementation, offers a handful of powerful stats functions that reveal a lot about your data without having to export your data set to another program.

In this chapter, we’ll explore these SQL stats functions along with guidelines on when to use them. ...

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.

Start your free trial

Publisher Resources

ISBN: 9781492067580

Practical SQL

by Anthony DeBarros

10STATISTICAL FUNCTIONS IN SQL

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.

You might also like

Practical SQL, 2nd Edition

Learning SQL

Learning SQL, 3rd Edition

Database Internals

Publisher Resources

10STATISTICAL FUNCTIONS IN SQL

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.

You might also like

Practical SQL, 2nd Edition

Learning SQL

Learning SQL, 3rd Edition

Database Internals

Publisher Resources

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.