ISBN-10:
0672328674
ISBN-13:
9780672328671
Pub. Date:
08/16/2007
Publisher:
Sams
Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes / Edition 1

Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes / Edition 1

by Ben Forta
Current price is , Original price is $29.99. You

Temporarily Out of Stock Online

Please check back later for updated availability.

Product Details

ISBN-13: 9780672328671
Publisher: Sams
Publication date: 08/16/2007
Series: Sams Teach Yourself Series
Pages: 368
Sales rank: 1,037,906
Product dimensions: 5.50(w) x 8.25(h) x 0.79(d)

About the Author

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/.

Read an Excerpt

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.

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

Preface

Introduction

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, 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.


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.

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews

Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes 4 out of 5 based on 0 ratings. 2 reviews.
JohnFair on LibraryThing More than 1 year ago
While the lessons are broken down into a series of fairly easily digestible lessons, if you really want to get the best out of the various samples you will need to spend a bit longer at them but the effort is worth it. Wrox classify this at the Beginning-Intermediate level which seems about right but I reckon it won't hurt to know some basic SQL before coming to the SQLServer specific code in the book and there is no great look at how to design the database in the fisrt place (though you would most likely be looking at using already created databases at this level of experience and at that level, it is good, with no actual code errors - there were a number of things that didn't work with SQL 2005 Express but that's my problem :-)
Anonymous More than 1 year ago