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

IntroductionIntroduction\ Microsoft® SQL Server has become one of the most popular database management systems in the world. From small development projects to some of the best-known and most prestigious sites on the Web, SQL Server has proven itself to be a solid, reliable, fast, and trusted solution to all sorts of data-storage needs.\ This book is based on my best-selling book Sams Teach Yourself SQL in 10 Minutes, which has become one of the most-used SQL tutorials in the world, with an emphasis on teaching what you really need to know, methodically, systematically, and simply. But as popular and as successful as that book is, it does have some limitations:\ \ \ In covering all the major DBMSs, coverage of DBMS-specific features and functionality had to be kept to a minimum.\ \ \ To simplify the SQL taught, the lowest common denominator had to be found; SQL statements that would (as much as possible) work with all major DBMSs. This requirement necessitated that better DBMS-specific solutions not be covered.\ \ \ Although basic SQL tends to be rather portable between DBMSs, more advanced SQL most definitely is not. As such, that book could not cover advanced topics, such as triggers, cursors, stored procedures, access control, transactions, and more in any real detail.\ \ \ And that is where this book comes in. Sams Teach Yourself Microsoft® SQL Server T-SQL in 10 Minutes builds on the proven tutorials and structure of Sams Teach Yourself SQL in 10 Minutes, without getting bogged down with anything but Transact-SQL (T-SQL, for short). It starts with simple data retrieval and works on to more complex topics, including the use of joins,subqueries, full text-based searches, functions and stored procedures, cursors, triggers, table constraints,\ \ Note: Written for SQL Server 2005 - This book was written with SQL Server 2005 in mind, and covers features and technologies new to that version of the software. However, with the exception of two lessons, the content and lessons can be used with earlier versions of SQL Server, including SQL Server 2000.\ \ So turn to Lesson 1, "Understanding SQL," and get to work. You'll be taking advantage of all SQL Server has to offer in no time at all.Who Is This Book For?\ This book is for you if...\ \ \ You are new to SQL.\ \ \ You are just getting started with SQL Server and want to hit the ground running.\ \ \ You want to quickly learn how to get the most out of SQL Server and T-SQL.\ \ \ You want to learn how to use T-SQL in your own application development.\ \ \ You want to be productive quickly and easily using SQL Server without having to call someone for help.\ \ \ Companion Website\ This book has a companion website online at http://forta.com/books/0672328674/.\ Visit the site to access the following:\ \ \ Table creation and population scripts used to create the sample tables used throughout this book\ \ \ The online support forum\ \ \ Online errata (should one be required)\ \ \ Other books that may be of interest to you\ \ \ Conventions Used in This Book\ This book uses different typefaces to differentiate between code and regular English, and also to help you identify important concepts.\ Text that you type and text that should appear on your screen is presented in monospace type. It looks like this to mimic the way text looks on your screen.\ Placeholders for variables and expressions appear in monospace italic font. You should replace the placeholder with the specific value it represents.\ This arrow (¬) at the beginning of a line of code means that a single line of code is too long to fit on the printed page. Continue typing all the characters after the ¬ as though they were part of the preceding line.\ \ Note - A note presents interesting pieces of information related to the surrounding discussion.\ \ \ Tip - A tip offers advice or teaches an easier way to do something.\ \ \ Caution - A caution advises you about potential problems and helps you steer clear of disaster.\ \ \ Plain English: New Term icons provide clear definitions of new, essential terms.\ \ Input\ Input identifies code that you can type in yourself. It usually appears next to a listing.\ Output\ Output highlights the output produced by running T-SQL code. It usually appears after a listing.\ Analysis\ Analysis alerts you to the author's line-by-line analysis of input or output.\ \ © Copyright Pearson Education. All rights reserved.

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\ Understanding SQL Server\ Retrieving Data as\ Storing\ Searching for\ 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