| Acknowledgments | p. xvii |
| Introduction | p. xix |
| Learning the Essentials | |
| Accessing the Database | p. 3 |
| Connecting to the Database | p. 5 |
| Using a Query Tool | p. 8 |
| Using ODBC | p. 10 |
| Using OLE DB | p. 19 |
| Connecting from a Program | p. 21 |
| Using a Connection Library | p. 22 |
| Using DAO | p. 28 |
| Using ADO | p. 30 |
| Create a Connection | p. 32 |
| Mastery Check | p. 33 |
| Retrieving Data | p. 35 |
| Using Select | p. 37 |
| Adding a Where Clause | p. 44 |
| Adding Order By | p. 49 |
| Adding Summaries | p. 50 |
| Adding a Having Clause | p. 54 |
| Joining Tables | p. 56 |
| Combining Fields and Creating Columns | p. 62 |
| Presenting Data | p. 64 |
| Mastery Check | p. 67 |
| Inserting Data | p. 69 |
| Using Insert | p. 70 |
| Using Select with Insert | p. 81 |
| Adding a Where Clause | p. 83 |
| Adding a Having Clause | p. 84 |
| Using Select Into | p. 85 |
| Data Entry | p. 87 |
| Mastery Check | p. 92 |
| Updating Data | p. 93 |
| Using Update | p. 94 |
| Data Validation | p. 95 |
| Adding a Where Clause | p. 98 |
| Adding a Having Clause | p. 101 |
| Using Multiple Tables | p. 104 |
| Update Data | p. 107 |
| Mastery Check | p. 113 |
| Deleting Data | p. 115 |
| Deleting Data | p. 116 |
| Adding a Where Clause | p. 117 |
| Using a Query to Control Deletion | p. 119 |
| Using Transactions | p. 120 |
| Data Deletion | p. 124 |
| Mastery Check | p. 126 |
| Creating Tables | p. 129 |
| Using Create Table | p. 130 |
| Planning Keys, Primary and Foreign | p. 135 |
| Normalizing Data | p. 137 |
| Adding Indexes | p. 141 |
| Adding Constraints | p. 143 |
| Altering Tables | p. 147 |
| Deleting Tables | p. 148 |
| Fix the Recordset Handling Bugs | p. 149 |
| Mastery Check | p. 151 |
| Creating Databases | p. 153 |
| Using Create Database | p. 154 |
| Planning Tables | p. 157 |
| Normalizing Tables | p. 159 |
| Create the Portfolio Database | p. 166 |
| Planning Security | p. 170 |
| Fix Bugs | p. 173 |
| Mastery Check | p. 175 |
| Building Skills | |
| Using Operators | p. 179 |
| Using Relational and Boolean Operators | p. 180 |
| Finding What Exists | p. 183 |
| Finding Something in a Set | p. 184 |
| Finding Items In Between | p. 185 |
| Finding In, Any, All, or Some | p. 185 |
| Using Like and Is Null | p. 187 |
| Build an Administrative Tool | p. 190 |
| Mastery Check | p. 194 |
| Using Functions | p. 197 |
| Aggregating Data | p. 198 |
| AVG | p. 198 |
| COUNT | p. 199 |
| MAX | p. 199 |
| MIN | p. 200 |
| SUM | p. 200 |
| STDEV | p. 201 |
| STDEVP | p. 202 |
| VAR | p. 202 |
| VARP | p. 203 |
| Fix the Bug | p. 204 |
| Handling Dates and Times | p. 204 |
| DATEADD | p. 205 |
| DATEDIFF | p. 205 |
| DATENAME | p. 206 |
| DATEPART | p. 206 |
| DAY | p. 207 |
| GETDATE | p. 207 |
| MONTH | p. 208 |
| YEAR | p. 208 |
| Test Functions | p. 209 |
| Using Math Functions | p. 210 |
| ABS | p. 210 |
| ACOS | p. 210 |
| ASIN | p. 211 |
| ATAN | p. 211 |
| ATN2 | p. 212 |
| CEILING | p. 212 |
| COS | p. 212 |
| COT | p. 213 |
| DEGREES | p. 213 |
| EXP | p. 214 |
| FLOOR | p. 214 |
| LOG | p. 215 |
| LOG10 | p. 215 |
| PI | p. 216 |
| POWER | p. 216 |
| RADIANS | p. 216 |
| RAND | p. 217 |
| ROUND | p. 217 |
| SIGN | p. 218 |
| SIN | p. 218 |
| SQUARE | p. 219 |
| SQRT | p. 219 |
| TAN | p. 220 |
| Using String Functions | p. 221 |
| ASCII | p. 221 |
| CHAR | p. 221 |
| CHARINDEX | p. 222 |
| DIFFERENCE | p. 222 |
| LEFT | p. 223 |
| LEN | p. 223 |
| LOWER | p. 224 |
| LTRIM | p. 224 |
| NCHAR | p. 225 |
| PATINDEX | p. 225 |
| QUOTENAME | p. 226 |
| REPLACE | p. 226 |
| REPLICATE | p. 227 |
| REVERSE | p. 227 |
| RIGHT | p. 228 |
| RTRIM | p. 228 |
| SOUNDEX | p. 229 |
| SPACE | p. 229 |
| STR | p. 230 |
| STUFF | p. 230 |
| SUBSTR | p. 231 |
| UNICODE | p. 231 |
| UPPER | p. 232 |
| Using Critical System Functions | p. 233 |
| CASE | p. 233 |
| CAST | p. 234 |
| CONVERT | p. 234 |
| ISDATE | p. 235 |
| ISNULL | p. 236 |
| ISNUMBERIC | p. 236 |
| Enter Your Own Database Connections | p. 238 |
| Mastery Check | p. 241 |
| Building Subqueries | p. 243 |
| Selecting an Item | p. 246 |
| Setting a Condition | p. 247 |
| Substituting for Joins | p. 249 |
| Deleting, Updating, and Inserting | p. 251 |
| Delete Records | p. 254 |
| Mastery Check | p. 257 |
| Building Views | p. 259 |
| Rationalizing the Use of Views | p. 261 |
| Create the Query | p. 263 |
| Create the View | p. 265 |
| Create a View | p. 266 |
| Add Forms | p. 268 |
| Mastery Check | p. 284 |
| Building Stored Procedures | p. 285 |
| Editing the Procedure | p. 288 |
| Using Create Procedure | p. 290 |
| Convert Queries | p. 293 |
| Calling the Procedure | p. 294 |
| Using the Results | p. 295 |
| Convert Queries to Stored Procedures | p. 296 |
| Mastery Check | p. 297 |
| Programming in Stored Procedures | p. 299 |
| Using Variables | p. 301 |
| Using Flow Control | p. 303 |
| Returning Values | p. 308 |
| Raising Errors | p. 310 |
| Assemble the Stored Procedure | p. 313 |
| Mastery Check | p. 314 |
| Using Parameters | p. 315 |
| Creating Parameters | p. 319 |
| Passing Values | p. 321 |
| Add Parameters | p. 323 |
| Using the Results | p. 323 |
| Mastery Check | p. 327 |
| Using Cursors | p. 329 |
| Declaring Cursors | p. 331 |
| Manipulating Cursors | p. 333 |
| Updating and Searching Cursors | p. 336 |
| Closing and Deallocating Cursors | p. 339 |
| Assemble a Stored Procedure | p. 339 |
| Mastery Check | p. 342 |
| Building Unions | p. 343 |
| Using the Union Operator | p. 345 |
| Adhering to Union Constraints | p. 346 |
| Building Practical Unions | p. 348 |
| Build a Union | p. 350 |
| Mastery Check | p. 353 |
| Winding Down the Portfolio Project | p. 355 |
| The Client's Structure | p. 357 |
| The Registration Process | p. 363 |
| Write the Lookups | p. 364 |
| Planning Required Insert Procedures | p. 365 |
| Write the INSERTS | p. 366 |
| Planning Required Unregister Procedures | p. 368 |
| Write the Unregister Procedures | p. 369 |
| Planning Required Checkout Procedures | p. 370 |
| Write the Checkout Procedure | p. 371 |
| Planning Required Check-In Procedures | p. 371 |
| Write the Check-In Procedure | p. 371 |
| Planning Required Delete Procedures | p. 372 |
| Write the Delete Procedure | p. 373 |
| A Client Architecture | p. 373 |
| Mastery Check | p. 377 |
| Answers to Mastery Checks | p. 379 |
| Accessing the Database | p. 380 |
| Retrieving Data | p. 380 |
| Inserting Data | p. 381 |
| Updating Data | p. 383 |
| Deleting Data | p. 384 |
| Creating Tables | p. 385 |
| Creating Databases | p. 386 |
| Using Operators | p. 387 |
| Using Functions | p. 388 |
| Building Subqueries | p. 389 |
| Building Views | p. 390 |
| Building Stored Procedures | p. 391 |
| Programming in Stored Procedures | p. 393 |
| Using Parameters | p. 394 |
| Using Cursors | p. 395 |
| Building Unions | p. 396 |
| Winding Down the Portfolio Project | p. 397 |
| A Little SQL Theory | p. 399 |
| Declarative, Not Procedural | p. 400 |
| A Relational Model | p. 401 |
| The Structure of the Language | p. 403 |
| Data Definition Language | p. 403 |
| Data Manipulation Language | p. 404 |
| Data Control Language | p. 404 |
| Dependence on Transactions | p. 405 |
| Focus on Integrity | p. 406 |
| Conclusion | p. 407 |
| Index | p. 433 |
| Table of Contents provided by Syndetics. All Rights Reserved. |
