Part I: An SQL Concepts Overview
HOUR 1: Welcome to the World of SQL 1
SQL Definition and History 1
SQL Sessions 8
Types of SQL Commands 9
The Database Used in This Book 12
Summary 17
Q&A 17
Workshop 18
Part II: Building Your Database
HOUR 2: Defining Data Structures 21
What Is Data? 21
Basic Data Types 22
Summary 31
Q&A 31
Workshop 32
HOUR 3: Managing Database Objects 37
What Are Database Objects? 37
What Is a Schema? 37
Tables: The Primary Storage for Data 39
Integrity Constraints 49
Summary 54
Q&A 55
Workshop 55
HOUR 4: The Normalization Process 61
Normalizing a Database 61
Denormalizing a Database 69
Summary 70
Q&A 70
Workshop 71
HOUR 5: Manipulating Data 73
Overview of Data Manipulation 73
Populating Tables with New Data 74
Updating Existing Data 80
Deleting Data from Tables 82
Summary 83
Q&A 83
Workshop 84
HOUR 6: Managing Database Transactions 87
What Is a Transaction? 87
Controlling Transactions 88
Transactional Control and Database Performance 95
Summary 96
Q&A 96
Workshop 97
Part III: Getting Effective Results from Queries
HOUR 7: Introduction to the Database Query 99
What Is a Query? 99
Introduction to the SELECT Statement 99
Examples of Simple Queries 108
Summary 112
Q&A 112
Workshop 113
HOUR 8: Using Operators to Categorize Data 115
What Is an Operator in SQL? 115
Comparison Operators 116
Logical Operators 119
Conjunctive Operators 126
Negative Operators 129
Arithmetic Operators 133
Summary 136
Q&A 137
Workshop 137
HOUR 9: Summarizing Data Results from a Query 141
What Are Aggregate Functions? 141
Summary 150
Q&A 150
Workshop 150
HOUR 10: Sorting and Grouping Data 153
Why Group Data? 153
The GROUP BY Clause 154
GROUP BY Versus ORDER BY 159
CUBE and ROLLUP Expressions 161
The HAVING Clause 164
Summary 165
Q&A 166
Workshop 166
HOUR 11: Restructuring the Appearance of Data 169
ANSI Character Functions 169
Common Character Functions 170
Miscellaneous Character Functions 179
Mathematical Functions 183
Conversion Functions 183
Combining Character Functions 186
Summary 187
Q&A 188
Workshop 188
HOUR 12: Understanding Dates and Times 191
How Is a Date Stored? 191
Date Functions 193
Date Conversions 198
Summary 204
Q&A 204
Workshop 205
Part IV: Building Sophisticated Database Queries
HOUR 13: Joining Tables in Queries 207
Selecting Data from Multiple Tables 207
Understanding Joins 208
Join Considerations 217
Summary 221
Q&A 222
Workshop 222
HOUR 14: Using Subqueries to Define Unknown Data 225
What Is a Subquery? 225
Embedded Subqueries 231
Correlated Subqueries 233
Subquery Performance 234
Summary 235
Q&A 235
Workshop 236
HOUR 15: Combining Multiple Queries into One 239
Single Queries Versus Compound Queries 239
Compound Query Operators 240
Using ORDER BY with a Compound Query 246
Using GROUP BY with a Compound Query 248
Retrieving Accurate Data 250
Summary 250
Q&A 250
Workshop 251
Part V: SQL Performance Tuning
HOUR 16: Using Indexes to Improve Performance 255
What Is an Index? 255
How Do Indexes Work? 256
The CREATE INDEX Command 257
Types of Indexes 258
When Should Indexes Be Considered? 260
When Should Indexes Be Avoided? 261
Altering an Index 263
Dropping an Index 263
Summary 264
Q&A 264
Workshop 265
HOUR 17: Improving Database Performance 267
What Is SQL Statement Tuning? 267
Database Tuning Versus SQL Statement Tuning 268
Formatting Your SQL Statement 268
Full Table Scans 274
Other Performance Considerations 275
Cost-Based Optimization 279
Performance Tools 280
Summary 280
Q&A 281
Workshop 281
Part VI: Using SQL to Manage Users and Security
HOUR 18: Managing Database Users 285
User Management in the Database 285
The Management Process 288
Tools Utilized by Database Users 296
Summary 296
Q&A 297
Workshop 297
HOUR 19: Managing Database Security 299
What Is Database Security? 299
What Are Privileges? 301
Controlling User Access 304
Controlling Privileges Through Roles 308
Summary 310
Q&A 310
Workshop 311
Part VII: Summarized Data Structures
HOUR 20: Creating and Using Views and Synonyms 313
What Is a View? 313
Creating Views 316
WITH CHECK OPTION 320
Creating a Table from a View 321
Views and the ORDER BY Clause 322
Updating Data Through a View 322
Dropping a View 323
Performance Impact of Using Nested Views 323
What Is a Synonym? 324
Summary 325
Q&A 326
Workshop 326
HOUR 21: Working with the System Catalog 329
What Is the System Catalog? 329
How Is the System Catalog Created? 331
What Is Contained in the System Catalog? 331
System Catalog Tables by Implementation 333
Querying the System Catalog 334
Updating System Catalog Objects 336
Summary 337
Q&A 337
Workshop 338
Part VIII: Applying SQL Fundamentals in Today's World
HOUR 22: Advanced SQL Topics 339
Cursors 339
Stored Procedures and Functions 343
Triggers 346
Dynamic SQL 348
Call-Level Interface 349
Using SQL to Generate SQL 350
Direct Versus Embedded SQL 351
Windowed Table Functions 351
Working with XML 352
Summary 353
Q&A 353
Workshop 354
HOUR 23: Extending SQL to the Enterprise, the Internet, and the Intranet 355
SQL and the Enterprise 355
Accessing a Remote Database 357
SQL and the Internet 360
SQL and the Intranet 361
Summary 362
Q&A 363
Workshop 363
HOUR 24: Extensions to Standard SQL 367
Various Implementations 367
Example Extensions 370
Interactive SQL Statements 373
Summary 374
Q&A 375
Workshop 375
Part IX: Appendixes
APPENDIX A: Common SQL Commands 377
SQL Statements 377
SQL Clauses 381
APPENDIX B: Using the Databases for Exercises 383
Windows Installation Instructions for MySQL 383
Windows Installation Instructions for Oracle 386
Windows Installation Instructions for Microsoft SQL Server 388
APPENDIX C: Answers to Quizzes and Exercises 391
APPENDIX D: CREATE TABLE Statements for Book Examples 439
APPENDIX E: INSERT Statements for Data in Book Examples 443
APPENDIX F: Glossary 451
APPENDIX G: Bonus Exercises 455
INDEX 461
