SQL Pocket Guide: A Guide to SQL Usage

Copyright
Table of Contents
Preface
	Why SQL?
	Goals of This Book
	Updates to the Fourth Edition
	Navigating This Book
		I. Basic Concepts
		II. Database Objects, Data Types, and Functions
		III. Advanced Concepts
	Conventions Used in This Book
	Using Code Examples
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Chapter 1. SQL Crash Course
	What Is a Database?
		SQL
		NoSQL
		Database Management Systems (DBMS)
	A SQL Query
		SQL Statements
		SQL Queries
		The SELECT Statement
		Order of Execution
	A Data Model
Chapter 2. Where Can I Write SQL Code?
	RDBMS Software
		Which RDBMS to Choose?
		What Is a Terminal Window?
		SQLite
		MySQL
		Oracle
		PostgreSQL
		SQL Server
	Database Tools
		Connect a Database Tool to a Database
	Other Programming Languages
		Connect Python to a Database
		Connect R to a Database
Chapter 3. The SQL Language
	Comparison to Other Languages
	ANSI Standards
	SQL Terms
		Keywords and Functions
		Identifiers and Aliases
		Statements and Clauses
		Expressions and Predicates
		Comments, Quotes, and Whitespace
	Sublanguages
Chapter 4. Querying Basics
	The SELECT Clause
		Selecting Columns
		Selecting All Columns
		Selecting Expressions
		Selecting Functions
		Aliasing Columns
		Qualifying Columns
		Selecting Subqueries
		DISTINCT
	The FROM Clause
		From Multiple Tables
		From Subqueries
		Why Use a Subquery in the FROM Clause?
	The WHERE Clause
		Multiple Predicates
		Filtering on Subqueries
	The GROUP BY Clause
	The HAVING Clause
	The ORDER BY Clause
	The LIMIT Clause
Chapter 5. Creating, Updating, and Deleting
	Databases
		Data Model Versus Schema
		Display Names of Existing Databases
		Display Name of Current Database
		Switch to Another Database
		Create a Database
		Delete a Database
	Creating Tables
		Create a Simple Table
		Display Names of Existing Tables
		Create a Table That Does Not Already Exist
		Create a Table with Constraints
		Create a Table with Primary and Foreign Keys
		Create a Table with an Automatically Generated Field
		Insert the Results of a Query into a Table
		Insert Data from a Text File into a Table
	Modifying Tables
		Rename a Table or Column
		Display, Add, and Delete Columns
		Display, Add, and Delete Rows
		Display, Add, Modify, and Delete Constraints
		Update a Column of Data
		Update Rows of Data
		Update Rows of Data with the Results of a Query
		Delete a Table
	Indexes
		Book Index Versus SQL Index Comparison
		Create an Index to Speed Up Queries
	Views
		Create a View to Save the Results of a Query
	Transaction Management
		Double-Check Changes Before a COMMIT
		Undo Changes with a ROLLBACK
Chapter 6. Data Types
	How to Choose a Data Type
	Numeric Data
		Numeric Values
		Integer Data Types
		Decimal Data Types
		Floating Point Data Types
	String Data
		String Values
		Character Data Types
		Unicode Data Types
	Datetime Data
		Datetime Values
		Datetime Data Types
	Other Data
		Boolean Data
		External Files (Images, Documents, etc.)
Chapter 7. Operators and Functions
	Operators
		Logical Operators
		Comparison Operators
		Math Operators
	Aggregate Functions
	Numeric Functions
		Apply Math Functions
		Generate Random Numbers
		Round and Truncate Numbers
		Convert Data to a Numeric Data Type
	String Functions
		Find the Length of a String
		Change the Case of a String
		Trim Unwanted Characters Around a String
		Concatenate Strings
		Search for Text in a String
		Extract a Portion of a String
		Replace Text in a String
		Delete Text from a String
		Use Regular Expressions
		Convert Data to a String Data Type
	Datetime Functions
		Return the Current Date or Time
		Add or Subtract a Date or Time Interval
		Find the Difference Between Two Dates or Times
		Extract a Part of a Date or Time
		Determine the Day of the Week of a Date
		Round a Date to the Nearest Time Unit
		Convert a String to a Datetime Data Type
	Null Functions
		Return an Alternative Value if There Is a Null Value
Chapter 8. Advanced Querying Concepts
	Case Statements
		Display Values Based on If-Then Logic for a Single Column
		Display Values Based on If-Then Logic for Multiple Columns
	Grouping and Summarizing
		GROUP BY Basics
		Aggregate Rows into a Single Value or List
		ROLLUP, CUBE, and GROUPING SETS
	Window Functions
		Aggregate Function
		Window Function
		Rank the Rows in a Table
		Return the First Value in Each Group
		Return the Second Value in Each Group
		Return the First Two Values in Each Group
		Return the Prior Row Value
		Calculate the Moving Average
		Calculate the Running Total
	Pivoting and Unpivoting
		Break Up the Values of a Column into Multiple Columns
		List the Values of Multiple Columns in a Single Column
Chapter 9. Working with Multiple Tables and Queries
	Joining Tables
		Join Basics and INNER JOIN
		LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
		USING and NATURAL JOIN
		CROSS JOIN and Self Join
	Union Operators
		UNION
		EXCEPT and INTERSECT
	Common Table Expressions
		CTEs Versus Subqueries
		Recursive CTEs
Chapter 10. How Do I…?
	Find the Rows Containing Duplicate Values
		Return All Unique Combinations
		Return Only the Rows with Duplicate Values
	Select Rows with the Max Value for Another Column
	Concatenate Text from Multiple Fields into a Single Field
		Concatenate Text from Fields in a Single Row
		Concatenate Text from Fields in Multiple Rows
	Find All Tables Containing a Specific Column Name
	Update a Table Where the ID Matches Another Table
Index