T-SQL Fundamentals
By Itzik Ben-Gan
Contents:
Acknowledgments
About the Author
Introduction
Chapter 1 Background to T-SQL querying and programming
Theoretical background
SQL
Set theory
Predicate logic
The relational model
Types of database workloads
SQL Server architecture
On-premises and cloud RDBMS flavors
SQL Server instances
Databases
Schemas and objects
Creating tables and defining data integrity
Creating tables
Defining data integrity
Conclusion
Chapter 2 Single-table queries
Elements of the SELECT statement
The FROM clause
The WHERE clause
The GROUP BY clause
The HAVING clause
The SELECT clause
The ORDER BY clause
The TOP and OFFSET-FETCH filters
A quick look at window functions
Predicates and operators
CASE expressions
NULLs
The GREATEST and LEAST functions
All-at-once operations
Working with character data
Data types
Collation
Operators and functions
The LIKE predicate
Working with date and time data
Date and time data types
Literals
Working with date and time separately
Filtering date ranges
Date and time functions
Querying metadata
Catalog views
Information schema views
System stored procedures and functions
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10
Chapter 3 Joins
Cross joins
SQL-92 syntax
SQL-89 syntax
Self cross joins
Producing tables of numbers
Inner joins
SQL-92 syntax
SQL-89 syntax
Inner join safety
More join examples
Composite joins
Non-equi joins
Multi-join queries
Outer joins
Outer joins, described
Including missing values
Filtering attributes from the nonpreserved side of
an outer join
Using outer joins in a multi-join query
Using the COUNT aggregate with outer joins
Conclusion
Exercises
Exercise 1-1
Exercise 1-2
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Solutions
Exercise 1-1
Exercise 1-2
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Chapter 4 Subqueries
Self-contained subqueries
Self-contained scalar subquery examples
Self-contained multivalued subquery examples
Correlated subqueries
The EXISTS predicate
Returning previous or next values
Using running aggregates
Dealing with misbehaving subqueries
NULL trouble
Substitution errors in subquery column names
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10
Chapter 5 Table expressions
Derived tables
Assigning column aliases
Using arguments
Multiple references
Common table expressions
Assigning column aliases in CTEs
Using arguments in CTEs
Defining multiple CTEs
Multiple references in CTEs
Recursive CTEs
Views
Views and the ORDER BY clause
View options
Inline table-valued functions
The APPLY operator
Conclusion
Exercises
Exercise 1
Exercise 2-1
Exercise 2-2
Exercise 3-1
Exercise 3-2
Exercise 4
Exercise 5-1
Exercise 5-2
Exercise 6-1
Exercise 6-2
Solutions
Exercise 1
Exercise 2-1
Exercise 2-2
Exercise 3-1
Exercise 3-2
Exercise 4
Exercise 5-1
Exercise 5-2
Exercise 6-1
Exercise 6-2
Chapter 6 Set operators
The UNION operator
The UNION ALL operator
The UNION (DISTINCT) operator
The INTERSECT operator
The INTERSECT (DISTINCT) operator
The INTERSECT ALL operator
The EXCEPT operator
The EXCEPT (DISTINCT) operator
The EXCEPT ALL operator
Precedence
Circumventing unsupported logical phases
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Chapter 7 T-SQL for data analysis
Window functions
Ranking window functions
Offset window functions
Aggregate window functions
The WINDOW clause
Pivoting data
Pivoting with a grouped query
Pivoting with the PIVOT operator
Unpivoting data
Unpivoting with the APPLY operator
Unpivoting with the UNPIVOT operator
Grouping sets
The GROUPING SETS subclause
The CUBE subclause
The ROLLUP subclause
The GROUPING and GROUPING_ID functions
Time series
Sample data
The DATE_BUCKET function
Custom computation of start of containing bucket
Applying bucket logic to sample data
Gap filling
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Chapter 8 Data modification
Inserting data
The INSERT VALUES statement
The INSERT SELECT statement
The INSERT EXEC statement
The SELECT INTO statement
The BULK INSERT statement
The identity property and the sequence object
Deleting data
The DELETE statement
The TRUNCATE statement
DELETE based on a join
Updating data
The UPDATE statement
UPDATE based on a join
Assignment UPDATE
Merging data
Modifying data through table expressions
Modifications with TOP and OFFSET-FETCH
The OUTPUT clause
INSERT with OUTPUT
DELETE with OUTPUT
UPDATE with OUTPUT
MERGE with OUTPUT
Nested DML
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Chapter 9 Temporal tables
Creating tables
Modifying data
Querying data
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Chapter 10 Transactions and concurrency
Transactions
Locks and blocking
Locks
Troubleshooting blocking
Isolation levels
The READ UNCOMMITTED isolation level
The READ COMMITTED isolation level
The REPEATABLE READ isolation level
The SERIALIZABLE isolation level
Isolation levels based on row versioning
Summary of isolation levels
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Chapter 11 SQL Graph
Creating tables
Traditional modeling
Graph modeling
Querying data
Using the MATCH clause
Recursive queries
Using the SHORTEST_PATH option
SQL Graph querying features that are still missing
Data modification considerations
Deleting and updating data
Merging data
Conclusion
Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Solutions
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Cleanup
Chapter 12 Programmable objects
Variables
Batches
A batch as a unit of parsing
Batches and variables
Statements that cannot be combined in the same
batch
A batch as a unit of resolution
The GO n option
Flow elements
The IF . . . ELSE flow element
The WHILE flow element
Cursors
Temporary tables
Local temporary tables
Global temporary tables
Table variables
Table types
Dynamic SQL
The EXEC command
The sp_executesql stored procedure
Using PIVOT with Dynamic SQL
Routines
User-defined functions
Stored procedures
Triggers
Error handling
Conclusion
Appendix: Getting started
Index