book

SQL Pocket Guide, 4th Edition

Name: SQL Pocket Guide, 4th Edition
Author: Alice Zhao
ISBN: 9781492090403

by Alice Zhao

August 2021

Beginner to intermediate

354 pages

4h 59m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Preface
Why SQL?Goals of This BookUpdates to the Fourth EditionNavigating This BookConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
1. SQL Crash Course
What Is a Database?SQLNoSQLDatabase Management Systems (DBMS)A SQL QueryThe SELECT StatementOrder of ExecutionA Data Model
2. Where Can I Write SQL Code?
RDBMS SoftwareSQLiteMySQLOraclePostgreSQLSQL ServerDatabase ToolsConnect a Database Tool to a DatabaseOther Programming LanguagesConnect Python to a DatabaseConnect R to a Database
3. The SQL Language
Comparison to Other LanguagesANSI StandardsSQL TermsKeywords and FunctionsIdentifiers and AliasesStatements and ClausesExpressions and PredicatesComments, Quotes, and WhitespaceSublanguages
4. Querying Basics
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
5. Creating, Updating, and Deleting
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
6. Data Types
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.)
7. Operators and Functions
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
8. Advanced Querying Concepts
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
9. Working with Multiple Tables and Queries
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

10. How Do I…?
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
Index
About the Author

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781492090397Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills