Klienditugi: 7440010 (E-R 10-18)

Abi | Registreeri | Logi sisse

E-raamat: Beginning T-SQL: A Step-by-Step Approach

5.00/5 (6 hinnangut Goodreads-ist)

Kathi Kellenberger, Lee Everest

Formaat: PDF+DRM
Ilmumisaeg: 26-Dec-2020
Kirjastus: APress
Keel: eng
ISBN-13: 9781484266069

Teised raamatud teemal:

Formaat - PDF+DRM
Hind: 61,74 €*
* hind on lõplik, st. muud allahindlused enam ei rakendu
Lisa ostukorvi
Lisa soovinimekirja
See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.

Formaat: PDF+DRM
Ilmumisaeg: 26-Dec-2020
Kirjastus: APress
Keel: eng
ISBN-13: 9781484266069

Teised raamatud teemal:

DRM piirangud

Kopeerimine (copy/paste):

ei ole lubatud
Printimine:

ei ole lubatud
Kasutamine:

Digitaalõiguste kaitse (DRM)
Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

Vajalik tarkvara
Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

Seda e-raamatut ei saa lugeda Amazon Kindle's.

Beginning-Intermediate user level

Get a performance-oriented introduction to the T-SQL language underlying the Microsoft SQL Server and Azure SQL database engines. This fourth edition is updated to include SQL Notebooks as well as up-to-date syntax and features for T-SQL on-premises and in the Azure cloud. Exercises and examples now include the WideWorldImporters database, the newest sample database from Microsoft for SQL Server. Also new in this edition is coverage of JSON from T-SQL, news about performance enhancements called Intelligent Query Processing, and an appendix on running SQL Server in a container on macOS or Linux. 

Beginning T-SQL starts you on the path to mastering T-SQL with an emphasis on best practices. Using the sound coding techniques taught in this book will lead to excellent performance in the queries that you write in your daily work. Important techniques such as windowing functions are covered to help you write fast-executing queries that solve real business problems.The book begins with an introduction to databases, normalization, and to setting up your learning environment. You will learn about the tools you need to use such as SQL Server Management Studio, Azure Data Studio, and SQL Notebooks. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in most chapters provide an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional. 

A stand-out feature in this book is that most chapters end with a Thinking About Performance section. These sections cover aspects of query performance relative to the content just presented, including the new Intelligent Query Processing features that make queries faster without changing code. They will help you avoid beginner mistakes by knowing about and thinking about performance from day 1.

What You Will Learn

Install a sandboxed SQL Server instance for learning
Understand how relational databases are designed
Create objects such as tables and stored procedures
Query a SQL Server table 
Filter and order the results of a query
Query and work with specialized data types such as XML and JSON
Apply modern features such as window functions
Choose correct techniques so that your queries perform well

Who This Book Is For

Anyone who wants to learn T-SQL from the beginning or improve their T-SQL skills; those who need T-SQL as an additional skill; and those who write queries such as application developers, database administrators, business intelligence developers, and data scientists. The book is also helpful for anyone who must retrieve data from a SQL Server database.

About the Authors

xxi

About the Technical Reviewer

xxiii

Acknowledgments

xxv

Introduction

xxvii

Chapter 1 Getting Started

(32)

Installing SQL Server Developer Edition

(17)

Finding Help for SQL Server

(1)

Download Sample Databases

(1)

Installing Tools for SQL Server

(1)

Using Azure Data Studio

(12)

Summary

(1)

Chapter 2 Exploring Database Concepts

(14)

What Is SQL Server?

(2)

Service vs. Application

(1)

Database as Container

(3)

Data Is Stored in Tables

(1)

Data Types

(2)

Normalization

(3)

Understanding Indexes

(1)

Database Schemas

(1)

Summary

(1)

Chapter 3 Writing Simple SELECT Queries

(48)

Using the SELECT Statement

(6)

Selecting a Literal Value

(1)

Retrieving from a Table

(2)

Generating a SELECT List

(1)

Mixing Literals and Column Names

(2)

IntelliSense

(1)

Formatting T-SQL Code

(4)

Can the Statement Be Written on One Line?

(1)

Must Keywords Be Uppercase?

(1)

Are Column and Table Names Case Sensitive?

(1)

Is the Semicolon Important?

(1)

Why Use Aliases?

(1)

When to Use Single Quotes, Double Quotes, or Square Brackets?

(2)

Filtering Data

(16)

Adding a WHERE Clause

(2)

Using WHERE Clauses with Alternate Operators

(3)

Using BETWEEN

(2)

Using BETWEEN with NOT

(2)

Filtering on Date and Time

(2)

Using WHERE Clauses with Two Predicates

(3)

Using the IN Operator

(2)

Working with NULL

(3)

Sorting Data

(2)

Thinking About Performance

(5)

Taking Advantage of Indexes

(1)

Viewing Execution Plans

(3)

Summary

(1)

Answers to the Exercises

(9)

Solutions to Exercise 3-1: Using the SELECT Statement

(1)

Solutions to Exercise 3-2: Filtering Data

(2)

Solutions to Exercise 3-3: Using WHERE Clauses with Two Predicates

(2)

Solutions to Exercise 3-4: Working with NULL

(1)

Solutions to Exercise 3-5: Sorting Data

(3)

Chapter 4 Using Built-in Functions and Expressions

(68)

Expressions Using Operators

(9)

Concatenating Strings

(1)

Concatenating Strings and NULL

(1)

CONCAT

(1)

ISNULL and COALESCE

(2)

Concatenating Other Data Types to Strings

101

(3)

Using Mathematical Operators

104

(3)

Using String Functions

107

(10)

RTRIM, LTRIM, and TRIM

107

(2)

LEFT and RIGHT

109

(1)

LEN and DATALENGTH

109

(1)

CHARINDEX

110

(2)

SUBSTRING

112

(1)

CHOOSE

113

(1)

REVERSE

113

(1)

UPPER and LOWER

114

(1)

REPLACE

115

(1)

STRING_SPLIT and STRING_AGG

116

(1)

Nesting Functions

117

(3)

Using Date and Time Functions

120

(9)

GETDATE and SYSDATETIME

120

(1)

DATEADD

120

(2)

DATEDIFF

122

(1)

DATENAME and DATEPART

123

(1)

DAY, MONTH, and YEAR

124

(1)

CONVERT

125

(2)

FORMAT

127

(1)

DATEFROMPARTS

128

(1)

EOMONTH

128

(1)

Using Mathematical Functions

129

(5)

ABS

130

(1)

POWER

130

(1)

SQUARE and SORT

130

(1)

ROUND

131

(1)

RAND

132

(2)

Logical Functions and Expressions

134

(5)

The CASE Expression

134

(3)

IIF

137

(1)

COALESCE

138

(1)

Administrative Functions

139

(2)

Using Functions in the WHERE and ORDER BY Clauses

141

(2)

The TOP Keyword

143

(2)

Thinking About Performance

145

(4)

Summary

149

(1)

Answers to the Exercises

150

(13)

Solutions to Exercise 4-1: Expressions Using Operators

150

(2)

Solutions to Exercise 4-2: Using Mathematical Operators

152

(1)

Solutions to Exercise 4-3: Using Functions

153

(2)

Solutions to Exercise 4-4: Using Date and Time Functions

155

(2)

Solutions to Exercise 4-5: Using Mathematical Functions

157

(1)

Solutions to Exercise 4-6: Using Logical and System Functions

158

(2)

Solutions to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses

160

(3)

Chapter 5 Joining Tables

163

(36)

Using INNER JOIN

164

(10)

Joining Two Tables

164

(2)

Avoiding an Incorrect Join Condition

166

(2)

Joining on a Different Column Name

168

(1)

Joining on More Than One Column

169

(2)

Joining Three or More Tables

171

(3)

Using OUTER JOIN

174

(14)

Using LEFT OUTER JOIN

174

(2)

Using RIGHT OUTER JOIN

176

(1)

Using OUTER JOIN to Find Rows with No Match

177

(1)

Adding a Table to the Right Side of a LEFT JOIN

178

(2)

Adding a Table to the Main Table of a LEFT JOIN

180

(1)

FULL OUTER JOIN

181

(2)

CROSS JOIN

183

(2)

Self-Joins

185

(3)

Thinking About Performance

188

(5)

Merge Join

188

(2)

Nested Loop

190

(2)

Hash Match

192

(1)

Summary

193

(1)

Answers to the Exercises

193

(6)

Solutions to Exercise 5-1: Using INNER JOIN

194

(2)

Solutions to Exercise 5-2: Using OUTER JOIN

196

(3)

Chapter 6 Building on Subqueries, Common Table Expressions, and Unions

199

(28)

Writing Subqueries

199

(12)

Using a Subquery in an IN List

199

(1)

Using a Subquery and NOT IN

200

(1)

Using a Subquery Containing NULL with NOT IN

201

(2)

Using EXISTS

203

(1)

Using CROSS APPLY and OUTER APPLY

204

(1)

Writing UNION Queries

205

(3)

Using EXCEPT and INTERSECT

208

(3)

Using Derived Tables and Common Table Expressions

211

(7)

Using Derived Tables

211

(2)

Using Common Table Expressions

213

(2)

Using a Common Table Expression to Solve a Complicated Join Problem

215

(3)

Thinking About Performance

218

(3)

Summary

221

(1)

Answers to the Exercises

222

(5)

Solutions to Exercise 6-1: Using Subqueries

222

(3)

Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions

225

(2)

Chapter 7 Grouping and Summarizing Data

227

(34)

Aggregate Functions

227

(4)

The GROUP BY Clause

231

(4)

Grouping on Columns

231

(2)

Grouping on Expressions

233

(2)

The ORDER BY Clause

235

(2)

The WHERE Clause

237

(1)

The HAVING Clause

238

(2)

Order of Operations

240

(2)

DISTINCT Keyword

242

(3)

Using DISTINCT vs. GROUP BY

242

(1)

DISTINCT Within an Aggregate Expression

243

(2)

Aggregate Queries with More Than One Table

245

(3)

Aggregate Functions and NULL

248

(1)

Thinking About Performance

249

(3)

Summary

252

(1)

Answers to the Exercises

253

(8)

Solutions to Exercise 7-1: Aggregate Functions

253

(1)

Solutions to Exercise 7-2: The GROUP BY Clause

254

(2)

Solutions to Exercise 7-3: The HAVING Clause

256

(2)

Solutions to Exercise 7-4: DISTINCT Keyword

258

(1)

Solutions to Exercise 7-5: Aggregate Queries with More Than One Table

259

(2)

Chapter 8 Discovering Windowing Functions

261

(38)

What Is a Windowing Function?

261

(1)

Ranking Functions and the OVER Clause

262

(6)

Defining the Window

262

(3)

Using NTILE

265

(1)

Dividing the Window into Partitions

266

(2)

Summarizing Results with Window Aggregates

268

(2)

Defining the Window with Framing

270

(2)

Calculating Running Totals

272

(1)

Understanding the Difference Between ROWS and RANGE

273

(2)

Using Window Analytic Functions

275

(8)

LAG and LEAD

275

(2)

FIRST_VALUE and LAST_VALUE

277

(2)

PERCENT RANK and CUME DIST

279

(1)

PERCENTILE_CONT and PERCENTILE_DISC

280

(3)

Applying Windowing Functions

283

(4)

Removing Duplicates

283

(2)

Solving an Islands Problem

285

(2)

Thinking About Performance

287

(4)

Indexing

287

(1)

The Trouble with Window Aggregates

288

(1)

Framing

289

(2)

Summary

291

(1)

Answers to the Exercises

291

(8)

Solutions to Exercise 8-1: Ranking Functions

291

(1)

Solutions to Exercise 8-2: Summarizing Results with Window Aggregates

292

(3)

Solutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGE

295

(1)

Solutions to Exercise 8-4: Using Window Analytic Functions

296

(3)

Chapter 9 Advanced WHERE Clauses

299

(32)

Pattern Matching

299

(1)

Using LIKE

299

(8)

Restricting the Characters in Pattern Matches

301

(2)

Searching for Wildcards

303

(1)

Combining Wildcards

304

(3)

Using PATINDEX

307

(1)

Using SOME, ANY, and ALL

308

(2)

Using WHERE Clauses with Three or More Predicates

310

(5)

Using NOT with Parentheses

313

(2)

Performing a Full-Text Search

315

(7)

Using CONTAINS

316

(1)

Using Multiple Terms with CONTAINS

317

(1)

Searching Multiple Columns

318

(1)

Using FREETEXT

318

(1)

Using FREETEXTTABLE

319

(3)

Thinking About Performance

322

(1)

Summary

323

(8)

Answers to the Exercises

324

(1)

Solutions to Exercise 9-1: Using LIKE

324

(2)

Solutions to Exercise 9-2: Using WHERE Clauses with Three or More Predicates

326

(1)

Solutions to Exercise 9-3: Performing a Full-Text Search

327

(4)

Chapter 10 Manipulating Data

331

(42)

Inserting New Rows

331

(18)

Adding One Row with Literal Values

332

(2)

Avoiding Common Insert Errors

334

(3)

Inserting Multiple Rows with One Statement

337

(1)

Inserting Rows from Another Table

338

(2)

Inserting Missing Rows

340

(1)

Creating and Populating a Table in One Statement

341

(2)

Inserting Rows into Tables with Default Column Values

343

(2)

Inserting Rows into Tables with Automatically Populating Columns

345

(4)

Deleting Rows

349

(8)

Using DELETE

349

(3)

Deleting from a Table in a JOIN

352

(4)

Truncating

356

(1)

Updating Existing Rows

357

(6)

Using the UPDATE Statement

358

(1)

Updating Data with Expressions and Columns

359

(2)

Updating with a Join

361

(2)

Thinking About Performance

363

(3)

Database Cleanup

365

(1)

Summary

366

(1)

Answers to the Exercises

366

(7)

Solutions to Exercise 10-1: Inserting New Rows

366

(4)

Solutions to Exercise 10-2: Deleting Rows

370

(1)

Solutions to Exercise 10-3: Updating Existing Rows

371

(2)

Chapter 11 Managing Transactions

373

(26)

ACID Properties

373

(1)

Writing an Explicit Transaction

374

(7)

Rolling Back a Transaction

376

(3)

Using the XACT_ABORT Setting

379

(2)

Error Handling

381

(10)

Using TRY...CATCH

382

(2)

Viewing Untrappable Errors

384

(1)

Using RAISERROR

385

(2)

Using TRY...CATCH with Transactions

387

(2)

Using THROW Instead of RAISERROR

389

(2)

Thinking About Performance

391

(3)

Summary

394

(1)

Answers to the Exercises

394

(5)

Solutions to Exercise 11-1: Writing an Explicit Transaction

394

(2)

Solutions to Exercise 11-2: Error Handling

396

(3)

Chapter 12 Understanding T-SQL Programming Logic

399

(54)

Variables

399

(10)

Declaring and Initializing a Variable

399

(4)

Using Expressions and Functions with Variables

403

(2)

Using Variables in WHERE and HAVING Clauses

405

(4)

The IF ... ELSE Construct

409

(9)

Using IF

409

(2)

Using ELSE

411

(2)

Using Multiple Conditions

413

(1)

Nesting IF...ELSE

414

(2)

Using IF with a Query

416

(2)

WHILE

418

(7)

Using a WHILE Loop

418

(3)

Nesting WHILE Loops

421

(1)

Exiting a Loop Early

422

(1)

Using CONTINUE

423

(2)

Temporary Tables and Table Variables

425

(11)

Creating Local Temp Tables

426

(1)

Creating Global Temp Tables

427

(1)

Creating Table Variables

428

(2)

Using a Temp Table or Table Variable

430

(2)

Using a Temp Table or Table Variable Like an Array

432

(1)

Using a Cursor

433

(3)

Thinking About Performance

436

(4)

Summary

440

(1)

Answers to the Exercises

441

(12)

Solutions to Exercise 12-1: Variables

441

(2)

Solutions to Exercise 12-2: The IF...ELSE Construct

443

(3)

Solutions to Exercise 12-3: WHILE

446

(2)

Solutions to Exercise 12-4: Temporary Tables and Table Variables

448

(5)

Chapter 13 Implementing Logic in the Database

453

(74)

Tables

453

(23)

Adding Check Constraints to a Table

454

(2)

Adding UNIQUE Constraints

456

(4)

Adding a Primary Key to a Table

460

(4)

Creating Foreign Keys

464

(3)

Creating Foreign Keys with Delete and Update Rules

467

(4)

Defining Automatically Populated Columns

471

(5)

Views

476

(11)

Creating Views

477

(3)

Avoiding Common Problems with Views

480

(3)

Manipulating Data with Views

483

(4)

User-Defined Functions

487

(6)

Creating User-Defined Scalar Functions

487

(3)

Using Table-Valued User-Defined Functions

490

(3)

Stored Procedures

493

(11)

Using Default Values with Parameters

497

(1)

Using the OUTPUT Parameter

498

(2)

Saving the Results of a Stored Procedure in a Table

500

(2)

Using a Logic in Stored Procedures

502

(2)

User-Defined Data Types

504

(1)

Table Types

505

(3)

Triggers

508

(2)

Thinking About Performance

510

(3)

Database Cleanup

513

(2)

Summary

515

(1)

Answers to the Exercises

515

(12)

Solutions to Exercise 13-1: Tables

515

(3)

Solutions to Exercise 13-2: Views

518

(2)

Solutions to Exercise 13-3: User-Defined Functions

520

(2)

Solutions to Exercise 13-4: Stored Procedures

522

(5)

Chapter 14 Expanding on Data Type Concepts

527

(46)

Large-Value String Data Types (MAX)

528

(3)

Large-Value Binary Data Types

531

(11)

Creating VARBINARY(MAX) Data

531

(1)

Using FILESTREAM

532

(8)

FileTables

540

(2)

Enhanced Date and Time

542

(3)

Using DATE, TIME, and DATETIME2

543

(1)

Using DATETIMEOFFSET

544

(1)

HIERARCHYID

545

(8)

Viewing HIERARCHYID

546

(1)

Creating a Hierarchy

547

(2)

Using Stored Procedures to Manage Hierarchical Data

549

(4)

Spatial Data Types

553

(6)

Using GEOMETRY

553

(2)

Using GEOGRAPHY

555

(1)

Viewing the Spatial Results Tab

556

(1)

Circular Arcs

557

(2)

Sparse Columns

559

(3)

Graph Databases

562

(6)

Thinking About Performance

568

(3)

Summary

571

(2)

Chapter 15 Working with XML and JSON

573

(38)

The Parts of XML

573

(2)

Converting XML Using OPENXML

575

(4)

Retrieving Data as XML Using the FOR XML Clause

579

(11)

FOR XML RAW

580

(2)

FOR XML AUTO

582

(2)

FOR XML EXPLICIT

584

(3)

FOR XML PATH

587

(3)

The XML Data Type

590

(2)

XML Methods

592

(10)

The QUERY Method

593

(2)

The VALUE Method

595

(2)

The EXIST Method

597

(1)

The MODIFY Method

598

(2)

The NODES Method

600

(2)

JSON Data

602

(8)

Returning JSON Data

603

(2)

Inserting JSON Data

605

(2)

Shredding JSON Data

607

(3)

Summary

610

(1)

Chapter 16 Writing Advanced Queries

611

(48)

Advanced CTE Queries

611

(13)

Alternate CTE Syntax

612

(1)

Using Multiple CTEs

612

(4)

Referencing a CTE Multiple Times

616

(1)

Joining a CTE to Another CTE

617

(2)

Writing a Recursive Query

619

(3)

Data Manipulation with CTEs

622

(2)

Isolating Aggregate Query Logic

624

(8)

Correlated Subqueries in the SELECT List

624

(2)

Using Derived Tables

626

(1)

Common Table Expressions

627

(2)

Using CROSS APPLY and OUTER APPLY

629

(3)

The OUTPUT Clause

632

(5)

Using OUTPUT to View Data

632

(3)

Saving OUTPUT Data to a Table

635

(2)

The MERGE Statement

637

(4)

GROUPING SETS

641

(2)

CUBE and ROLLUP

643

(2)

Pivoted Queries

645

(9)

Pivoting Data with CASE

645

(3)

Using the PIVOT Function

648

(3)

Using the UNPIVOT Function

651

(3)

Temporal Tables

654

(2)

Paging

656

(2)

Summary

658

(1)

Chapter 17 Where to Go Next?

659

(6)

Online Resources

660

(1)

Conferences

661

(1)

User Groups

661

(1)

Vendors

662

(1)

Books

662

(1)

Classes

662

(1)

SQL Server Documentation

663

(1)

Practice, Practice, and More Practice

663

(1)

Teach Someone Else

663

(2)

Appendix A: SQL Server for Linux and macOS

665

(12)

Installing a SQL Server Instance in a Container

665

(5)

Copy Sample Databases to the Container

670

(1)

Restore Sample Databases

671

(6)

Appendix B: Using SSMS

677

(12)

Installing SQL Server Management Studio

677

(1)

Launching SQL Server Management Studio

677

(3)

Installing the Sample Databases

680

(6)

Get Started with SSMS

686

(2)

Summary

688

(1)

Appendix C: SQL Notebooks

689

(6)

Opening a SQL Notebook

689

(1)

Adding Code to the Notebook

690

(1)

Adding a Text Cell

691

(2)

Organizing Your Notebooks

693

(1)

Summary

694

(1)

Index

695

Kathi Kellenberger is an editor and DevOps advocate at Redgate Software and a Microsoft Data Platform MVP. She has been working with SQL Server for over 20 years, starting with version 6.5. She has worked as a developer, database administrator, and consultant. Kathi has been involved with almost 20 book projects as an author, co-author, or technical editor, and enjoys presenting at events, webinars, and user groups. When not working, she teaches T-SQL to beginners at a non-profit in St. Louis, Missouri that helps people transition to tech careers. Lee Everest is a SQL Server developer who has worked with the product since version 6.5. He has taught part-time at Dallas College North Lake Campus for 18 years, and has had several students move on to companies such as Microsoft, filling roles from support engineer to data scientist to vice president. When Lee isnt working, he enjoys computers, golf, and fishing.

Lisainfo e-raamatute kohta

Püsilink: https://www.kriso.ee/db/97814842660692e.html

Märksõnad:

E-raamat: Beginning T-SQL: A Step-by-Step Approach

DRM piirangud

Kopeerimine (copy/paste):

Printimine:

Kasutamine:

Konto & seaded

Otsing

Otsingu andmebaas

Filtreeri tulemusi

Teemad E-raamatute teemad

Vali ostukorv