You are previewing T-SQL Fundamentals, Third Edition.
O'Reilly logo
T-SQL Fundamentals, Third Edition

Book Description

Effectively query and modify data using Transact-SQL
Master T-SQL fundamentals and write robust code for Microsoft SQL Server and Azure SQL Database. Itzik Ben-Gan explains key T-SQL concepts and helps you apply your knowledge with hands-on exercises. The book first introduces T-SQL’s roots and underlying logic. Next, it walks you through core topics such as single-table queries, joins, subqueries, table expressions, and set operators. Then the book covers more-advanced data-query topics such as window functions, pivoting, and grouping sets. The book also explains how to modify data, work with temporal tables, and handle transactions, and provides an overview of programmable objects.


Microsoft Data Platform MVP Itzik Ben-Gan shows you how to:

  • Review core SQL concepts and its mathematical roots

  • Create tables and enforce data integrity

  • Perform effective single-table queries by using the SELECT statement

  • Query multiple tables by using joins, subqueries, table expressions, and set operators

  • Use advanced query techniques such as window functions, pivoting, and grouping sets

  • Insert, update, delete, and merge data

  • Use transactions in a concurrent environment

  • Get started with programmable objects–from variables and batches to user-defined functions, stored procedures, triggers, and dynamic SQL

  • Table of Contents

    1. Cover
    2. Title Page
    3. Copyright Page
    4. Dedication Page
    5. Contents at a glance
    6. Contents
    7. Introduction
      1. Who should read this book
        1. Assumptions
      2. This book might not be for you if...
      3. Organization of this book
      4. System requirements
      5. Installing and using the source code
      6. Acknowledgments
      7. Errata, updates, & book support
      8. Free ebooks from Microsoft Press
      9. We want to hear from you
      10. Stay in touch
    8. Chapter 1. Background to T-SQL querying and programming
      1. Theoretical background
        1. SQL
        2. Set theory
        3. Predicate logic
        4. The relational model
        5. Types of database systems
      2. SQL Server architecture
        1. The ABCs of Microsoft RDBMS flavors
        2. SQL Server instances
        3. Databases
        4. Schemas and objects
      3. Creating tables and defining data integrity
        1. Creating tables
        2. Defining data integrity
      4. Conclusion
    9. Chapter 2. Single-table queries
      1. Elements of the SELECT statement
        1. The FROM clause
        2. The WHERE clause
        3. The GROUP BY clause
        4. The HAVING clause
        5. The SELECT clause
        6. The ORDER BY clause
        7. The TOP and OFFSET-FETCH filters
        8. A quick look at window functions
      2. Predicates and operators
      3. CASE expressions
      4. NULLs
      5. All-at-once operations
      6. Working with character data
        1. Data types
        2. Collation
        3. Operators and functions
        4. The LIKE predicate
      7. Working with date and time data
        1. Date and time data types
        2. Literals
        3. Working with date and time separately
        4. Filtering date ranges
        5. Date and time functions
      8. Querying metadata
        1. Catalog views
        2. Information schema views
        3. System stored procedures and functions
      9. Conclusion
      10. Exercises
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
        7. Exercise 7
        8. Exercise 8
        9. Exercise 9
        10. Exercise 10
      11. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
        7. Exercise 7
        8. Exercise 8
        9. Exercise 9
        10. Exercise 10
    10. Chapter 3. Joins
      1. Cross joins
        1. ISO/ANSI SQL-92 syntax
        2. ISO/ANSI SQL-89 syntax
        3. Self cross joins
        4. Producing tables of numbers
      2. Inner joins
        1. ISO/ANSI SQL-92 syntax
        2. ISO/ANSI SQL-89 syntax
        3. Inner join safety
      3. More join examples
        1. Composite joins
        2. Non-equi joins
        3. Multi-join queries
      4. Outer joins
        1. Fundamentals of outer joins
        2. Beyond the fundamentals of outer joins
      5. Conclusion
      6. Exercises
        1. Exercise 1-1
        2. Exercise 1-2 (optional, advanced)
        3. Exercise 2
        4. Exercise 3
        5. Exercise 4
        6. Exercise 5
        7. Exercise 6
        8. Exercise 7 (optional, advanced)
        9. Exercise 8 (optional, advanced)
        10. Exercise 9 (optional, advanced)
      7. Solutions
        1. Exercise 1-1
        2. Exercise 1-2
        3. Exercise 2
        4. Exercise 3
        5. Exercise 4
        6. Exercise 5
        7. Exercise 6
        8. Exercise 7
        9. Exercise 8
        10. Exercise 9
    11. Chapter 4. Subqueries
      1. Self-contained subqueries
        1. Self-contained scalar subquery examples
        2. Self-contained multivalued subquery examples
      2. Correlated subqueries
        1. The EXISTS predicate
      3. Beyond the fundamentals of subqueries
        1. Returning previous or next values
        2. Using running aggregates
        3. Dealing with misbehaving subqueries
      4. Conclusion
      5. Exercises
        1. Exercise 1
        2. Exercise 2 (optional, advanced)
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
        7. Exercise 7 (optional, advanced)
        8. Exercise 8 (optional, advanced)
        9. Exercise 9
        10. Exercise 10 (optional, advanced)
      6. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
        7. Exercise 7
        8. Exercise 8
        9. Exercise 9
        10. Exercise 10
    12. Chapter 5. Table expressions
      1. Derived tables
        1. Assigning column aliases
        2. Using arguments
        3. Nesting
        4. Multiple references
      2. Common table expressions
        1. Assigning column aliases in CTEs
        2. Using arguments in CTEs
        3. Defining multiple CTEs
        4. Multiple references in CTEs
        5. Recursive CTEs
      3. Views
        1. Views and the ORDER BY clause
        2. View options
      4. Inline table-valued functions
      5. The APPLY operator
      6. Conclusion
      7. Exercises
        1. Exercise 1
        2. Exercise 2-1
        3. Exercise 2-2
        4. Exercise 3-1
        5. Exercise 3-2
        6. Exercise 4 (optional, advanced)
        7. Exercise 5-1
        8. Exercise 5-2 (optional, advanced)
        9. Exercise 6-1
        10. Exercise 6-2
      8. Solutions
        1. Exercise 1
        2. Exercise 2-1
        3. Exercise 2-2
        4. Exercise 3-1
        5. Exercise 3-2
        6. Exercise 4
        7. Exercise 5-1
        8. Exercise 5-2
        9. Exercise 6-1
        10. Exercise 6-2
    13. Chapter 6. Set operators
      1. The UNION operator
        1. The UNION ALL operator
        2. The UNION (DISTINCT) operator
      2. The INTERSECT Operator
        1. The INTERSECT (DISTINCT) operator
        2. The INTERSECT ALL operator
      3. The EXCEPT operator
        1. The EXCEPT (DISTINCT) operator
        2. The EXCEPT ALL operator
      4. Precedence
      5. Circumventing unsupported logical phases
      6. Conclusion
      7. Exercises
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6 (optional, advanced)
      8. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
    14. Chapter 7. Beyond the fundamentals of querying
      1. Window functions
        1. Ranking window functions
        2. Offset window functions
        3. Aggregate window functions
      2. Pivoting data
        1. Pivoting with a grouped query
        2. Pivoting with the PIVOT operator
      3. Unpivoting data
        1. Unpivoting with the APPLY operator
        2. Unpivoting with the UNPIVOT operator
      4. Grouping sets
        1. The GROUPING SETS subclause
        2. The CUBE subclause
        3. The ROLLUP subclause
        4. The GROUPING and GROUPING_ID functions
      5. Conclusion
      6. Exercises
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
      7. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
    15. Chapter 8. Data modification
      1. Inserting data
        1. The INSERT VALUES statement
        2. The INSERT SELECT statement
        3. The INSERT EXEC statement
        4. The SELECT INTO statement
        5. The BULK INSERT statement
        6. The identity property and the sequence object
      2. Deleting data
        1. The DELETE statement
        2. The TRUNCATE statement
        3. DELETE based on a join
      3. Updating data
        1. The UPDATE statement
        2. UPDATE based on a join
        3. Assignment UPDATE
      4. Merging data
      5. Modifying data through table expressions
      6. Modifications with TOP and OFFSET-FETCH
      7. The OUTPUT clause
        1. INSERT with OUTPUT
        2. DELETE with OUTPUT
        3. UPDATE with OUTPUT
        4. MERGE with OUTPUT
        5. Nested DML
      8. Conclusion
      9. Exercises
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
      10. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
        5. Exercise 5
        6. Exercise 6
    16. Chapter 9. Temporal tables
      1. Creating tables
      2. Modifying data
      3. Querying data
      4. Conclusion
      5. Exercises
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
      6. Solutions
        1. Exercise 1
        2. Exercise 2
        3. Exercise 3
        4. Exercise 4
    17. Chapter 10. Transactions and concurrency
      1. Transactions
      2. Locks and blocking
        1. Locks
        2. Troubleshooting blocking
      3. Isolation levels
        1. The READ UNCOMMITTED isolation level
        2. The READ COMMITTED isolation level
        3. The REPEATABLE READ isolation level
        4. The SERIALIZABLE isolation level
        5. Isolation levels based on row versioning
        6. Summary of isolation levels
      4. Deadlocks
      5. Conclusion
      6. Exercises
        1. Exercise 1-1
        2. Exercise 1-2
        3. Exercise 1-3
        4. Exercise 1-4
        5. Exercise 1-5
        6. Exercise 1-6
        7. Exercise 2-1
        8. Exercise 2-2
        9. Exercise 2-3
        10. Exercise 2-4
        11. Exercise 2-5
        12. Exercise 2-6
        13. Exercise 3-1
        14. Exercise 3-2
        15. Exercise 3-3
        16. Exercise 3-4
        17. Exercise 3-5
        18. Exercise 3-6
        19. Exercise 3-7
    18. Chapter 11. Programmable objects
      1. Variables
      2. Batches
        1. A batch as a unit of parsing
        2. Batches and variables
        3. Statements that cannot be combined in the same batch
        4. A batch as a unit of resolution
        5. The GO n option
      3. Flow elements
        1. The IF . . . ELSE flow element
        2. The WHILE flow element
      4. Cursors
      5. Temporary tables
        1. Local temporary tables
        2. Global temporary tables
        3. Table variables
        4. Table types
      6. Dynamic SQL
        1. The EXEC command
        2. The sp_executesql stored procedure
        3. Using PIVOT with Dynamic SQL
      7. Routines
        1. User-defined functions
        2. Stored procedures
        3. Triggers
      8. Error handling
      9. Conclusion
    19. Appendix: Getting started
      1. Getting started with Azure SQL Database
      2. Installing a SQL Server box product
        1. 1. Obtain SQL Server
        2. 2. Install the database engine
      3. Downloading and installing SQL Server Management Studio
      4. Downloading source code and installing the sample database
      5. Working with SQL Server Management Studio
      6. Working with SQL Server Books Online
    20. Index
    21. About the author
    22. Visit us today at
    23. Hear about it first
    24. Free ebooks
    25. Survey
    26. Code Snippets