Exam Ref 70-761 Querying Data with Transact-SQL, 1st Edition

✍ Scribed by Safari, an O'Reilly Media Company.; Ben-Gan, Itzik

• Publisher: Microsoft Press
• Year: 2017
• Tongue: English
• Leaves: 352
• Edition: 1st edition
• Category: Library

✦ Synopsis

This is a reference material to prepare for Microsoft Exam 70-761 and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, book focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.

Covered topics include the expertise measured by the objectives to filter, sort, join, aggregate and modify data, use subqueries, table expressions, grouping sets and pivoting, query temporal and non-relational data, read and output XML or JSON, create views, user-defined functions and stored procedures, implement error handling, transactions, data types and nulls.

✦ Table of Contents

Cover......Page 1
Title Page......Page 2
Copyright Page......Page 3
Contents......Page 8
Organization of this book......Page 12
Free ebooks from Microsoft Press......Page 13
Errata, updates, & book support......Page 14
Stay in touch......Page 15
Preparing for the exam......Page 16
Skill 1.1: Create Transact-SQL SELECT queries......Page 18
Understanding the foundations of T-SQL......Page 19
Understanding logical query processing......Page 27
Getting started with the SELECT statement......Page 34
Filtering data with predicates......Page 38
Sorting data......Page 45
Filtering data with TOP and OFFSET-FETCH......Page 50
Combining sets with set operators......Page 56
Skill 1.2: Query multiple tables by using joins......Page 62
Cross joins......Page 63
Inner joins......Page 65
Outer joins......Page 69
Queries with composite joins and NULLs in join columns......Page 75
Multi-join queries......Page 82
Skill 1.3: Implement functions and aggregate data......Page 84
Type conversion functions......Page 85
Date and time functions......Page 86
Character functions......Page 89
CASE expressions and related functions......Page 93
System functions......Page 96
Arithmetic operators and aggregate functions......Page 100
Search arguments......Page 103
Function determinism......Page 107
Inserting data......Page 110
Updating data......Page 117
Deleting data......Page 124
Merging data......Page 127
Using the OUTPUT option......Page 132
Impact of structural changes on data......Page 138
Chapter summary......Page 141
Thought experiment......Page 142
Thought experiment answer......Page 143
Skill 2.1: Query data by using subqueries and APPLY......Page 146
Subqueries......Page 147
The APPLY operator......Page 154
Skill 2.2: Query data by using table expressions......Page 158
Table expressions or temporary tables?......Page 159
Derived tables......Page 160
Common table expressions......Page 163
Views and inline table-valued functions......Page 165
Skill 2.3: Group and pivot data by using queries......Page 167
Writing grouped queries......Page 168
Pivoting and Unpivoting Data......Page 177
Using Window Functions......Page 184
Skill 2.4: Query temporal data and non-relational data......Page 193
System-versioned temporal tables......Page 194
Query and output XML data......Page 209
Query and output JSON data......Page 222
Chapter summary......Page 233
Thought experiment answer......Page 235
Skill 3.1: Create database programmability objects by using Transact-SQL......Page 238
Views......Page 239
User-defined functions......Page 254
Stored procedures......Page 267
Skill 3.2: Implement error handling and transactions......Page 280
Understanding transactions......Page 281
Error handling with TRY-CATCH......Page 299
Working with data types......Page 323
Handling NULLs......Page 331
Chapter summary......Page 338
Thought experiment......Page 339
Thought experiment answer......Page 340
C......Page 342
D......Page 343
E......Page 344
I......Page 345
M......Page 346
O......Page 347
R......Page 348
S......Page 349
T......Page 350
X......Page 351
About the author......Page 352

✦ Subjects

Nonfiction;Reference;Computer Science;Programming