book

T-SQL Fundamentals, Third Edition

Name: T-SQL Fundamentals, Third Edition
Author: Itzik Ben-Gan
ISBN: 150930200X

by Itzik Ben-Gan

August 2016

Intermediate to advanced

464 pages

15h 32m

English

Microsoft Press

Read now

Unlock full access

Cover
Title Page
Copyright Page
Dedication Page
Contents at a glance
Contents
Introduction
Who should read this bookAssumptionsThis book might not be for you if...Organization of this bookSystem requirementsInstalling and using the source codeAcknowledgmentsErrata, updates, & book supportFree ebooks from Microsoft PressWe want to hear from youStay in touch
Chapter 1. Background to T-SQL querying and programming
Theoretical backgroundSQLSet theoryPredicate logicThe relational modelTypes of database systemsSQL Server architectureThe ABCs of Microsoft RDBMS flavorsSQL Server instancesDatabasesSchemas and objectsCreating tables and defining data integrityCreating tablesDefining data integrityConclusion
Chapter 2. Single-table queries
Elements of the SELECT statementThe FROM clauseThe WHERE clauseThe GROUP BY clauseThe HAVING clauseThe SELECT clauseThe ORDER BY clauseThe TOP and OFFSET-FETCH filtersA quick look at window functionsPredicates and operatorsCASE expressionsNULLsAll-at-once operationsWorking with character dataData typesCollationOperators and functionsThe LIKE predicateWorking with date and time dataDate and time data typesLiteralsWorking with date and time separatelyFiltering date rangesDate and time functionsQuerying metadataCatalog viewsInformation schema viewsSystem stored procedures and functionsConclusionExercisesExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7Exercise 8Exercise 9Exercise 10SolutionsExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7Exercise 8Exercise 9Exercise 10
Chapter 3. Joins
Cross joinsISO/ANSI SQL-92 syntaxISO/ANSI SQL-89 syntaxSelf cross joinsProducing tables of numbersInner joinsISO/ANSI SQL-92 syntaxISO/ANSI SQL-89 syntaxInner join safetyMore join examplesComposite joinsNon-equi joinsMulti-join queriesOuter joinsFundamentals of outer joinsBeyond the fundamentals of outer joinsConclusionExercisesExercise 1-1Exercise 1-2 (optional, advanced)Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7 (optional, advanced)Exercise 8 (optional, advanced)Exercise 9 (optional, advanced)SolutionsExercise 1-1Exercise 1-2Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7Exercise 8Exercise 9

Chapter 4. Subqueries
Self-contained subqueriesSelf-contained scalar subquery examplesSelf-contained multivalued subquery examplesCorrelated subqueriesThe EXISTS predicateBeyond the fundamentals of subqueriesReturning previous or next valuesUsing running aggregatesDealing with misbehaving subqueriesConclusionExercisesExercise 1Exercise 2 (optional, advanced)Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7 (optional, advanced)Exercise 8 (optional, advanced)Exercise 9Exercise 10 (optional, advanced)SolutionsExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6Exercise 7Exercise 8Exercise 9Exercise 10
Chapter 5. Table expressions
Derived tablesAssigning column aliasesUsing argumentsNestingMultiple referencesCommon table expressionsAssigning column aliases in CTEsUsing arguments in CTEsDefining multiple CTEsMultiple references in CTEsRecursive CTEsViewsViews and the ORDER BY clauseView optionsInline table-valued functionsThe APPLY operatorConclusionExercisesExercise 1Exercise 2-1Exercise 2-2Exercise 3-1Exercise 3-2Exercise 4 (optional, advanced)Exercise 5-1Exercise 5-2 (optional, advanced)Exercise 6-1Exercise 6-2SolutionsExercise 1Exercise 2-1Exercise 2-2Exercise 3-1Exercise 3-2Exercise 4Exercise 5-1Exercise 5-2Exercise 6-1Exercise 6-2
Chapter 6. Set operators
The UNION operatorThe UNION ALL operatorThe UNION (DISTINCT) operatorThe INTERSECT OperatorThe INTERSECT (DISTINCT) operatorThe INTERSECT ALL operatorThe EXCEPT operatorThe EXCEPT (DISTINCT) operatorThe EXCEPT ALL operatorPrecedenceCircumventing unsupported logical phasesConclusionExercisesExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6 (optional, advanced)SolutionsExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6
Chapter 7. Beyond the fundamentals of querying
Window functionsRanking window functionsOffset window functionsAggregate window functionsPivoting dataPivoting with a grouped queryPivoting with the PIVOT operatorUnpivoting dataUnpivoting with the APPLY operatorUnpivoting with the UNPIVOT operatorGrouping setsThe GROUPING SETS subclauseThe CUBE subclauseThe ROLLUP subclauseThe GROUPING and GROUPING_ID functionsConclusionExercisesExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6SolutionsExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6
Chapter 8. Data modification
Inserting dataThe INSERT VALUES statementThe INSERT SELECT statementThe INSERT EXEC statementThe SELECT INTO statementThe BULK INSERT statementThe identity property and the sequence objectDeleting dataThe DELETE statementThe TRUNCATE statementDELETE based on a joinUpdating dataThe UPDATE statementUPDATE based on a joinAssignment UPDATEMerging dataModifying data through table expressionsModifications with TOP and OFFSET-FETCHThe OUTPUT clauseINSERT with OUTPUTDELETE with OUTPUTUPDATE with OUTPUTMERGE with OUTPUTNested DMLConclusionExercisesExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6SolutionsExercise 1Exercise 2Exercise 3Exercise 4Exercise 5Exercise 6
Chapter 9. Temporal tables
Creating tablesModifying dataQuerying dataConclusionExercisesExercise 1Exercise 2Exercise 3Exercise 4SolutionsExercise 1Exercise 2Exercise 3Exercise 4
Chapter 10. Transactions and concurrency
TransactionsLocks and blockingLocksTroubleshooting blockingIsolation levelsThe READ UNCOMMITTED isolation levelThe READ COMMITTED isolation levelThe REPEATABLE READ isolation levelThe SERIALIZABLE isolation levelIsolation levels based on row versioningSummary of isolation levelsDeadlocksConclusionExercisesExercise 1-1Exercise 1-2Exercise 1-3Exercise 1-4Exercise 1-5Exercise 1-6Exercise 2-1Exercise 2-2Exercise 2-3Exercise 2-4Exercise 2-5Exercise 2-6Exercise 3-1Exercise 3-2Exercise 3-3Exercise 3-4Exercise 3-5Exercise 3-6Exercise 3-7
Chapter 11. Programmable objects
VariablesBatchesA batch as a unit of parsingBatches and variablesStatements that cannot be combined in the same batchA batch as a unit of resolutionThe GO n optionFlow elementsThe IF . . . ELSE flow elementThe WHILE flow elementCursorsTemporary tablesLocal temporary tablesGlobal temporary tablesTable variablesTable typesDynamic SQLThe EXEC commandThe sp_executesql stored procedureUsing PIVOT with Dynamic SQLRoutinesUser-defined functionsStored proceduresTriggersError handlingConclusion
Appendix: Getting started
Getting started with Azure SQL DatabaseInstalling a SQL Server box product1. Obtain SQL Server2. Install the database engineDownloading and installing SQL Server Management StudioDownloading source code and installing the sample databaseWorking with SQL Server Management StudioWorking with SQL Server Books Online
Index
About the author
Visit us today at
Hear about it first
Free ebooks
Survey
Code Snippets

Content preview from T-SQL Fundamentals, Third Edition

Chapter 4. Subqueries

SQL supports writing queries within queries, or nesting queries. The outermost query is a query whose result set is returned to the caller and is known as the outer query. The inner query is a query whose result is used by the outer query and is known as a subquery. The inner query acts in place of an expression that is based on constants or variables and is evaluated at run time. Unlike the results of expressions that use constants, the result of a subquery can change, because of changes in the queried tables. When you use subqueries, you avoid the need for separate steps in your solutions that store intermediate query results in variables.

A subquery can be either self-contained or correlated. A self-contained subquery ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781509302031Purchase Link

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

T-SQL Fundamentals, Third Edition

by Itzik Ben-Gan

Chapter 4. Subqueries

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.