Practical SQL : The Sequel

Read an Excerpt

PREFACE:

Why This Book?

1. "Introduction" assumes that you've already started your SQL career. It explains the book approach, organization, and conventions, and lists the SQL systems used. It provides a brief summary of the sample database.


2. "Handling Dirty Data" explores SQL functions and predicates, with suggestions on using them for finding and fixing dirty data–data with case or space or size problems or data containing embedded garbage. You get practice in UPPER/LOWER, TRIM, CHAR_LENGTH, SUBSTR, concate nate, POSITION, and SOUNDEX. You also examine LIKE variants and some things you can do with BETWEEN. The chapter closes with a sec tion on dates–doing math on them, changing their display format, and matching them.


3. Translating Values" presents a number of ways to expand a code (dis-play "male" for 1, "female" for 2). Here's where you learn about DECODE and CASE. You'll also find explanations of other methods of doing the same thing–characteristic functions, UNION, joins and outer joins, and embedded subqueries. The chapter includes a summary of other conditional elements, including ISNULL, NVL, COALESCE, and TRANSLATE. Functions include LPAD, REPEAT/REPLICATE, and SPACE.


4. "Managing Multiples" is additional techniques for handling dirty data, but here it is more significant soiling than a few letters or spaces. You'll track down duplicate rows, locate near-duplicate entries, rescue disconnected rows, find out how to group items by some subset of characteristics, and look at distribution. In the process, you'll practice some important techniques, including GROUP BY, aggregates, self-joins, unequal joins, MINUS, HAVING, and outer joins. You'll also work with subqueries. To prevent future problems with multiples, you examine unique indexes and foreign key constraints.


5. "Navigating Numbers" starts out with a comparison of autonumbering mechanisms in the five target systems, with examples of each. These methods include default, column property, sequence object, and datatype. Next, there is an interesting collection of code, treated together because all use similar elements, often GROUP BY, COUNT, and unequal joins. Sections include finding the high value, using row numbers, getting the top N, locating every Nth, and calculating a running total. In most cases there are alternative methods that you can compare. The section on top N, for example, includes six approaches, from row limits and subqueries to cursors.


6. "Tuning Queries" explores indexes and the optimizer and ways to get information about them from your system. Then it compares WHERE clauses that can take advantage of indexes with those that can't, urging the SQL programmer not to use IN where a range will work or do math on a column unnecessarily. Multicolumn and covering indexes are the next topics, followed by some hints on joins and on eliminating unneeded sorting (as manifested in DISTINCT and UNION). HAVING versus WHERE performance issues and cautions on views fill out the picture. The chapter concludes with a list of questions you can ask when you have performance problems and a discussion of forcing indexes.



7. "Using SQL to Write SQL" reviews system catalogs, the tables or views that store meta-data about the system (users, tables, space, permissions and so on). These catalogs differ greatly from vendor to vendor in specific tables and columns, but they all supply the same kind of information. To use them effectively, you need to find out what system functions your RDBMS offers. Once you have an understanding of the system catalogs and system functions, you can use SQL to generate SQL–a technique often used to write cleanup and permission scripts. You can apply similar skills to the problem of test data.

Read More