1. Starting Microsoft SQL Server 2005 ................... ............. 1
Starting Microsoft SQL Server 2005 and SQL Server 2005's Management Studio 1
Creating a Database in Microsoft SQL Server 2005 3
The Query Editor 8
Creating Tables Using the Load Script 12
Viewing Table Definitions 13
Modifying Table Definitions 14
Viewing Table Data 16
Deleting a Table 18
Deleting a Database 18
Entering a SQL Query or Statement 19
Parsing a Query 20
Executing a Query 20
Saving a Query 21
Displaying the Results 21
Stopping Execution of a Long Query 23
Printing the Query and Results 23
Customizing SQL Server 2005 24
Summary 25
Review Questions 25
Exercises . 25
2. Beginning SQL Commands in SQL Server .......... ........ 26
Displaying Data with the SELECT Statement 26
Displaying or SELECTing Rows or Tuples from a Table 38
The COUNT Function 44
The ROWCOUNT Function 46
Using Aliases 47
Synonyms 52
Adding Comments to SQL Statements 53
Some Conventions for Writing SQL Statements 54
A Few Notes About SQL Server 2005 Syntax 54
Summary 55
Review Questions 55
Exercises 56
3. Creating, Populating, Atering, and Deleting Tables .. ....... 58
Data Types in SQL Server 2005 58
Creating a Table 65
Inserting Values intoo a Table 67
The UPDATE Command 73
The ALTER TABLE Command 74
The DELETE Command 78
Deleting a Table 79
Summary 79
Review Questions 79
Exercises 80
References 8 1
4. Joins ,........... ...... ..82
The JOIN 82
The Cartesian Product 89
Equi-Joins and Non-Equi-Joins 91
Self Joins 92
Using ORDER BY with a Join 94
Joinng M ore Than Two Tables 95
The OUTER JOIN 96
Summary 101
Review Questions 101
Exercises 101
5. Fu nctions ..... .............. ...... .. ....... ..... ... 105
Aggregate Functions 106
Row -Level Functions 110
Other Functions 116
String Functions 121
CONVERSION Functions 134
DATE Functions 136
Summary 143
Review Questions 144
Exercises 146
"6. Query Development and Derived Structures ....... .......... 150
Query Development 150
Parentheses in SQL Expressions 153
Derived Structures 157
Query Development with Derived Structures 169
Summary 174
Review Questions 174
Exercises 175
7. Set Operations ........ . .... 178
Introducing Set Operations 178
The UNION Operation 180
The UNION ALL Operation 183
Handling UNION and UNION ALL Situations with an
Mnequal Number of Columns 183
The IN and NOT..IN Predicates 185
The Difference Operation 194
ihe Union and the Join 197
A UNION Used to Implement a Full Outer Join 202
Summary 203
Review Questions 203
Exercises 204
Optional Exercise 207
8. Joins VersusSubqueries 208
Subquery with an IN Predicate 208
The Subquery as a Join 210
When the Toin Cannot Be Turned into a Subquery 212
More Examples Involving Joins and IN 213
Using Subqueries with Operators 217
Summary 219
Review Questions 219
Exercises 219
9. Aggregation and GROUP BY .. ........ ..222
A SELECT in Modified BNF 222
The GROUP BY Clause 222
The HAVING Clause 227
GROUP BY and HAVING: Aggregates of Aggregates 229
Auditing in Subqueries 233
Nulls Revisited 236
Summary 239
Review Questions 239
Exercises 240
10. Correlated Subqueries ....... .. ... 242
Noncorrelated Subqueries 242
Correlated Subqueries 4 243
Existence Queries and Correlation 245
"SQL Universal and Existential Quaifiers 255
Summrary 261
Review Questions 261
Exercises 261
11. Indexes and Constraints on Tables . . ... . . 265
he "Simple" CREATE TABLE 265
Indexes 266
Constrainmts 269
Summary 288
Review Questions 288
Exercises 289
A. The Student Database and Other Tables Used in This Boo 292
B. Script Used to Create the
Student course Database .. ..... . . .. . 295
Glossary of Terms ............. 307
Important Commands and Functions ...... 311