Practical SQL : The Sequel

by Bowman, Judith

• ISBN13: 9780201616385
• ISBN10: 0201616386
• Format: Paperback
• Copyright: 2000-11-01
• Publisher: Pearson P T R
• More Book Details
• Purchase Benefits

Summary

In the real world, the SQL challenges you're likely to face go far beyond the "generic" basics taught by most books. Whether you're maintaining existing SQL code, enhancing it, or writing entirely new applications, you need to review all kinds of SQL, understand what's going on "under the hood," and translate SQL's features into real tools for solving problems. In Practical SQL: The Sequel, Judith Bowman builds on her best-selling Practical SQL, delivering techniques and code you can use right now to write queries that solve even your most complex problems. Organized around categories of problems -- not abstract SQL features -- Practical SQL: The Sequel includes extensive proven code that has been tested against multiple leading RDBMS systems. Bowman offers detailed solutions for controlling data displays using character, number, and date functions; cleaning dirty data; and translating values using a variety of methods. There's a full chapter on managing multiples. Finally, Bowman presents numbering solutions, and techniques for tuning queries. An extensive sample database is included, along with a single source reference containing information about leading SQL dialect variants. For every intermediate-to-advanced level database and business professional who wants to use SQL more effectively.

Table of Contents

List of Tables	p. xi
List of Figures	p. xiii
Acknowledgments	p. xv
Introduction	p. 1
In This Chapter	p. 1
Why This Book?	p. 1
Use, not Feature	p. 2
Lots of Examples	p. 2
Multiple Systems	p. 2
Legacy	p. 3
Tuning	p. 3
Who This Book Is For	p. 4
Contents	p. 4
Chapters	p. 4
Appendices	p. 6
Speaking Multiple SQLs	p. 6
SQL Engines	p. 7
SQL Dialects	p. 8
Following Conventions	p. 8
Text	p. 8
Code	p. 9
Understanding the msdpn Database	p. 11
Table Details	p. 13
Using the Examples	p. 17
Summary	p. 17
Handling Dirty Data	p. 19
In This Chapter	p. 19
Dirty Data	p. 19
Case	p. 20
Sort Order	p. 20
UPPER and LOWER in Searches	p. 23
UPPER and LOWER in UPDATE	p. 25
UPPER and LOWER with Dates	p. 26
INITCAP	p. 27
Space	p. 28
Removing Spaces	p. 31
Additional Features	p. 32
Size	p. 34
Actual Size	p. 34
Defined Size	p. 36
Matching Patterns	p. 37
Quoting Wildcards with ESCAPE	p. 38
LIKE Variants	p. 38
Datatypes and LIKE	p. 40
Locating Patterns	p. 41
How LOCATE Works	p. 42
Using LOCATE Functions	p. 43
Sounds Like	p. 46
BETWEEN	p. 49
Dealing with Dates	p. 50
Converting Dates (and Other Datatypes)	p. 50
Doing Math on Dates	p. 51
Finding Dates	p. 54
Summary	p. 60
Translating Values	p. 63
In This Chapter	p. 63
Why Translate?	p. 63
Case/Decode	p. 64
Case	p. 64
Decode	p. 67
Case/Decode Variations	p. 69
Handling NULL	p. 71
Coalesce/Isnull/Nvl	p. 71
Finding the First Non-NULL: COALESCE	p. 73
Nullif	p. 74
Point Functions	p. 77
Getting CASE Effects from Functions and Column Values	p. 77
Getting CASE Effects from Multiple Functions	p. 78
Union	p. 84
How UNION Works	p. 84
UNION and NULL	p. 86
UNION Problems?	p. 87
Joins and Outer Joins	p. 88
Joins and NULL Values	p. 90
Outer Joins	p. 91
Subqueries	p. 96
Subqueries and Displays	p. 96
Embedded Correlated Subqueries	p. 96
Correlated and Noncorrelated Subqueries	p. 98
TRANSLATE: Another Conditional Expression	p. 99
Summary	p. 103
Managing Multiples	p. 105
In This Chapter	p. 105
What's the Issue with Multiples?	p. 105
Capturing Duplicates	p. 106
Duplicates and a Holding Table	p. 106
Using ROWID to Remove Duplicates	p. 112
Finding Near-Duplicates	p. 114
Self-Join	p. 115
Unequal Joins	p. 117
Similar	p. 124
Locating Disconnected Rows	p. 125
Using Outer Joins	p. 126
Using NOT IN Subqueries	p. 132
Using MINUS	p. 134
Counting Items Based on Characteristics	p. 137
Grouping By a Subset	p. 137
Locating the Critical Element	p. 140
Groups and Outer Joins	p. 145
Figuring Distribution	p. 146
Checking Detail Against Master	p. 146
Two Products Together?	p. 150
Restore ordermaster	p. 152
Having	p. 153
Summary	p. 155
Navigating Numbers	p. 157
In This Chapter	p. 157
What's in a Number?	p. 157
Comparing Autonumbering Systems	p. 158
ASA: Default	p. 158
Transact-SQL: Column Property	p. 159
Oracle: CREATE SEQUENCE	p. 161
Informix: SERIAL Datatype	p. 162
Associated Issues	p. 162
Locating the High Value	p. 163
Group by, Count, Having Max(Count)	p. 163
FROM Subquery	p. 165
Row Counts of Various Sorts	p. 167
Creating Row Numbers	p. 169
System Numbers	p. 169
Your Numbers	p. 174
Finding the Top N: Six Approaches	p. 178
Row Limits and ORDER BY	p. 179
Row Numbers and HAVING	p. 179
Subquery	p. 183
Nested Subqueries	p. 184
Aggregates and Many Copies	p. 186
Cursors	p. 187
Picking Every Nth	p. 189
What Modulo Is	p. 190
Modulo in WHERE and HAVING	p. 192
Back to Nth Row	p. 194
Correlated Subquery	p. 195
Generating a Running Total	p. 196
Summary	p. 200
Tuning Queries	p. 203
In This Chapter	p. 203
Perform, #%and#@!	p. 203
Defining the Basic Problem	p. 204
Understanding the Optimizer and Associated Tools	p. 206
Getting Information on Indexes	p. 206
Checking the Optimizer	p. 210
SQL Conventions	p. 213
Managing the WHERE Clause	p. 214
Why a Table Scan?	p. 214
Data Distribution Statistics	p. 214
Disabling an Index with a Bad Where	p. 215
Comparing Columns in the Same Table	p. 215
Using Nonselective Indexes	p. 216
Doing Math on a Column	p. 217
Using Functions	p. 218
Finding Ranges with BETWEEN	p. 219
Matching with LIKE	p. 220
Comparing to NULL	p. 221
Negating with NOT	p. 222
Converting Values	p. 223
Using OR	p. 223
Finding Sets of Values with IN	p. 224
Using Multicolumn Indexes	p. 225
Creating Covering Indexes	p. 225
Joining Columns	p. 228
Sorting with DISTINCT and UNION	p. 230
Distinct	p. 230
Union	p. 231
Where	p. 233
Choosing Between HAVING and WHERE	p. 234
Looking at Views	p. 235
Forcing Indexes	p. 238
Summary	p. 238
Asking Performance Questions	p. 239
Using SQL to Write SQL	p. 241
In This Chapter	p. 241
Systematically Speaking	p. 241
Getting Meta-Data from System Catalogs	p. 242
Listing System Catalogs	p. 242
Writing Queries Using System Catalogs	p. 250
Using System Functions	p. 251
Getting Administrative Information	p. 252
Finding Today's Date	p. 255
Inserting Today's Date	p. 257
Writing SQL with SQL	p. 259
GRANTing Permissions	p. 259
Removing Junk Objects	p. 261
Creating Test Data with SQL	p. 263
Summary	p. 265
Understanding the Sample DB: msdpn	p. 267
In This Appendix	p. 267
MegaSysDataProNet Co	p. 267
Collecting the CREATE Scripts	p. 268
Adaptive Server Anywhere	p. 269
Sybase Adaptive Server Enterprise	p. 272
Microsoft SQL Server	p. 274
Oracle	p. 274
Informix	p. 276
Explaining the INSERT Scripts	p. 278
INSERT INTO customer	p. 278
INSERT INTO supplier	p. 280
INSERT INTO product	p. 281
INSERT INTO employee	p. 284
INSERT INTO ordermaster	p. 284
INSERT INTO orderetail	p. 286
Experimenting and Transaction Management	p. 288
Removing Data and Objects: DROP and DELETE Commands	p. 292
Remove Database	p. 292
DROP Commands	p. 292
DELETE FROM Command	p. 293
Summary	p. 293
Comparing Datatypes and Functions	p. 295
In This Appendix	p. 295
Comparatively Speaking	p. 295
Datatype Comparison	p. 295
Function Comparison	p. 297
Character (String) Functions	p. 297
Number Functions	p. 299
Date Functions	p. 300
Conditional Functions	p. 301
Sequential Number Methods	p. 302
Row Number and Row ID Methods	p. 302
Tuning Functions	p. 302
System Functions	p. 303
Join Syntax Comparison	p. 304
Notes on Environment and Display	p. 305
Setting Number Formats	p. 305
Defining Display Precision	p. 309
Defining Default Date Format	p. 310
Summary	p. 311
Using Resources	p. 313
In This Appendix	p. 313
Books	p. 313
General	p. 313
Informix	p. 314
Microsoft SQL Server	p. 314
mSQL/MySQL	p. 314
Oracle	p. 314
Sybase	p. 315
Transact-SQL	p. 315
Web Sites	p. 315
Database Vendors	p. 315
Other Offerings	p. 316
Newsgroups	p. 316
Index	p. 317
Table of Contents provided by Syndetics. All Rights Reserved.