Sql server® 2012 t-sql fundamentals

Document Outline

• Foreword
• Introduction
• Background to T-SQL Querying and Programming
  • Theoretical Background
    • SQL
    • Set Theory
    • Predicate Logic
    • The Relational Model
    • The Data Life Cycle
  • SQL Server Architecture
    • The ABC Flavors of SQL Server
    • SQL Server Instances
    • Databases
    • Schemas and Objects
  • Creating Tables and Defining Data Integrity
    • Creating Tables
    • Defining Data Integrity
  • Conclusion
• 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
  • NULL Marks
  • 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
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
  • Solutions
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
• Joins
  • Cross Joins
    • ANSI SQL-92 Syntax
    • ANSI SQL-89 Syntax
    • Self Cross Joins
    • Producing Tables of Numbers
  • Inner Joins
    • ANSI SQL-92 Syntax
    • ANSI SQL-89 Syntax
    • Inner Join Safety
  • More Join Examples
    • Composite Joins
    • Non-Equi Joins
    • Multi-Join Queries
  • Outer Joins
    • Fundamentals of Outer Joins
    • Beyond the Fundamentals of Outer Joins
  • Conclusion
  • Exercises
    • 1-1
    • 1-2 (Optional, Advanced)
    • 2
    • 3
    • 4
    • 5
    • 6 (Optional, Advanced)
    • 7 (Optional, Advanced)
  • Solutions
    • 1-1
    • 1-2
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
• Subqueries
  • Self-Contained Subqueries
    • Self-Contained Scalar Subquery Examples
    • Self-Contained Multivalued Subquery Examples
  • Correlated Subqueries
    • The EXISTS Predicate
  • Beyond the Fundamentals of Subqueries
    • Returning Previous or Next Values
    • Using Running Aggregates
    • Dealing with Misbehaving Subqueries
  • Conclusion
  • Exercises
    • 1 
    • 2 (Optional, Advanced)
    • 3
    • 4
    • 5
    • 6
    • 7 (Optional, Advanced)
    • 8 (Optional, Advanced)
  • Solutions
    • 1 
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
• Table Expressions
  • Derived Tables
    • Assigning Column Aliases
    • Using Arguments
    • Nesting
    • 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 
    • 1-1
    • 1-2
    • 2-1
    • 2-2
    • 3 (Optional, Advanced)
    • 4-1
    • 4-2 (Optional, Advanced)
    • 5-1
    • 5-2
  • Solutions
    • 1-1
    • 1-2
    • 2-1
    • 2-2
    • 3
    • 4-1
    • 4-2
    • 5-1
    • 5-2
• Set Operators
  • The UNION Operator
    • The UNION ALL Multiset Operator
    • The UNION Distinct Set Operator
  • The INTERSECT Operator
    • The INTERSECT Distinct Set Operator
    • The INTERSECT ALL Multiset Operator
  • The EXCEPT Operator
    • The EXCEPT Distinct Set Operator
    • The EXCEPT ALL Multiset Operator
  • Precedence
  • Circumventing Unsupported Logical Phases
  • Conclusion
  • Exercises
    • 1
    • 2
    • 3
    • 4
    • 5 (Optional, Advanced)
  • Solutions
    • 1
    • 2
    • 3
    • 4
    • 5
• Beyond the Fundamentals of Querying
  • Window Functions
    • Ranking Window Functions
    • Offset Window Functions
    • Aggregate Window Functions
  • Pivoting Data
    • Pivoting with Standard SQL
    • Pivoting with the Native T-SQL PIVOT Operator
  • Unpivoting Data
    • Unpivoting with Standard SQL
    • Unpivoting with the Native T-SQL UNPIVOT Operator
  • Grouping Sets
    • The GROUPING SETS Subclause
    • The CUBE Subclause
    • The ROLLUP Subclause
    • The GROUPING and GROUPING_ID Functions
  • Conclusion
  • Exercises
    • 1
    • 2
    • 3
    • 4
    • 5
  • Solutions
    • 1
    • 2
    • 3
    • 4
    • 5
• 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
    • Composable DML
  • Conclusion
  • Exercises
    • 1
    • 1-1
    • 1-2
    • 1-3
    • 2
    • 3
    • 4
    • 5
    • 6
  • Solutions
    • 1-1
    • 1-2
    • 1-3
    • 2
    • 3
    • 4
    • 5
• 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
  • Deadlocks
  • Conclusion
  • Exercises
    • 1-1
    • 1-2
    • 1-3
    • 1-4
    • 1-5
    • 1-6
    • 2-1
    • 2-2
    • 2-3
    • 2-4
    • 2-5
    • 2-6
    • 3-1
    • 3-2
    • 3-3
    • 3-4
    • 3-5
    • 3-6
    • 3-7
• 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
    • An Example of Using IF and WHILE
  • 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
• Getting Started
  • Getting Started with SQL Database
  • Installing an On-Premises Implementation of SQL Server
    • 1. Obtain SQL Server
    • 2. Create a User Account
    • 3. Install Prerequisites
    • 4. Install the Database Engine, Documentation, and Tools
  • Downloading Source Code and Installing the Sample Database
  • Working with SQL Server Management Studio
  • Working with SQL Server Books Online
• Index
• About the Author