Beginning T-SQL: A Step-by-Step Approach

Table of Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Getting Started
	Installing SQL Server Developer Edition
	Finding Help for SQL Server
	Download Sample Databases
	Installing Tools for SQL Server
	Using Azure Data Studio
	Summary
Chapter 2: Exploring Database Concepts
	What Is SQL Server?
	Service vs. Application
	Database as Container
	Data Is Stored in Tables
	Data Types
	Normalization
	Understanding Indexes
	Database Schemas
	Summary
Chapter 3: Writing Simple SELECT Queries
	Using the SELECT Statement
		Selecting a Literal Value
		Retrieving from a Table
		Generating a SELECT List
		Mixing Literals and Column Names
	IntelliSense
	Formatting T-SQL Code
		Can the Statement Be Written on One Line?
		Must Keywords Be Uppercase?
		Are Column and Table Names Case Sensitive?
		Is the Semicolon Important?
		Why Use Aliases?
		When to Use Single Quotes, Double Quotes, or Square Brackets?
	Filtering Data
		Adding a WHERE Clause
		Using WHERE Clauses with Alternate Operators
		Using BETWEEN
		Using BETWEEN with NOT
		Filtering on Date and Time
		Using WHERE Clauses with Two Predicates
		Using the IN Operator
	Working with NULL
	Sorting Data
	Thinking About Performance
		Taking Advantage of Indexes
		Viewing Execution Plans
	Summary
	Answers to the Exercises
		Solutions to Exercise 3-1: Using the SELECT Statement
		Solutions to Exercise 3-2: Filtering Data
		Solutions to Exercise 3-3: Using WHERE Clauses with Two Predicates
		Solutions to Exercise 3-4: Working with NULL
		Solutions to Exercise 3-5: Sorting Data
Chapter 4: Using Built-in Functions and Expressions
	Expressions Using Operators
		Concatenating Strings
		Concatenating Strings and NULL
		CONCAT
		ISNULL and COALESCE
		Concatenating Other Data Types to Strings
	Using Mathematical Operators
	Using String Functions
		RTRIM, LTRIM, and TRIM
		LEFT and RIGHT
		LEN and DATALENGTH
		CHARINDEX
		SUBSTRING
		CHOOSE
		REVERSE
		UPPER and LOWER
		REPLACE
		STRING_SPLIT and STRING_AGG
	Nesting Functions
	Using Date and Time Functions
		GETDATE and SYSDATETIME
		DATEADD
		DATEDIFF
		DATENAME and DATEPART
		DAY, MONTH, and YEAR
		CONVERT
		FORMAT
		DATEFROMPARTS
		EOMONTH
	Using Mathematical Functions
		ABS
		POWER
		SQUARE and SQRT
		ROUND
		RAND
	Logical Functions and Expressions
		The CASE Expression
			Simple CASE
			Searched CASE
			Listing a Column as the Return Value
		IIF
		COALESCE
	Administrative Functions
	Using Functions in the WHERE and ORDER BY Clauses
	The TOP Keyword
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 4-1: Expressions Using Operators
		Solutions to Exercise 4-2: Using Mathematical Operators
		Solutions to Exercise 4-3: Using Functions
		Solutions to Exercise 4-4: Using Date and Time Functions
		Solutions to Exercise 4-5: Using Mathematical Functions
		Solutions to Exercise 4-6: Using Logical and System Functions
		Solutions to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses
Chapter 5: Joining Tables
	Using INNER JOIN
		Joining Two Tables
		Avoiding an Incorrect Join Condition
		Joining on a Different Column Name
		Joining on More Than One Column
		Joining Three or More Tables
	Using OUTER JOIN
		Using LEFT OUTER JOIN
		Using RIGHT OUTER JOIN
		Using OUTER JOIN to Find Rows with No Match
		Adding a Table to the Right Side of a LEFT JOIN
		Adding a Table to the Main Table of a LEFT JOIN
		FULL OUTER JOIN
		CROSS JOIN
		Self-Joins
	Thinking About Performance
		Merge Join
		Nested Loop
		Hash Match
	Summary
	Answers to the Exercises
		Solutions to Exercise 5-1: Using INNER JOIN
		Solutions to Exercise 5-2: Using OUTER JOIN
Chapter 6: Building on Subqueries, Common Table Expressions, and Unions
	Writing Subqueries
		Using a Subquery in an IN List
		Using a Subquery and NOT IN
		Using a Subquery Containing NULL with NOT IN
		Using EXISTS
		Using CROSS APPLY and OUTER APPLY
		Writing UNION Queries
		Using EXCEPT and INTERSECT
	Using Derived Tables and Common Table Expressions
		Using Derived Tables
		Using Common Table Expressions
		Using a Common Table Expression to Solve a Complicated Join Problem
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 6-1: Using Subqueries
		Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions
Chapter 7: Grouping and  Summarizing Data
	Aggregate Functions
	The GROUP BY Clause
		Grouping on Columns
		Grouping on Expressions
	The ORDER BY Clause
	The WHERE Clause
	The HAVING Clause
	Order of Operations
	DISTINCT Keyword
		Using DISTINCT vs. GROUP BY
		DISTINCT Within an Aggregate Expression
	Aggregate Queries with More Than One Table
	Aggregate Functions and NULL
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 7-1: Aggregate Functions
		Solutions to Exercise 7-2: The GROUP BY Clause
		Solutions to Exercise 7-3: The HAVING Clause
		Solutions to Exercise 7-4: DISTINCT Keyword
		Solutions to Exercise 7-5: Aggregate Queries with More Than One Table
Chapter 8: Discovering Windowing Functions
	What Is a Windowing Function?
	Ranking Functions and the OVER Clause
		Defining the Window
		Using NTILE
		Dividing the Window into Partitions
	Summarizing Results with Window Aggregates
	Defining the Window with Framing
	Calculating Running Totals
	Understanding the Difference Between ROWS and RANGE
	Using Window Analytic Functions
		LAG and LEAD
		FIRST_VALUE and LAST_VALUE
		PERCENT_RANK and CUME_DIST
		PERCENTILE_CONT and PERCENTILE_DISC
	Applying Windowing Functions
		Removing Duplicates
		Solving an Islands Problem
	Thinking About Performance
		Indexing
		The Trouble with Window Aggregates
		Framing
	Summary
	Answers to the Exercises
		Solutions to Exercise 8-1: Ranking Functions
		Solutions to Exercise 8-2: Summarizing Results with Window Aggregates
		Solutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGE
		Solutions to Exercise 8-4: Using Window Analytic Functions
Chapter 9: Advanced WHERE Clauses
	Pattern Matching
	Using LIKE
		Restricting the Characters in Pattern Matches
		Searching for Wildcards
		Combining Wildcards
	Using PATINDEX
	Using SOME, ANY, and ALL
	Using WHERE Clauses with Three or More Predicates
		Using NOT with Parentheses
	Performing a Full-Text Search
		Using CONTAINS
		Using Multiple Terms with CONTAINS
		Searching Multiple Columns
		Using FREETEXT
		Using FREETEXTTABLE
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 9-1: Using LIKE
		Solutions to Exercise 9-2: Using WHERE Clauses with Three or More Predicates
		Solutions to Exercise 9-3: Performing a Full-Text Search
Chapter 10: Manipulating Data
	Inserting New Rows
		Adding One Row with Literal Values
		Avoiding Common Insert Errors
		Inserting Multiple Rows with One Statement
		Inserting Rows from Another Table
		Inserting Missing Rows
		Creating and Populating a Table in One Statement
		Inserting Rows into Tables with Default Column Values
		Inserting Rows into Tables with Automatically Populating Columns
	Deleting Rows
		Using DELETE
		Deleting from a Table in a JOIN
		Truncating
	Updating Existing Rows
		Using the UPDATE Statement
		Updating Data with Expressions and Columns
		Updating with a Join
	Thinking About Performance
		Database Cleanup
	Summary
	Answers to the Exercises
		Solutions to Exercise 10-1: Inserting New Rows
		Solutions to Exercise 10-2: Deleting Rows
		Solutions to Exercise 10-3: Updating Existing Rows
Chapter 11: Managing Transactions
	ACID Properties
	Writing an Explicit Transaction
		Rolling Back a Transaction
		Using the XACT_ABORT Setting
	Error Handling
		Using TRY . . . CATCH
		Viewing Untrappable Errors
		Using RAISERROR
		Using TRY . . . CATCH with Transactions
		Using THROW Instead of RAISERROR
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 11-1: Writing an Explicit Transaction
		Solutions to Exercise 11-2: Error Handling
Chapter 12: Understanding T-SQL Programming Logic
	Variables
		Declaring and Initializing a Variable
		Using Expressions and Functions with Variables
		Using Variables in WHERE and HAVING Clauses
	The IF...ELSE Construct
		Using IF
		Using ELSE
		Using Multiple Conditions
		Nesting IF…ELSE
		Using IF with a Query
	WHILE
		Using a WHILE Loop
		Nesting WHILE Loops
		Exiting a Loop Early
		Using CONTINUE
	Temporary Tables and Table Variables
		Creating Local Temp Tables
		Creating Global Temp Tables
		Creating Table Variables
		Using a Temp Table or Table Variable
		Using a Temp Table or Table Variable Like an Array
		Using a Cursor
	Thinking About Performance
	Summary
	Answers to the Exercises
		Solutions to Exercise 12-1: Variables
		Solutions to Exercise 12-2: The IF...ELSE Construct
		Solutions to Exercise 12-3: WHILE
		Solutions to Exercise 12-4: Temporary Tables and Table Variables
Chapter 13: Implementing Logic in the Database
	Tables
		Adding Check Constraints to a Table
		Adding UNIQUE Constraints
		Adding a Primary Key to a Table
		Creating Foreign Keys
		Creating Foreign Keys with Delete and Update Rules
		Defining Automatically Populated Columns
	Views
		Creating Views
		Avoiding Common Problems with Views
		Manipulating Data with Views
	User-Defined Functions
		Creating User-Defined Scalar Functions
		Using Table-Valued User-Defined Functions
	Stored Procedures
		Using Default Values with Parameters
		Using the OUTPUT Parameter
		Saving the Results of a Stored Procedure in a Table
		Using a Logic in Stored Procedures
	User-Defined Data Types
	Table Types
	Triggers
	Thinking About Performance
	Database Cleanup
	Summary
	Answers to the Exercises
		Solutions to Exercise 13-1: Tables
		Solutions to Exercise 13-2: Views
		Solutions to Exercise 13-3: User-Defined Functions
		Solutions to Exercise 13-4: Stored Procedures
Chapter 14: Expanding on Data Type Concepts
	Large-Value String Data Types (MAX)
	Large-Value Binary Data Types
		Creating VARBINARY(MAX) Data
		Using FILESTREAM
		FileTables
	Enhanced Date and Time
		Using DATE, TIME, and DATETIME2
		Using DATETIMEOFFSET
	HIERARCHYID
		Viewing HIERARCHYID
		Creating a Hierarchy
		Using Stored Procedures to Manage Hierarchical Data
	Spatial Data Types
		Using GEOMETRY
		Using GEOGRAPHY
		Viewing the Spatial Results Tab
		Circular Arcs
	Sparse Columns
	Graph Databases
	Thinking About Performance
	Summary
Chapter 15: Working with XML and JSON
	The Parts of XML
	Converting XML Using OPENXML
	Retrieving Data as XML Using the FOR XML Clause
		FOR XML RAW
		FOR XML AUTO
		FOR XML EXPLICIT
		FOR XML PATH
	The XML Data Type
	XML Methods
		The QUERY Method
		The VALUE Method
		The EXIST Method
		The MODIFY Method
		The NODES Method
	JSON Data
		Returning JSON Data
		Inserting JSON Data
		Shredding JSON Data
	Summary
Chapter 16: Writing Advanced Queries
	Advanced CTE Queries
		Alternate CTE Syntax
		Using Multiple CTEs
		Referencing a CTE Multiple Times
		Joining a CTE to Another CTE
		Writing a Recursive Query
		Data Manipulation with CTEs
	Isolating Aggregate Query Logic
		Correlated Subqueries in the SELECT List
		Using Derived Tables
		Common Table Expressions
		Using CROSS APPLY and OUTER APPLY
	The OUTPUT Clause
		Using OUTPUT to View Data
		Saving OUTPUT Data to a Table
	The MERGE Statement
	GROUPING SETS
	CUBE and ROLLUP
	Pivoted Queries
		Pivoting Data with CASE
		Using the PIVOT Function
		Using the UNPIVOT Function
	Temporal Tables
	Paging
	Summary
Chapter 17: Where to Go Next?
	Online Resources
	Conferences
	User Groups
	Vendors
	Books
	Classes
	SQL Server Documentation
	Practice, Practice, and More Practice
	Teach Someone Else
Appendix A: SQL Server for Linux and macOS
	Installing a SQL Server Instance in a Container
	Copy Sample Databases to the Container
	Restore Sample Databases
Appendix B: Using SSMS
	Installing SQL Server Management Studio
	Launching SQL Server Management Studio
	Installing the Sample Databases
	Get Started with SSMS
	Summary
Appendix C: SQL Notebooks
	Opening a SQL Notebook
	Adding Code to the Notebook
	Adding a Text Cell
	Organizing Your Notebooks
	Summary
Index