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

1CREATING YOUR FIRST DATABASE AND TABLE

SQL is more than just a means for extracting knowledge from data. It’s also a language for defining the structures that hold data so we can organize relationships in the data. Chief among those structures is the table.

A table is a grid of rows and columns that store data. Each row holds a collection of columns, and each column contains data of a specified type: most commonly, numbers, characters, and dates. We use SQL to define the structure of a table and how each table might relate to other tables in the database. We also use SQL to extract, or query, data from tables.

Understanding tables is fundamental ...