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

Ben Forta is Adobe Systems’s Senior Technical Evangelist and has over 20 years of experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the bestselling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into more than a dozen languages), MySQL Crash Course, ColdFusion Web Application Construction Kit and Advanced ColdFusion Development (both published by Que), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on Flash, Java, WAP, Windows 2000, and other subjects. He has extensive experience in database design and development, has implemented databases for several highly successful commercial software programs, and is a frequent lecturer and columnist on Internet and database technologies. Born in London, England, and educated in London, New York, and Los Angeles, Ben now lives in Oak Park, Michigan with his wife Marcy and their seven children. Ben welcomes your email at ben@forta.com, and invites you to visit his website at http://www.forta.com/.

    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