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

352 Pages·2017·5.73 MB·English

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

Exam Ref 70-761
Querying Data with
Transact-SQL
Itzik Ben-Gan
Exam Ref 70-761 Querying Data with Transact-SQL
Published with the authorization of Microsoft Corporation by:
Pearson Education, Inc.
Copyright © 2017 by Itzik Ben-Gan
All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must
be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any
form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions,
request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit
www.pearsoned.com/permissions/. No patent liability is assumed with respect to the use of the information contained herein.
Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for
errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
ISBN-13: 978-1-5093-0433-2
ISBN-10: 1-5093-0433-9
Library of Congress Control Number: 2017935711
First Printing April 2017
Trademarks
Microsoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarks of the
Microsoft group of companies. All other marks are property of their respective owners.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is
implied. The information provided is on an “as is” basis. The authors, the publisher, and Microsoft Corporation shall have
neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information
contained in this book or programs accompanying it.
Special Sales
For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic
versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding
interests), please contact our corporate sales department at [email protected] or [phone].
For government sales inquiries, please contact [email protected].
For questions about sales outside the U.S., please contact [email protected].
Editor-in-Chief Greg Wiegand
Acquisitions Editor Trina MacDonald
Development Editor Troy Mott
Managing Editor Sandra Schroeder
Senior Project Editor Tracey Croom
Editorial Production Backstop Media
Copy Editor Christina Rudloff
Indexer Julie Grady
Proofreader Christina Rudloff
Technical Editor Dejan Sarka
Cover Designer Twist Creative, Seattle
In memory of my dad, Gabriel Ben-Gan, who appreciated the
beauty of numbers, logic and puzzles.
—ItzIk
This page intentionally left blank
Contents at a glance
Introduction xi
Preparing for the exam xv
CHAPTER 1 Manage data with Transact-SQL 1
CHAPTER 2 Query data with advanced Transact-SQL components 129
CHAPTER 3 Program databases by using Transact-SQL 221
Index 325
This page intentionally left blank
Contents
Introduction xi
Organization of this book ..........................................xi
Microsoft certifications ...........................................xii
Acknowledgments ...............................................xii
Free ebooks from Microsoft Press ..................................xii
Microsoft Virtual Academy ........................................xiii
Quick access to online references ..................................xiii
Errata, updates, & book support ...................................xiii
We want to hear from you ........................................xiv
Stay in touch ....................................................xiv
Preparing for the exam xv
Chapter 1 Manage data with Transact-SQL 1
Skill 1.1: Create Transact-SQL SELECT queries .........................1
Understanding the foundations of T-SQL 2
Understanding logical query processing 10
Getting started with the SELECT statement 17
Filtering data with predicates 21
Sorting data 28
Filtering data with TOP and OFFSET-FETCH 33
Combining sets with set operators 39
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our
books and learning resources for you. To participate in a brief online survey, please visit:
https://aka.ms/tellpress
vii
Skill 1.2: Query multiple tables by using joins .......................45
Cross joins 46
Inner joins 48
Outer joins 52
Queries with composite joins and NULLs in join columns 58
Multi-join queries 65
Skill 1.3: Implement functions and aggregate data ...................67
Type conversion functions 68
Date and time functions 69
Character functions 72
CASE expressions and related functions 76
System functions 79
Arithmetic operators and aggregate functions 83
Search arguments 86
Function determinism 90
Skill 1.4: Modify data .............................................93
Inserting data 93
Updating data 100
Deleting data 107
Merging data 110
Using the OUTPUT option 115
Impact of structural changes on data 121
Chapter summary ...............................................124
Thought experiment .............................................125
Thought experiment answer ......................................126
Chapter 2 Query data with advanced Transact-SQL components 129
Skill 2.1: Query data by using subqueries and APPLY ................129
Subqueries 130
The APPLY operator 137
Skill 2.2: Query data by using table expressions ....................141
Table expressions, described 142
Table expressions or temporary tables? 142
Derived tables 143
viii Contents
Common table expressions 146
Views and inline table-valued functions 148
Skill 2.3: Group and pivot data by using queries ....................150
Writing grouped queries 151
Pivoting and Unpivoting Data 160
Using Window Functions 167
Skill 2.4: Query temporal data and non-relational data ..............176
System-versioned temporal tables 177
Query and output XML data 192
Query and output JSON data 205
Chapter summary ...............................................216
Thought experiment .............................................218
Thought experiment answer ......................................218
Chapter 3 Program databases by using Transact-SQL 221
Skill 3.1: Create database programmability objects by
using Transact-SQL ...........................................221
Views 222
User-defined functions 237
Stored procedures 250
Skill 3.2: Implement error handling and transactions ................263
Understanding transactions 264
Error handling with TRY-CATCH 282
Skill 3.3: Implement data types and NULLs .........................306
Working with data types 306
Handling NULLs 314
Chapter summary ...............................................321
Thought experiment ...........................................322
Thought experiment answer ......................................323
Index 325
Contents ix