Hjem

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