Home Page Icon
Home Page
Table of Contents for
Dedication
Close
Dedication
by Scott Shaw, Kathi Kellenberger
Beginning T-SQL
Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Foreword
Chapter 1: Getting Started
Installing SQL Server Express Edition
Installing Books Online
Using Books Online
Using SQL Server Management Studio
Launching SQL Server Management Studio
Installing the Sample Databases
Get Started with SSMS
Summary
Chapter 2: Exploring Database Concepts
What Is SQL Server?
Databases in the Cloud
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
Filtering Data
Adding a WHERE Clause
Using WHERE Clauses with Alternate Operators
Using BETWEEN
UsingBETWEEN 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 and LTRIM
LEFT and RIGHT
LEN and DATALENGTH
CHARINDEX
SUBSTRING
CHOOSE
REVERSE
UPPER and LOWER
REPLACE
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
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
Solution to Exercise 4-3: Using Functions
Solution to Exercise 4-4: Using Date and Time Functions
Solution to Exercise 4-5: Using Mathematical Functions
Solution to Exercise 4-6: Using Logical and System Functions
Solution 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
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
Defining the Window
Dividing the Window into Partitions
Using NTILE
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 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
Thinking About Performance
Summary
Answers to the Exercises
Solutions to Exercise 9-1: Using LIKE
Solution to Exercise 9-2: Using WHERE Clauses with Three or More Predicates
Solution 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 Using EXISTS
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
Solution to Exercise 10-1: Inserting New Rows
Solution to Exercise 10-2: Deleting Rows
Solution to Exercise 10-3: Updating Existing Rows
Chapter 11: 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
Paging
Summary
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: 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 13-1: Writing an Explicit Transaction
Solutions to Exercise 13-2: Error Handling
Chapter 14: 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
CLR Integration
Thinking About Performance
Database Cleanup
Summary
Answers to the Exercises
Solutions to Exercise 14-1: Tables
Solution to Exercise 14-2: Views
Solution to Exercise 14-3: User-Defined Functions
Solution to Exercise 14-4: Stored Procedures
Chapter 15: Working with XML
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
Namespaces
Splitting a String
Summary
Chapter 16: 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
Thinking About Performance
Summary
Chapter 17: Running SQL Server in the Cloud
Procuring a Microsoft Azure Account
The Azure Dashboard
Windows Azure Virtual Machines
Azure SQL Database
Throttling
Database Size Limitations
Pricing
Summary
Chapter 18: Where to Go Next?
Online Resources
Conferences
User Groups
Vendors
Books
Classes
SQL Server Documentation
Practice, Practice, and More Practice
Teach Someone Else
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Copyright
Next
Next Chapter
Contents at a Glance
For my new little guy, Wyatt.
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset