Product Cover Image

Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes

By Ben Forta

Published by Sams

Published Date: Aug 2, 2007

Description

Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through 10-minute lessons, you’ll learn everything you need to know to take advantage of Microsoft SQL Server’s T-SQL language.

 

This handy pocket guide starts with simple data retrieval and moves on to more complex topics, including the use of joins, subqueries, full text-based searches, functions and stored procedures, cursors, triggers, table constraints, XML, and much more.

 

You’ll learn what you need to know methodically, systematically, and simply—in highly focused lessons designed to make you immediately and effortlessly productive.

 

Tips point out shortcuts and solutions

 

Cautions help you avoid common pitfalls

 

Notes explain additional concepts, and provide additional information

 

10 minutes is all you need to learn how to…

  • Use T-SQL in the Microsoft SQL Server environment
  • Construct complex T-SQL statements using multiple clauses and operators
  • Filter data so you get the information you need quickly
  • Retrieve, sort, and format database contents
  • Join two or more related tables
  • Make SQL Server work for you with globalization and localization
  • Create subqueries to pinpoint your data
  • Automate your workload with triggers
  • Create and alter database tables
  • Work with views, stored procedures, and more

 Register your book at www.samspublishing.com/register to download examples and source code from this book.

Table of Contents

    Introduction                                                                                                  1

        Who Is This Book For?..................................................................................................... 2

        Companion Website............................................................................................................. 2

        Conventions Used in This Book................................................................................. 3

    1    Understanding SQL                                                                               5

        Database Basics....................................................................................................................... 5

        What Is SQL?......................................................................................................................... 11

        Try It Yourself...................................................................................................................... 12

        Summary.................................................................................................................................... 12

    2    Introducing SQL Server                                                                       13

        What Is SQL Server?......................................................................................................... 13

        SQL Server Tools................................................................................................................ 16

        Summary.................................................................................................................................... 18

    3    Working with SQL Server                                                                   19

        Making the Connection................................................................................................... 19

        Selecting a Database........................................................................................................... 20

        Learning About Databases and Tables................................................................ 21

        Summary.................................................................................................................................... 25

    4    Retrieving Data                                                                                     27

        The SELECT Statement................................................................................................. 27

        Retrieving Individual Columns.................................................................................. 27

        Retrieving Multiple Columns..................................................................................... 29

        Retrieving All Columns................................................................................................... 31

        Retrieving Distinct Rows.............................................................................................. 32

        Limiting Results.................................................................................................................... 33

        Using Fully Qualified Table Names...................................................................... 36

        Summary.................................................................................................................................... 36

    5    Sorting Retrieved Data                                                                        37

        Sorting Data............................................................................................................................. 37

        Sorting by Multiple Columns.................................................................................... 39

        Specifying Sort Direction.............................................................................................. 40

        Summary.................................................................................................................................... 43

    6    Filtering Data                                                                                         45

        Using the WHERE Clause............................................................................................ 45

        The WHERE Clause Operators................................................................................ 46

        Summary.................................................................................................................................... 52

    7    Advanced Data Filtering                                                                     53

        Combining WHERE Clauses....................................................................................... 53

        Using the IN Operator..................................................................................................... 57

        Using the NOT Operator.............................................................................................. 59

        Summary.................................................................................................................................... 60

    8    Using Wildcard Filtering                                                                     61

        Using the LIKE Operator.............................................................................................. 61

        Tips for Using Wildcards.............................................................................................. 67

        Summary.................................................................................................................................... 67

    9    Creating Calculated Fields                                                                  69

        Understanding Calculated Fields.............................................................................. 69

        Concatenating Fields......................................................................................................... 70

        Performing Mathematical Calculations............................................................... 75

        Summary.................................................................................................................................... 77

    10    Using Data Manipulation Functions                                               79

        Understanding Functions............................................................................................... 79

        Using Functions.................................................................................................................... 80

        Summary.................................................................................................................................... 89

    11    Summarizing Data                                                                               91

        Using Aggregate Functions........................................................................................... 91

        Aggregates on Distinct Values................................................................................... 98

        Combining Aggregate Functions............................................................................ 100

        Summary................................................................................................................................. 100

    12    Grouping Data                                                                                   101

        Understanding Data Grouping................................................................................ 101

        Creating Groups................................................................................................................ 102

        Filtering Groups................................................................................................................ 103

        Grouping and Sorting..................................................................................................... 106

        SELECT Clause Ordering........................................................................................... 108

        Summary................................................................................................................................. 109

    13    Working with Subqueries                                                               111

        Understanding Subqueries.......................................................................................... 111

        Filtering by Subquery.................................................................................................... 111

        Using Subqueries as Calculated Fields.............................................................. 116

        Checking for Existence with Subqueries.......................................................... 119

        Summary................................................................................................................................. 121

    14    Joining Tables                                                                                   123

        Understanding Joins....................................................................................................... 123

        Creating a Join..................................................................................................................... 126

        Summary................................................................................................................................. 135

    15    Creating Advanced Joins                                                                137

        Using Table Aliases........................................................................................................ 137

        Using Different Join Types...................................................................................... 138

        Using Joins with Aggregate Functions............................................................. 145

        Using Joins and Join Conditions........................................................................... 147

        Summary................................................................................................................................. 147

    16    Combining Queries                                                                           149

        Understanding Combined Queries....................................................................... 149

        Creating Combined Queries...................................................................................... 150

        Summary................................................................................................................................. 155

    17    Full-Text Searching                                                                          157

        Understanding Full-Text Searching..................................................................... 157

        Setting Up Full-Text Searching.............................................................................. 158

        Performing Full-Text Searches................................................................................ 162

        Summary................................................................................................................................. 170

    18    Inserting Data                                                                                   171

        Understanding Data Insertion................................................................................. 171

        Inserting Complete Rows........................................................................................... 172

        Inserting Multiple Rows............................................................................................. 176

        Inserting Retrieved Data.............................................................................................. 177

        Summary................................................................................................................................. 179

    19    Updating and Deleting Data                                                           181

        Updating Data..................................................................................................................... 181

        Deleting Data....................................................................................................................... 183

        Guidelines for Updating and Deleting Data.................................................. 184

        Summary................................................................................................................................. 185

    20    Creating and Manipulating Tables                                                187

        Creating Tables................................................................................................................... 187

        Updating Tables................................................................................................................ 195

        Deleting Tables................................................................................................................... 197

        Renaming Tables............................................................................................................... 198

        Summary................................................................................................................................. 198

    21    Using Views                                                                                      199

        Understanding Views..................................................................................................... 199

        Using Views.......................................................................................................................... 201

        Summary................................................................................................................................. 208

    22    Programming with T-SQL                                                                209

        Understanding T-SQL Programming.................................................................. 209

        Using Variables................................................................................................................... 210

        Using Conditional Processing.................................................................................. 217

        Grouping Statements..................................................................................................... 219

        Using Looping..................................................................................................................... 221

        Summary................................................................................................................................. 222

    23    Working with Stored Procedures                                                   223

        Understanding Stored Procedures........................................................................ 223

        Why Use Stored Procedures.................................................................................... 224

        Using Stored Procedures............................................................................................. 225

        Summary................................................................................................................................. 234

    24    Using Cursors                                                                                   235

        Understanding Cursors................................................................................................. 235

        Working with Cursors................................................................................................... 235

        Summary................................................................................................................................. 242

    25    Using Triggers                                                                                  243

        Understanding Triggers................................................................................................ 243

        Using Triggers..................................................................................................................... 247

        Summary................................................................................................................................. 250

    26    Managing Transaction Processing                                               251

        Understanding Transaction Processing............................................................ 251

        Controlling Transactions............................................................................................. 253

        Summary................................................................................................................................. 258

    27    Working with XML                                                                          259

        Understanding SQL Server XML Support................................................... 259

        Retrieving Data as XML............................................................................................. 260

        Storing XML Data........................................................................................................... 264

        Searching for XML Data............................................................................................. 267

        Summary................................................................................................................................. 268

    28    Globalization and Localization                                                        269

        Understanding Character Sets and Collation Sequences...................... 269

        Working with Collation Sequences...................................................................... 270

        Managing Case Sensitivity........................................................................................ 273

        Working with Unicode.................................................................................................. 275

        Summary................................................................................................................................. 277

    29    Managing Security                                                                           279

        Understanding Access Control............................................................................... 279

        Managing Users................................................................................................................. 281

        Managing Access Rights............................................................................................. 283

        Summary................................................................................................................................. 285

    30    Improving Performance                                                                   287

        Improving Performance................................................................................................ 287

        Summary................................................................................................................................. 289

Appendixes

    A Getting Started with SQL Server and T-SQL                                     291

    B The Example Tables                                                                               295

    C T-SQL Statement Syntax                                                                       303

    D T-SQL Datatypes                                                                                   309

    E T-SQL Reserved Words                                                                        315

TOC, 0672328674, 7/11/2007

Purchase Info

ISBN-10: 0-13-264990-X

ISBN-13: 978-0-13-264990-2

Format: eBook (Watermarked)?

This eBook includes the following formats, accessible from your Account page after purchase:

ePubEPUBThe open industry format known for its reflowable content and usability on supported mobile devices.

MOBIMOBIThe eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

Adobe ReaderPDFThe popular standard, used most often with the free Adobe® Reader® software.

This eBook requires no passwords or activation to read. We customize your eBook by discretely watermarking it with your name, making it uniquely yours.

Includes EPUB, MOBI, and PDF

$23.99 $19.19

Add to Cart