book

SQL Pocket Guide, 4th Edition

by Alice Zhao

August 2021

Beginner to intermediate

354 pages

4h 59m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Why SQL?Goals of This BookUpdates to the Fourth EditionNavigating This BookConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
What Is a Database?SQLNoSQLDatabase Management Systems (DBMS)A SQL QueryThe SELECT StatementOrder of ExecutionA Data Model
RDBMS SoftwareSQLiteMySQLOraclePostgreSQLSQL ServerDatabase ToolsConnect a Database Tool to a DatabaseOther Programming LanguagesConnect Python to a DatabaseConnect R to a Database
Comparison to Other LanguagesANSI StandardsSQL TermsKeywords and FunctionsIdentifiers and AliasesStatements and ClausesExpressions and PredicatesComments, Quotes, and WhitespaceSublanguages
The SELECT ClauseAliasing ColumnsQualifying ColumnsSelecting SubqueriesDISTINCTThe FROM ClauseFrom Multiple TablesFrom SubqueriesThe WHERE ClauseFiltering on SubqueriesThe GROUP BY ClauseThe HAVING ClauseThe ORDER BY ClauseThe LIMIT Clause
DatabasesDisplay Names of Existing DatabasesDisplay Name of Current DatabaseSwitch to Another DatabaseCreate a DatabaseDelete a DatabaseCreating TablesCreate a Simple TableDisplay Names of Existing TablesCreate a Table That Does Not Already ExistCreate a Table with ConstraintsCreate a Table with Primary and Foreign KeysCreate a Table with an Automatically Generated FieldInsert the Results of a Query into a TableModifying TablesRename a Table or ColumnDisplay, Add, and Delete ColumnsDisplay, Add, and Delete RowsDisplay, Add, Modify, and Delete ConstraintsUpdate a Column of DataUpdate Rows of DataUpdate Rows of Data with the Results of a QueryDelete a TableIndexesCreate an Index to Speed Up QueriesViewsCreate a View to Save the Results of a QueryTransaction ManagementDouble-Check Changes Before a COMMITUndo Changes with a ROLLBACK
How to Choose a Data TypeNumeric DataNumeric ValuesInteger Data TypesDecimal Data TypesString DataString ValuesCharacter Data TypesUnicode Data TypesDatetime DataDatetime ValuesDatetime Data TypesOther DataBoolean DataExternal Files (Images, Documents, etc.)
OperatorsLogical OperatorsComparison OperatorsMath OperatorsAggregate FunctionsNumeric FunctionsApply Math FunctionsGenerate Random NumbersRound and Truncate NumbersConvert Data to a Numeric Data TypeString FunctionsFind the Length of a StringChange the Case of a StringTrim Unwanted Characters Around a StringConcatenate StringsSearch for Text in a StringExtract a Portion of a StringReplace Text in a StringDelete Text from a StringUse Regular ExpressionsConvert Data to a String Data TypeDatetime FunctionsReturn the Current Date or TimeAdd or Subtract a Date or Time IntervalExtract a Part of a Date or TimeDetermine the Day of the Week of a DateRound a Date to the Nearest Time UnitConvert a String to a Datetime Data TypeNull FunctionsReturn an Alternative Value if There Is a Null Value
Case StatementsDisplay Values Based on If-Then Logic for a Single ColumnDisplay Values Based on If-Then Logic for Multiple ColumnsGrouping and SummarizingGROUP BY BasicsAggregate Rows into a Single Value or ListROLLUP, CUBE, and GROUPING SETSWindow FunctionsRank the Rows in a TableReturn the First Value in Each GroupReturn the Second Value in Each GroupReturn the First Two Values in Each GroupReturn the Prior Row ValueCalculate the Moving AverageCalculate the Running TotalPivoting and UnpivotingBreak Up the Values of a Column into Multiple ColumnsList the Values of Multiple Columns in a Single Column
Joining TablesJoin Basics and INNER JOINLEFT JOIN, RIGHT JOIN, and FULL OUTER JOINUSING and NATURAL JOINCROSS JOIN and Self JoinUnion OperatorsUNIONEXCEPT and INTERSECTCommon Table ExpressionsCTEs Versus SubqueriesRecursive CTEs

Find the Rows Containing Duplicate ValuesSelect Rows with the Max Value for Another ColumnConcatenate Text from Multiple Fields into a Single FieldFind All Tables Containing a Specific Column NameUpdate a Table Where the ID Matches Another Table

Overview

If you use SQL in your day-to-day work as a data analyst, data scientist, or data engineer, this popular pocket guide is your ideal on-the-job reference. You'll find many examples that address the language's complexities, along with key aspects of SQL used in Microsoft SQL Server, MySQL, Oracle Database, PostgreSQL, and SQLite.

In this updated edition, author Alice Zhao describes how these database management systems implement SQL syntax for both querying and making changes to a database. You'll find details on data types and conversions, regular expression syntax, window functions, pivoting and unpivoting, and more.

Quickly look up how to perform specific tasks using SQL
Apply the book's syntax examples to your own queries
Update SQL queries to work in five different database management systems
NEW: Connect Python and R to a relational database
NEW: Look up frequently asked SQL questions in the "How Do I?" chapter

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: 9781492090397Errata Page

SQL Pocket Guide, 4th Edition

by Alice Zhao

Overview

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

SQL Cookbook, 2nd Edition

Practical SQL, 2nd Edition

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

SQL in a Nutshell, 4th Edition

Publisher Resources

Overview

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

SQL Cookbook, 2nd Edition

Practical SQL, 2nd Edition

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

SQL in a Nutshell, 4th Edition

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.