| |
| |
| List of Tables | |
| |
| |
| List of Figures | |
| |
| |
| Acknowledgments | |
| |
| |
| |
| Introduction | |
| |
| |
| In This Chapter | |
| |
| |
| Why This Book? | |
| |
| |
| Use, not Feature | |
| |
| |
| Lots of Examples | |
| |
| |
| Multiple Systems | |
| |
| |
| Legacy | |
| |
| |
| Tuning | |
| |
| |
| Who This Book Is For | |
| |
| |
| Contents | |
| |
| |
| Chapters | |
| |
| |
| Appendices | |
| |
| |
| Speaking Multiple SQLs | |
| |
| |
| SQL Engines | |
| |
| |
| SQL Dialects | |
| |
| |
| Following Conventions | |
| |
| |
| Text | |
| |
| |
| Code | |
| |
| |
| Understanding the msdpn Database | |
| |
| |
| Table Details | |
| |
| |
| Using the Examples | |
| |
| |
| Summary | |
| |
| |
| |
| Handling Dirty Data | |
| |
| |
| In This Chapter | |
| |
| |
| Dirty Data | |
| |
| |
| Case | |
| |
| |
| Sort Order | |
| |
| |
| UPPER and LOWER in Searches | |
| |
| |
| UPPER and LOWER in UPDATE | |
| |
| |
| UPPER and LOWER with Dates | |
| |
| |
| INITCAP | |
| |
| |
| Space | |
| |
| |
| Removing Spaces | |
| |
| |
| Additional Features | |
| |
| |
| Size | |
| |
| |
| Actual Size | |
| |
| |
| Defined Size | |
| |
| |
| Matching Patterns | |
| |
| |
| Quoting Wildcards with ESCAPE | |
| |
| |
| LIKE Variants | |
| |
| |
| Datatypes and LIKE | |
| |
| |
| Locating Patterns | |
| |
| |
| How LOCATE Works | |
| |
| |
| Using LOCATE Functions | |
| |
| |
| Sounds Like | |
| |
| |
| BETWEEN | |
| |
| |
| Dealing with Dates | |
| |
| |
| Converting Dates (and Other Datatypes) | |
| |
| |
| Doing Math on Dates | |
| |
| |
| Finding Dates | |
| |
| |
| Summary | |
| |
| |
| |
| Translating Values | |
| |
| |
| In This Chapter | |
| |
| |
| Why Translate? | |
| |
| |
| Case/Decode | |
| |
| |
| Case | |
| |
| |
| Decode | |
| |
| |
| Case/Decode Variations | |
| |
| |
| Handling NULL | |
| |
| |
| Coalesce/Isnull/Nvl | |
| |
| |
| Finding the First Non-NULL: COALESCE | |
| |
| |
| Nullif | |
| |
| |
| Point Functions | |
| |
| |
| Getting CASE Effects from Functions and Column Values | |
| |
| |
| Getting CASE Effects from Multiple Functions | |
| |
| |
| Union | |
| |
| |
| How UNION Works | |
| |
| |
| UNION and NULL | |
| |
| |
| UNION Problems? | |
| |
| |
| Joins and Outer Joins | |
| |
| |
| Joins and NULL Values | |
| |
| |
| Outer Joins | |
| |
| |
| Subqueries | |
| |
| |
| Subqueries and Displays | |
| |
| |
| Embedded Correlated Subqueries | |
| |
| |
| Correlated and Noncorrelated Subqueries | |
| |
| |
| TRANSLATE: Another Conditional Expression | |
| |
| |
| Summary | |
| |
| |
| |
| Managing Multiples | |
| |
| |
| In This Chapter | |
| |
| |
| What's the Issue with Multiples? | |
| |
| |
| Capturing Duplicates | |
| |
| |
| Duplicates and a Holding Table | |
| |
| |
| Using ROWID to Remove Duplicates | |
| |
| |
| Finding Near-Duplicates | |
| |
| |
| Self-Join | |
| |
| |
| Unequal Joins | |
| |
| |
| Similar | |
| |
| |
| Locating Disconnected Rows | |
| |
| |
| Using Outer Joins | |
| |
| |
| Using NOT IN Subqueries | |
| |
| |
| Using MINUS | |
| |
| |
| Counting Items Based on Characteristics | |
| |
| |
| Grouping By a Subset | |
| |
| |
| Locating the Critical Element | |
| |
| |
| Groups and Outer Joins | |
| |
| |
| Figuring Distribution | |
| |
| |
| Checking Detail Against Master | |
| |
| |
| Two Products Together? | |
| |
| |
| Restore ordermaster | |
| |
| |
| Having | |
| |
| |
| Summary | |
| |
| |
| |
| Navigating Numbers | |
| |
| |
| In This Chapter | |
| |
| |
| What's in a Number? | |
| |
| |
| Comparing Autonumbering Systems | |
| |
| |
| ASA: Default | |
| |
| |
| Transact-SQL: Column Property | |
| |
| |
| Oracle: CREATE SEQUENCE | |
| |
| |
| Informix: SERIAL Datatype | |
| |
| |
| Associated Issues | |
| |
| |
| Locating the High Value | |
| |
| |
| Group by, Count, Having Max(Count) | |
| |
| |
| FROM Subquery | |
| |
| |
| Row Counts of Various Sorts | |
| |
| |
| Creating Row Numbers | |
| |
| |
| System Numbers | |
| |
| |
| Your Numbers | |
| |
| |
| Finding the Top N: Six Approaches | |
| |
| |
| Row Limits and ORDER BY | |
| |
| |
| Row Numbers and HAVING | |
| |
| |
| Subquery | |
| |
| |
| Nested Subqueries | |
| |
| |
| Aggregates and Many Copies | |
| |
| |
| Cursors | |
| |
| |
| Picking Every Nth | |
| |
| |
| What Modulo Is | |
| |
| |
| Modulo in WHERE and HAVING | |
| |
| |
| Back to Nth Row | |
| |
| |
| Correlated Subquery | |
| |
| |
| Generating a Running Total | |
| |
| |
| Summary | |
| |
| |
| |
| Tuning Queries | |
| |
| |
| In This Chapter | |
| |
| |
| Perform, #%and#@! | |
| |
| |
| Defining the Basic Problem | |
| |
| |
| Understanding the Optimizer and Associated Tools | |
| |
| |
| Getting Information on Indexes | |
| |
| |
| Checking the Optimizer | |
| |
| |
| SQL Conventions | |
| |
| |
| Managing the WHERE Clause | |
| |
| |
| Why a Table Scan? | |
| |
| |
| Data Distribution Statistics | |
| |
| |
| Disabling an Index with a Bad Where | |
| |
| |
| Comparing Columns in the Same Table | |
| |
| |
| Using Nonselective Indexes | |
| |
| |
| Doing Math on a Column | |
| |
| |
| Using Functions | |
| |
| |
| Finding Ranges with BETWEEN | |
| |
| |
| Matching with LIKE | |
| |
| |
| Comparing to NULL | |
| |
| |
| Negating with NOT | |
| |
| |
| Converting Values | |
| |
| |
| Using OR | |
| |
| |
| Finding Sets of Values with IN | |
| |
| |
| Using Multicolumn Indexes | |
| |
| |
| Creating Covering Indexes | |
| |
| |
| Joining Columns | |
| |
| |
| Sorting with DISTINCT and UNION | |
| |
| |
| Distinct | |
| |
| |
| Union | |
| |
| |
| Where | |
| |
| |
| Choosing Between HAVING and WHERE | |
| |
| |
| Looking at Views | |
| |
| |
| Forcing Indexes | |
| |
| |
| Summary | |
| |
| |
| Asking Performance Questions | |
| |
| |
| |
| Using SQL to Write SQL | |
| |
| |
| In This Chapter | |
| |
| |
| Systematically Speaking | |
| |
| |
| Getting Meta-Data from System Catalogs | |
| |
| |
| Listing System Catalogs | |
| |
| |
| Writing Queries Using System Catalogs | |
| |
| |
| Using System Functions | |
| |
| |
| Getting Administrative Information | |
| |
| |
| Finding Today's Date | |
| |
| |
| Inserting Today's Date | |
| |
| |
| Writing SQL with SQL | |
| |
| |
| GRANTing Permissions | |
| |
| |
| Removing Junk Objects | |
| |
| |
| Creating Test Data with SQL | |
| |
| |
| Summary | |
| |
| |
| |
| Understanding the Sample DB: msdpn | |
| |
| |
| In This Appendix | |
| |
| |
| MegaSysDataProNet Co | |
| |
| |
| Collecting the CREATE Scripts | |
| |
| |
| Adaptive Server Anywhere | |
| |
| |
| Sybase Adaptive Server Enterprise | |
| |
| |
| Microsoft SQL Server | |
| |
| |
| Oracle | |
| |
| |
| Informix | |
| |
| |
| Explaining the INSERT Scripts | |
| |
| |
| INSERT INTO customer | |
| |
| |
| INSERT INTO supplier | |
| |
| |
| INSERT INTO product | |
| |
| |
| INSERT INTO employee | |
| |
| |
| INSERT INTO ordermaster | |
| |
| |
| INSERT INTO orderetail | |
| |
| |
| Experimenting and Transaction Management | |
| |
| |
| Removing Data and Objects: DROP and DELETE Commands | |
| |
| |
| Remove Database | |
| |
| |
| DROP Commands | |
| |
| |
| DELETE FROM Command | |
| |
| |
| Summary | |
| |
| |
| |
| Comparing Datatypes and Functions | |
| |
| |
| In This Appendix | |
| |
| |
| Comparatively Speaking | |
| |
| |
| Datatype Comparison | |
| |
| |
| Function Comparison | |
| |
| |
| Character (String) Functions | |
| |
| |
| Number Functions | |
| |
| |
| Date Functions | |
| |
| |
| Conditional Functions | |
| |
| |
| Sequential Number Methods | |
| |
| |
| Row Number and Row ID Methods | |
| |
| |
| Tuning Functions | |
| |
| |
| System Functions | |
| |
| |
| Join Syntax Comparison | |
| |
| |
| Notes on Environment and Display | |
| |
| |
| Setting Number Formats | |
| |
| |
| Defining Display Precision | |
| |
| |
| Defining Default Date Format | |
| |
| |
| Summary | |
| |
| |
| |
| Using Resources | |
| |
| |
| In This Appendix | |
| |
| |
| Books | |
| |
| |
| General | |
| |
| |
| Informix | |
| |
| |
| Microsoft SQL Server | |
| |
| |
| mSQL/MySQL | |
| |
| |
| Oracle | |
| |
| |
| Sybase | |
| |
| |
| Transact-SQL | |
| |
| |
| Web Sites | |
| |
| |
| Database Vendors | |
| |
| |
| Other Offerings | |
| |
| |
| Newsgroups | |
| |
| |
| Index | |