book

Beginning T-SQL

Name: Beginning T-SQL
ISBN: 9781484200469

by Kathi Kellenberger, Scott Shaw

August 2014

Beginner

496 pages

12h 26m

English

Apress

Read now

Unlock full access

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 EditionInstalling Books OnlineUsing Books OnlineUsing SQL Server Management StudioLaunching SQL Server Management StudioInstalling the Sample DatabasesGet Started with SSMSSummary
Chapter 2: Exploring Database Concepts
What Is SQL Server?Databases in the CloudService vs. ApplicationDatabase as ContainerData Is Stored in TablesData TypesNormalizationUnderstanding IndexesDatabase SchemasSummary
Chapter 3: Writing Simple SELECT Queries
Using the SELECT StatementSelecting a Literal ValueRetrieving from a TableGenerating a Select-ListMixing Literals and Column NamesFiltering DataAdding a WHERE ClauseUsing WHERE Clauses with Alternate OperatorsUsing BETWEENUsingBETWEEN with NOTFiltering on Date and TimeUsing WHERE Clauses with Two PredicatesUsing the IN OperatorWorking with NULLSorting DataThinking About PerformanceTaking Advantage of IndexesViewing Execution PlansSummaryAnswers to the ExercisesSolutions to Exercise 3-1: Using the SELECT StatementSolutions to Exercise 3-2: Filtering DataSolutions to Exercise 3-3: Using WHERE Clauses with Two PredicatesSolutions to Exercise 3-4: Working with NULLSolutions to Exercise 3-5: Sorting Data
Chapter 4: Using Built-In Functions and Expressions
Expressions Using OperatorsConcatenating StringsConcatenating Strings and NULLCONCATISNULL and COALESCEConcatenating Other Data Types to StringsUsing Mathematical OperatorsUsing String FunctionsRTRIM and LTRIMLEFT and RIGHTLEN and DATALENGTHCHARINDEXSUBSTRINGCHOOSEREVERSEUPPER and LOWERREPLACENesting FunctionsUsing Date and Time FunctionsGETDATE and SYSDATETIMEDATEADDDATEDIFFDATENAME and DATEPARTDAY, MONTH, and YEARCONVERTFORMATDATEFROMPARTSEOMONTHUsing Mathematical FunctionsABSPOWERSQUARE and SQRTROUNDRANDLogical Functions and ExpressionsThe CASE ExpressionIIFCOALESCEAdministrative FunctionsUsing Functions in the WHERE and ORDER BY ClausesThe TOP KeywordThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 4-1: Expressions Using OperatorsSolutions to Exercise 4-2: Using Mathematical OperatorsSolution to Exercise 4-3: Using FunctionsSolution to Exercise 4-4: Using Date and Time FunctionsSolution to Exercise 4-5: Using Mathematical FunctionsSolution to Exercise 4-6: Using Logical and System FunctionsSolution to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses
Chapter 5: Joining Tables
Using INNER JOINJoining Two TablesAvoiding an Incorrect Join ConditionJoining on a Different Column NameJoining on More Than One ColumnJoining Three or More TablesUsing OUTER JOINUsing LEFT OUTER JOINUsing RIGHT OUTER JOINUsing OUTER JOIN to Find Rows with No MatchAdding a Table to the Right Side of a LEFT JOINAdding a Table to the Main Table of a LEFT JOINFULL OUTER JOINCROSS JOINSelf-JoinsThinking About PerformanceMerge JoinNested LoopHash MatchSummaryAnswers to the ExercisesSolutions to Exercise 5-1: Using INNER JOINSolutions to Exercise 5-2: Using OUTER JOIN
Chapter 6: Building on Subqueries, Common Table Expressions, and Unions
Writing SubqueriesUsing a Subquery in an IN ListUsing a Subquery and NOT INUsing a Subquery Containing NULL with NOT INUsing EXISTSUsing CROSS APPLY and OUTER APPLYWriting UNION QueriesUsing EXCEPT and INTERSECTUsing Derived Tables and Common Table ExpressionsUsing Derived TablesUsing Common Table ExpressionsUsing a Common Table Expression to Solve a Complicated Join ProblemThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 6-1: Using SubqueriesSolutions to Exercise 6-2: Using Derived Tables and Common Table Expressions
Chapter 7: Grouping and Summarizing Data
Aggregate FunctionsThe GROUP BY ClauseGrouping on ColumnsGrouping on ExpressionsThe ORDER BY ClauseThe WHERE ClauseThe HAVING ClauseDISTINCT KeywordUsing DISTINCT vs. GROUP BYDISTINCT Within an Aggregate ExpressionAggregate Queries with More Than One TableAggregate Functions and NULLThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 7-1: Aggregate FunctionsSolutions to Exercise 7-2: The GROUP BY ClauseSolutions to Exercise 7-3: The HAVING ClauseSolutions to Exercise 7-4: DISTINCT KeywordSolutions to Exercise 7-5: Aggregate Queries with More Than One Table
Chapter 8: Discovering Windowing Functions
What Is a Windowing Function?Ranking FunctionsDefining the WindowDividing the Window into PartitionsUsing NTILESummarizing Results with Window AggregatesDefining the Window with FramingCalculating Running TotalsUnderstanding the Difference Between ROWS and RANGEUsing Window Analytic FunctionsLAG and LEADFIRST_VALUE and LAST_VALUEPERCENT_RANK and CUME_DISTPERCENTILE_CONT and PERCENTILE_DISCApplying Windowing FunctionsRemoving DuplicatesSolving an Islands ProblemThinking About PerformanceIndexingThe Trouble with Window AggregatesFramingSummaryAnswers to the ExercisesSolutions to Exercise 8-1: Ranking FunctionsSolutions to Exercise 8-2: Summarizing Results with Window AggregatesSolutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGESolutions to Exercise 8-4: Using Window Analytic Functions
Chapter 9: Advanced WHERE Clauses
Pattern MatchingUsing LIKERestricting the Characters in Pattern MatchesSearching for WildcardsCombining WildcardsUsing PATINDEXUsing WHERE Clauses with Three or More PredicatesUsing NOT with ParenthesesPerforming a Full-Text SearchUsing CONTAINSUsing Multiple Terms with CONTAINSSearching Multiple ColumnsUsing FREETEXTThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 9-1: Using LIKESolution to Exercise 9-2: Using WHERE Clauses with Three or More PredicatesSolution to Exercise 9-3: Performing a Full-Text Search
Chapter 10: Manipulating Data
Inserting New RowsAdding One Row with Literal ValuesAvoiding Common Insert ErrorsInserting Multiple Rows with One StatementInserting Rows from Another TableInserting Missing RowsCreating and Populating a Table in One StatementInserting Rows into Tables with Default Column ValuesInserting Rows into Tables with Automatically Populating ColumnsDeleting RowsUsing DELETEDeleting from a Table Using EXISTSTruncatingUpdating Existing RowsUsing the UPDATE StatementUpdating Data with Expressions and ColumnsUpdating with a JoinThinking About PerformanceDatabase CleanupSummaryAnswers to the ExercisesSolution to Exercise 10-1: Inserting New RowsSolution to Exercise 10-2: Deleting RowsSolution to Exercise 10-3: Updating Existing Rows
Chapter 11: Writing Advanced Queries
Advanced CTE QueriesAlternate CTE SyntaxUsing Multiple CTEsReferencing a CTE Multiple TimesJoining a CTE to Another CTEWriting a Recursive QueryData Manipulation with CTEsIsolating Aggregate Query LogicCorrelated Subqueries in the SELECT listUsing Derived TablesCommon Table ExpressionsUsing CROSS APPLY and OUTER APPLYThe OUTPUT ClauseUsing OUTPUT to View DataSaving OUTPUT Data to a TableThe MERGE StatementGROUPING SETSCUBE and ROLLUPPivoted QueriesPivoting Data with CASEUsing the PIVOT FunctionUsing the UNPIVOT FunctionPagingSummary
Chapter 12: Understanding T-SQL Programming Logic
VariablesDeclaring and Initializing a VariableUsing Expressions and Functions with VariablesUsing Variables in WHERE and HAVING ClausesThe IF . . . ELSE ConstructUsing IFUsing ELSEUsing Multiple ConditionsNesting IF ... ELSEUsing IF with a QueryWHILEUsing a WHILE LoopNesting WHILE LoopsExiting a Loop EarlyUsing CONTINUETemporary Tables and Table VariablesCreating Local Temp TablesCreating Global Temp TablesCreating Table VariablesUsing a Temp Table or Table VariableUsing a Temp Table or Table Variable Like an ArrayUsing a CursorThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 12-1: VariablesSolutions to Exercise 12-2: The IF . . . ELSE ConstructSolutions to Exercise 12-3: WHILESolutions to Exercise 12-4: Temporary Tables and Table Variables
Chapter 13: Managing Transactions
ACID PropertiesWriting an Explicit TransactionRolling Back a TransactionUsing the XACT_ABORT SettingError HandlingUsing TRY . . . CATCHViewing Untrappable ErrorsUsing RAISERRORUsing TRY . . . CATCH with TransactionsUsing THROW Instead of RAISERRORThinking About PerformanceSummaryAnswers to the ExercisesSolutions to Exercise 13-1: Writing an Explicit TransactionSolutions to Exercise 13-2: Error Handling
Chapter 14: Implementing Logic in the Database
TablesAdding Check Constraints to a TableAdding UNIQUE ConstraintsAdding a Primary Key to a TableCreating Foreign KeysCreating Foreign Keys with Delete and Update RulesDefining Automatically Populated ColumnsViewsCreating ViewsAvoiding Common Problems with ViewsManipulating Data with ViewsUser-Defined FunctionsCreating User-Defined Scalar FunctionsUsing Table-Valued User-Defined FunctionsStored ProceduresUsing Default Values with ParametersUsing the OUTPUT ParameterSaving the Results of a Stored Procedure in a TableUsing a Logic in Stored ProceduresUser-Defined Data TypesTable TypesTriggersCLR IntegrationThinking About PerformanceDatabase CleanupSummaryAnswers to the ExercisesSolutions to Exercise 14-1: TablesSolution to Exercise 14-2: ViewsSolution to Exercise 14-3: User-Defined FunctionsSolution to Exercise 14-4: Stored Procedures
Chapter 15: Working with XML
The Parts of XMLConverting XML Using OPENXMLRetrieving Data as XML Using the FOR XML ClauseFOR XML RAWFOR XML AUTOFOR XML EXPLICITFOR XML PATHThe XML Data TypeXML MethodsThe QUERY MethodThe VALUE MethodThe EXIST MethodThe MODIFY MethodThe NODES MethodNamespacesSplitting a StringSummary
Chapter 16: Expanding on Data Type Concepts
Large-Value String Data Types (MAX)Large-Value Binary Data TypesCreating VARBINARY(MAX) DataUsing FILESTREAMFileTablesEnhanced Date and TimeUsing DATE, TIME, and DATETIME2Using DATETIMEOFFSETHIERARCHYIDViewing HIERARCHYIDCreating a HierarchyUsing Stored Procedures to Manage Hierarchical DataSpatial Data TypesUsing GEOMETRYUsing GEOGRAPHYViewing the Spatial Results TabCircular ArcsSparse ColumnsThinking About PerformanceSummary
Chapter 17: Running SQL Server in the Cloud
Procuring a Microsoft Azure AccountThe Azure DashboardWindows Azure Virtual MachinesAzure SQL DatabaseThrottlingDatabase Size LimitationsPricingSummary
Chapter 18: Where to Go Next?
Online ResourcesConferencesUser GroupsVendorsBooksClassesSQL Server DocumentationPractice, Practice, and More PracticeTeach Someone Else
Index

Content preview from Beginning T-SQL

CHAPTER 11

Writing Advanced Queries

In Chapter 10 you learned how to manipulate data. In this chapter you will learn some advanced query techniques. For example, you will learn more about common table expressions (CTEs), how to write a pivot query, and more. As a beginning T-SQL developer, you may or may not need this information right away. This chapter doesn’t contain any exercises, but I encourage you to experiment and come up with your own examples for any of the features that you are interested in. Consider the information in this chapter as a head start in becoming an expert T-SQL developer.

Advanced CTE Queries

In Chapter 6 you learned ...

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: 9781484200469Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Beginning T-SQL

by Kathi Kellenberger, Scott Shaw

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.