Includes Coverage of Oracle and Microsoft SQL Implementations

PART I: AN SQL CONCEPTS OVERVIEWHour 1: Welcome to the World of SQL 1 SQL Definition and History 1 SQL Sessions 8 Types of SQL Commands 8 Canary Airlines: The Database Used in This Book 11 Summary 15 Q&A 16 Workshop 16 PART II: BUILDING YOUR DATABASEHour 2: Defining Data Structures 19 What Is Data? 19 Basic Data Types 20 Summary 28 Q&A 29 Workshop 29 Hour 3: Managing Database Objects 33 Database Objects and Schema 33 Tables: The Primary Storage for Data 35 Integrity Constraints 44 Summary 49 Q&A 50 Workshop 50 Hour 4: The Normalization Process 55 Normalizing a Database 55 Denormalizing a Database 63 Summary 64 Q&A 64 Workshop 64 Hour 5: Manipulating Data 67 Overview of Data Manipulation 67 Populating Tables with New Data 68 Updating Existing Data 73 Deleting Data from Tables 75 Summary 76 Q&A 77 Workshop 77 Hour 6: Managing Database Transactions 81 What Is a Transaction? 81 Controlling Transactions 82 Poor Transactional Control 90 Summary 90 Q&A 90 Workshop 91 PART III: GETTING EFFECTIVE RESULTS FROM QUERIESHour 7: Introduction to Database Queries 93 The SELECT Statement 93 Case-Sensitivity 101 Fundamentals of Query Writing 102 Summary 106 Q&A 106 Workshop 107 Hour 8: Using Operators to Categorize Data 109 What Is an Operator in SQL? 109 Comparison Operators 110 Logical Operators 113 Conjunctive Operators 120 Negative Operators 123 Arithmetic Operators 128 Summary 130 Q&A 131 Workshop 131 Hour 9: Summarizing Data Results from a Query 133 Aggregate Functions 133 Summary 141 Q&A 142 Workshop 142 Hour 10: Sorting and Grouping Data 145 Why Group Data? 145 The GROUP BY Clause 146 GROUP BY Versus ORDER BY 150 CUBE and ROLLUP Expressions 153 The HAVING Clause 155 Summary 156 Q&A 157 Workshop 157 Hour 11: Restructuring the Appearance of Data 159 ANSI Character Functions 159 Common Character Functions 160 Miscellaneous Character Functions 169 Mathematical Functions 172 Conversion Functions 173 Combining Character Functions 176 Summary 177 Q&A 177 Workshop 178 Hour 12: Understanding Dates and Times 181 How Is a Date Stored? 181 Date Functions 183 Date Conversions 188 Summary 193 Q&A 194 Workshop 194 PART IV: BUILDING SOPHISTICATED DATABASE QUERIESHour 13: Joining Tables in Queries 197 Selecting Data from Multiple Tables 197 Understanding Joins 198 Join Considerations 207 Summary 210 Q&A 210 Workshop 211 Hour 14: Using Subqueries to Define Unknown Data 213 What Is a Subquery? 213 Embedded Subqueries 219 Correlated Subqueries 223 Subquery Performance 225 Summary 225 Q&A 226 Workshop 226 Hour 15: Combining Multiple Queries into One 229 Single Queries Versus Compound Queries 229 Compound Query Operators 230 Using ORDER BY with a Compound Query 235 Using GROUP BY with a Compound Query 237 Retrieving Accurate Data 238 Summary 239 Q&A 239 Workshop 240 PART V: SQL PERFORMANCE TUNINGHour 16: Using Indexes to Improve Performance 243 What Is an Index? 243 How Do Indexes Work? 244 The CREATE INDEX Command 245 Types of Indexes 245 When Should Indexes Be Considered? 248 When Should Indexes Be Avoided? 248 Altering an Index 250 Dropping an Index 250 Summary 251 Q&A 251 Workshop 252 Hour 17: Improving Database Performance 253 What Is SQL Statement Tuning? 253 Database Tuning Versus SQL Statement Tuning 254 Formatting Your SQL Statement 254 Full Table Scans 260 Other Performance Considerations 261 Cost-Based Optimization 264 Summary 266 Q&A 266 Workshop 266 PART VI: USING SQL TO MANAGE USERS AND SECURITYHour 18: Managing Database Users 269 User Management in the Database 269 The Management Process 272 Tools Utilized by Database Users 279 Summary 279 Q&A 280 Workshop 280 Hour 19: Managing Database Security 283 What Is Database Security? 283 What Are Privileges? 284 Controlling User Access 287 Controlling Privileges Through Roles 291 Summary 293 Q&A 293 Workshop 294 PART VII: SUMMARIZED DATA STRUCTURESHour 20: Creating and Using Views and Synonyms 297 What Is a View? 297 Creating Views 300 Updating Data Through a View 307 Dropping a View 307 Performance Impact of Nested Views 308 What Is a Synonym? 308 Summary 310 Q&A 310 Workshop 311 Hour 21: Working with the System Catalog 313 What Is the System Catalog? 313 How Is the System Catalog Created? 314 What Is Contained in the System Catalog? 315 System Catalog Tables by Implementation 316 Querying the System Catalog 317 Updating System Catalog Objects 320 Summary 320 Q&A 321 Workshop 321 PART VIII: APPLYING SQL FUNDAMENTALS IN TODAY'S WORLDHour 22: Advanced SQL Topics 323 Cursors 323 Stored Procedures and Functions 326 Triggers 329 Dynamic SQL 331 Call-Level Interface 331 Using SQL to Generate SQL 332 Direct Versus Embedded SQL 333 Windowed Table Functions 333 Working with XML 334 Summary 335 Q&A 335 Workshop 336 Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet 337 SQL and the Enterprise 337 Accessing a Remote Database 339 SQL and the Internet 342 SQL and the Intranet 343 Summary 344 Q&A 344 Workshop 344 Hour 24: Extensions to Standard SQL 347 Various Implementations 347 Example Extensions 350 Interactive SQL Statements 353 Summary 354 Q&A 354 Workshop 355 PART IX: APPENDIXESAppendix A: Common SQL Commands 357 SQL Statements 357 SQL Clauses 360 Appendix B: Installing Oracle and Microsoft SQL 363 Windows Installation Instructions for Oracle 363 Windows Installation Instructions for Microsoft SQL Server 365 Appendix C: Answers to Quizzes and Exercises 369 Hour 1, "Welcome to the World of SQL" 369 Hour 2, "Defining Data Structures" 371 Hour 3, "Managing Database Objects" 373 Hour 4, "The Normalization Process" 374 Hour 5, "Manipulating Data" 376 Hour 6, "Managing Database Transactions" 379 Hour 7, "Introduction to the Database Queries" 380 Hour 8, "Using Operators to Categorize Data" 383 Hour 9, "Summarizing Data Results from a Query" 386 Hour 10, "Sorting and Grouping Data" 388 Hour 11, "Restructuring the Appearance of Data" 390 Hour 12, "Understanding Dates and Times" 391 Hour 13, "Joining Tables in Queries" 392 Hour 14, "Using Subqueries to Define Unknown Data" 394 Hour 15, "Combining Multiple Queries into One" 396 Hour 16, "Using Indexes to Improve Performance" 397 Hour 17, "Improving Database Performance" 399 Hour 18, "Managing Database Users" 403 Hour 19, "Managing Database Security" 404 Hour 20, "Creating and Using Views and Synonyms" 404 Hour 21, "Working with the System Catalog" 406 Hour 22, "Advanced SQL Topics" 407 Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet" 408 Hour 24, "Extensions to Standard SQL" 409 Appendix D: Bonus Exercises 411Appendix E: Glossary 425Index 429