## 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