In just 24 lessons of one hour or less, you will learn professional techniques to design and build efficient databases and query them to extract useful information. Using a straightforward, step-by-step approach, each lesson builds on the previous one, allowing you to learn the essentials of ANSI SQL from the ground up.   Example code demonstrates the authors’ professional techniques, while exercises written for MySQL offer the reader hands-on learning with an open-source database. Included are advanced techniques for using views, managing transactions, database administration, and extending SQL.   Step-by-step instructions carefully walk you through the most common SQL tasks. Q&As, Quizzes, and Exercises at the end of each chapter help you test your knowledge. Notes and Tips point out shortcuts and solutions. New terms are clearly defined and explained.   Learn how to… Use SQL-2003, the latest standard for the Structured Query Language Design and deploy efficient, secure databases Build advanced queries for information retrieval Sort, group, and summarize information for best presentation Tune databases and queries for maximum performance Understand database administration and security techniques   For more than ten years the authors have studied, applied, and documented the SQL standard and its application to critical database systems.   Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders of Perpetual Technologies, Inc. (PTI), a fast-growing IT management and consulting firm which specializes in database technologies. They taught database courses for Indiana University–Purdue University in Indianapolis for five years and have authored more than a dozen books on Oracle, SQL, database design, and the high availability of critical systems.   Arie D. Jones is Senior SQL Server database administrator and analyst for PTI. He is a regular speaker at technical events and has authored several books and articles.   Category: Database Covers: ANSI SQL User Level: Beginning–Intermediate   Register your book at informit.com/title/9780672330186 for convenient access to updates and corrections as they become available.

Table of Contents:
Introduction                                                                            1     What This Book Intends to Accomplish......................... 1     What We Added to This Edition..................................... 1     What You Need............................................................ 2     Conventions Used in This Book..................................... 2     ANSI SQL and Vendor Implementations........................ 3     Understanding the Examples and Exercises................... 3 Part I: A SQL Concepts Overview Hour 1: Welcome to the World of SQL                                   7     SQL Definition and History............................................ 7     SQL Sessions............................................................. 14     Types of SQL Commands........................................... 15     The Database Used in This Book................................. 17     Summary.................................................................. 22     Q&A.......................................................................... 23     Workshop................................................................. 24 Part II: Building Your Database Hour 2: Defining Data Structures                                          27     What Is Data?............................................................ 27     Basic Data Types....................................................... 28     Summary.................................................................. 36     Q&A.......................................................................... 37     Workshop................................................................. 37 Hour 3: Managing Database Objects                                    41     What Are Database Objects?...................................... 41     What Is a Schema?.................................................... 42     A Table: The Primary Storage for Data........................ 44     Integrity Constraints.................................................. 52     Summary.................................................................. 56     Q&A.......................................................................... 57     Workshop................................................................. 58 Hour 4: The Normalization Process                                      61     Normalizing a Database............................................. 61     Denormalizing a Database.......................................... 69     Summary.................................................................. 69     Q&A.......................................................................... 70     Workshop................................................................. 70 Hour 5: Manipulating Data                                                    73     Overview of Data Manipulation................................... 73     Populating Tables with New Data................................ 74     Updating Existing Data............................................... 79     Deleting Data from Tables.......................................... 81     Summary.................................................................. 82     Q&A.......................................................................... 82     Workshop................................................................. 83 Hour 6: Managing Database Transactions                             87     What Is a Transaction?............................................... 87     Controlling Transactions............................................. 88     Transactional Control and Database Performance........ 95     Summary.................................................................. 95     Q&A.......................................................................... 96     Workshop................................................................. 96 Part III: Getting Effective Results from Queries Hour 7: Introduction to the Database Query                       101     What Is a Query?..................................................... 101     Introduction to the SELECT Statement...................... 101     Examples of Simple Queries..................................... 109     Summary................................................................ 113     Q&A........................................................................ 113     Workshop............................................................... 114 Hour 8: Using Operators to Categorize Data                      117     What Is an Operator in SQL?.................................... 117     Comparison Operators............................................. 118     Logical Operators..................................................... 121     Conjunctive Operators............................................. 127     Negative Operators.................................................. 130     Arithmetic Operators................................................ 134     Summary................................................................ 138     Q&A........................................................................ 138     Workshop............................................................... 138 Hour 9: Summarizing Data Results from a Query                141     What Are Aggregate Functions?................................ 141     Summary................................................................ 149     Q&A........................................................................ 149     Workshop............................................................... 149 Hour 10: Sorting and Grouping Data                                  151     Why Group Data?.................................................... 151     The GROUP BY Clause.............................................. 152     GROUP BY Versus ORDER BY.................................... 156     The HAVING Clause.................................................. 159     Summary................................................................ 160     Q&A........................................................................ 160     Workshop............................................................... 161 Hour 11: Restructuring the Appearance of Data                  165     ANSI Character Functions......................................... 165     Various Common Character Functions...................... 166     Miscellaneous Character Functions............................ 175     Mathematical Functions............................................ 178     Conversion Functions............................................... 179     Combining Character Functions................................ 181     Summary................................................................ 182     Q&A........................................................................ 182     Workshop............................................................... 183 Hour 12: Understanding Dates and Times                           185     How Is a Date Stored?............................................. 186     Date Functions........................................................ 187     Date Conversions.................................................... 192     Summary................................................................ 197     Q&A........................................................................ 197     Workshop............................................................... 198 Part IV: Building Sophisticated Database Queries Hour 13: Joining Tables in Queries                                      203     Selecting Data from Multiple Tables........................... 203     Types of Joins......................................................... 204     Join Considerations.................................................. 214     Summary................................................................ 218     Q&A........................................................................ 218     Workshop............................................................... 219 Hour 14: Using Subqueries to Define Unknown Data           221     What Is a Subquery?................................................ 221     Embedded Subqueries.............................................. 227     Correlated Subqueries.............................................. 229     Summary................................................................ 230     Q&A........................................................................ 231     Workshop............................................................... 231 Hour 15: Combining Multiple Queries into One                    235     Single Queries Versus Compound Queries................. 235     Compound Query Operators.................................... 236     Using ORDER BY with a Compound Query................. 242     Using GROUP BY with a Compound Query................. 244     Retrieving Accurate Data.......................................... 246     Summary................................................................ 246     Q&A........................................................................ 246     Workshop............................................................... 247 Part V: SQL Performance Tuning Hour 16: Using Indexes to Improve Performance               253     What Is an Index?.................................................... 253     How Do Indexes Work?........................................... 254     The CREATE INDEX Command.................................. 255     Types of Indexes..................................................... 255     When Should Indexes Be Considered?....................... 258     When Should Indexes Be Avoided?........................... 259     Dropping an Index.................................................... 260     Summary................................................................ 261     Q&A........................................................................ 261     Workshop............................................................... 262 Hour 17: Improving Database Performance                        265     What Is SQL Statement Tuning?............................... 265     Database Tuning Versus SQL Statement Tuning......... 266     Formatting Your SQL Statement............................... 266     Full Table Scans....................................................... 272     Other Performance Considerations........................... 273     Performance Tools................................................... 276     Summary................................................................ 276     Q&A........................................................................ 277     Workshop............................................................... 278 Part VI: Using SQL to Manage Users and Security Hour 18: Managing Database Users                                   283     Users Are the Reason.............................................. 284     The Management Process........................................ 286     Tools Utilized by Database Users.............................. 293     Summary................................................................ 294     Q&A........................................................................ 294     Workshop............................................................... 295 Hour 19: Managing Database Security                                297     What Is Database Security?...................................... 297     What Are Privileges?................................................. 298     Controlling User Access............................................ 302     Controlling Privileges Through Roles.......................... 305     Summary................................................................ 307     Q&A........................................................................ 308     Workshop............................................................... 309 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     Updating Data Through a View.................................. 321     Creating a Table from a View.................................... 322     Views and the ORDER BY Clause............................... 323     Dropping a View....................................................... 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................................... 335     Updating System Catalog Objects............................ 337     Summary................................................................ 337     Q&A........................................................................ 338     Workshop............................................................... 338 Part VIII: Applying SQL Fundamentals in Today’s World Hour 22: Advanced SQL Topics                                          343     Cursors................................................................... 343     Stored Procedures and Functions............................. 346     Triggers................................................................... 349     Dynamic SQL........................................................... 351     Call-Level Interface.................................................. 352     Using SQL to Generate SQL...................................... 352     Direct Versus Embedded SQL................................... 353     Windowed Table Functions....................................... 354     Working with XML.................................................... 354     Summary................................................................ 355     Q&A........................................................................ 356     Workshop............................................................... 356 Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet      359     SQL and the Enterprise............................................. 359     Accessing a Remote Database.................................. 361     SQL and the Internet................................................ 364     SQL and the Intranet................................................ 365     Summary................................................................ 366     Q&A........................................................................ 367     Workshop............................................................... 367 Hour 24: Extensions to Standard SQL                                369     Various Implementations.......................................... 369     Example Extensions................................................. 372     Interactive SQL Statements..................................... 375     Summary................................................................ 376     Q&A........................................................................ 377     Workshop............................................................... 377 Part IX: Appendixes Appendix A: Common SQL Commands                               381 Appendix B: Using MySQL for Exercises                              387 Appendix C: Answers to Quizzes and Exercises                   391 Appendix D: CREATE TABLE Statements for Book Examples 435 Appendix E: INSERT Statements for Book Examples            437 Appendix F: Bonus Exercises                                              441 Glossary                                                                             447 TOC, 0672330180, MF