Transcription
T-SQL Fundamentals,Third EditionItzik Ben-Gan
PUBLISHED BYMicrosoft PressA division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright 2016 by Itzik Ben-GanAll rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by anymeans without the written permission of the publisher.Library of Congress Control Number: 2015955815ISBN: 978-1-5093-0200-0Printed and bound in the United States of America.First PrintingMicrosoft Press books are available through booksellers and distributors worldwide. If you need support relatedto this book, email Microsoft Press Support at mspinput@microsoft.com. Please tell us what you think of thisbook at http://aka.ms/tellpress.This book is provided “as-is” and expresses the author’s views and opinions. The views, opinions and informationexpressed in this book, including URL and other Internet website references, may change without notice.Some examples depicted herein are provided for illustration only and are fictitious. No real association orconnection is intended or should be inferred.Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are trademarksof the Microsoft group of companies. All other marks are property of their respective owners.Acquisitions Editor: Devon MusgraveDevelopmental Editor: Carol DillinghamProject Editor: Carol DillinghamEditorial Production: Christian Holdener; S4Carlisle Publishing ServicesTechnical Reviewer: Bob Beauchemin; Technical Review services provided by Content Master, a member ofCM Group, Ltd.Copyeditor: Roger LeblancIndexer: Maureen Johnson, MoJo’s Indexing ServicesCover: Twist Creative Seattle and Joel Panchot
To Dato,To live in hearts we leave behind,Is not to die.—THOMAS CAMPBELL
This page intentionally left blank
Contents at a glanceIntroductionxixCHAPTER 1Background to T-SQL querying and programming1CHAPTER 2Single-table queriesCHAPTER 3Joins103CHAPTER 4Subqueries133CHAPTER 5Table expressions161CHAPTER 6Set operators193CHAPTER 7Beyond the fundamentals of querying213CHAPTER 8Data modification249CHAPTER 9Temporal tables297CHAPTER 10Transactions and concurrency319CHAPTER 11Programmable objects361Appendix: Getting started395Index41527
This page intentionally left blank
ContentsIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xixChapter 1Background to T-SQL querying and programming1Theoretical background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Set theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Predicate logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4The relational model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4Types of database systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10SQL Server architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12The ABCs of Microsoft RDBMS flavors. . . . . . . . . . . . . . . . . . . . . . . . . . . . 12SQL Server instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Schemas and objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18Creating tables and defining data integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Creating tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Defining data integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Chapter 2Single-table queries27Elements of the SELECT statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27The FROM clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29The WHERE clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31The GROUP BY clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32The HAVING clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35The SELECT clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36What do you think of this book? We want to hear from you!Microsoft is interested in hearing your feedback so we can improve our books and learning resources foryou. To participate in a brief survey, please visit:http://aka.ms/tellpressvii
The ORDER BY clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41The TOP and OFFSET-FETCH filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43A quick look at window functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Predicates and operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49CASE expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52NULLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54All-at-once operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Working with character data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Collation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61Operators and functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63The LIKE predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71Working with date and time data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73Date and time data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Working with date and time separately . . . . . . . . . . . . . . . . . . . . . . . . . . 78Filtering date ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Date and time functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80Querying metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Catalog views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Information schema views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91System stored procedures and functions . . . . . . . . . . . . . . . . . . . . . . . . . 91Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Exercise 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Exercise 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Exercise 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Exercise 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97viiiContents
Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Exercise 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Exercise 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Exercise 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Exercise 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Chapter 3Joins103Cross joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103ISO/ANSI SQL-92 syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104ISO/ANSI SQL-89 syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105Self cross joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105Producing tables of numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106Inner joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107ISO/ANSI SQL-92 syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107ISO/ANSI SQL-89 syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108Inner join safety . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109More join examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109Composite joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110Non-equi joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111Multi-join queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Outer joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Fundamentals of outer joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114Beyond the fundamentals of outer joins . . . . . . . . . . . . . . . . . . . . . . . . 116Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123Exercise 1-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123Exercise 1-2 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Contentsix
Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Exercise 7 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Exercise 8 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Exercise 9 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Exercise 1-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Exercise 1-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Exercise 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Exercise 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Exercise 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Chapter 4Subqueries133Self-contained subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Self-contained scalar subquery examples . . . . . . . . . . . . . . . . . . . . . . . 134Self-contained multivalued subquery examples . . . . . . . . . . . . . . . . . 136Correlated subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139The EXISTS predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Beyond the fundamentals of subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143Returning previous or next values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143Using running aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Dealing with misbehaving subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . 145Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150Exercise 2 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151xContents
Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152Exercise 7 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Exercise 8 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Exercise 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Exercise 10 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Exercise 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Exercise 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Exercise 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Exercise 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158Chapter 5Table expressions161Derived tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161Assigning column aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163Using arguments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164Nesting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Multiple references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Common table expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Assigning column aliases in CTEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Using arguments in CTEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168Defining multiple CTEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168Multiple references in CTEs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169Recursive CTEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Views and the ORDER BY clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172View options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Contentsxi
Inline table-valued functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178The APPLY operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183Exercise 2-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184Exercise 2-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184Exercise 3-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184Exercise 3-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Exercise 4 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Exercise 5-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186Exercise 5-2 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Exercise 6-1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Exercise 6-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Exercise 2-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189Exercise 2-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189Exercise 3-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189Exercise 3-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190Exercise 5-1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190Exercise 5-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191Exercise 6-1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191Exercise 6-2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192Chapter 6Set operators193The UNION operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194The UNION ALL operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194The UNION (DISTINCT) operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195The INTERSECT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196The INTERSECT (DISTINCT) operator . . . . . . . . . . . . . . . . . . . . . . . . . . . 196The INTERSECT ALL operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197The EXCEPT operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199xiiContents
The EXCEPT (DISTINCT) operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199The EXCEPT ALL operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201Circumventing unsupported logical phases . . . . . . . . . . . . . . . . . . . . . . . . . . .203Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .206Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207Exercise 6 (optional, advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .208Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .208Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .208Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211Chapter 7Beyond the fundamentals of querying213Window functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Ranking window functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216Offset window functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219Aggregate window functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221Pivoting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224Pivoting with a grouped query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .226Pivoting with the PIVOT operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227Unpivoting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230Unpivoting with the APPLY operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231Unpivoting with the UNPIVOT operator. . . . . . . . . . . . . . . . . . . . . . . . .233Grouping sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .234The GROUPING SETS subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236The CUBE subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236Contentsxiii
The ROLLUP subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236The GROUPING and GROUPING ID functions . . . . . . . . . . . . . . . . . .238Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245Exercise 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245Exercise 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245Exercise 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245Exercise 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .246Exercise 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .247Exercise 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .247Chapter 8Data modification249Inserting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249The INSERT VALUES statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249The INSERT SELECT s
T-SQL code to query and modify data, and you’ll get an overview of programmable objects. Although this book is intended for beginners, it’s not merely a set of procedures for readers to follow. It goes beyond the syntactical elements of T-SQL and
