Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes
Published on 9. August 2007
Book
Paperback/Softback
368 pages
978-0-672-32867-1 (ISBN)
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.
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.
More details
Other editions
New editions
Book
01/2017
2nd Edition
Sams Publishing
€27.22
Article exhausted; check different version
Person
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/.
Content
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
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