Practical SQL The Sequel (With CD-ROM) - Softcover

Judith S. Bowman

Softcover

ISBN 10: 0201616386 ISBN 13: 9780201616385

Publisher: Addison-Wesley, 2000

Synopsis

Database specialist Bowman takes off where her 1996 Practical SQL Handbook leaves off, going beyond the basic query structure and simple examples to explore the complexities of using Structured Query Language to meet real-world business needs. She describes dealing with legacy systems, inherited problematic code, dirty data, and query tuning for better performance. She assumes readers to have a solid foundation in databases and the language. The accompanying disk contains the entire database used as an example throughout the text. Annotation c. Book News, Inc., Portland, OR (booknews.com)

About this title

From the Inside Flap

SQL (pronounced "sequel") is the premier language for relational database management systems (RDBMSs)

Why This Book?

SQL (pronounced "sequel") is the premier language for relational database management systems (RDBMSs). If you work with databases, you need to know it. This book assumes that you know the fundamentals and want to move on.

There are lots of books on learning basic SQL, and more are being published all the time. You can find excellent general tutorials, references, and vendor specific manuals. Classes and videos abound, too. Unfortunately, most working database applications don’t use basic SQL. After you read an introductory book or get some training, you’re thrown into a world of complex code and told to "maintain this–don’t change it, just keep it working" or "fix this." The lines you look at have only a passing similarity to the things you learned from that great book or in class. How do you make the transition?
This book aims to help you over the classroom-to-reality hump in five ways. Information is organized by use, rather than by feature.
The text is code-heavy, and all the examples use the same database.
Every example was tested on multiple systems–Adaptive Server Anywhere (on the CD), Oracle, Informix, Microsoft SQL Server, and Sybase Adaptive Server Enterprise.
Legacy systems and inherited problems are given special attention.
SQL tuning notes help you avoid bad performance.

  Who this Book is For

This book is for you, the SQL user who understands the basics and wants to know more.

You’ve been using a GUI report writer, and you’re trying to do things it can’t; or you’d like to stop being at the mercy of the system guys by giving them clearer instructions or doing more of the coding yourself.
Your opportunities for practice are limited and the code templates you find turn out to be based on a specific system or on a theoretical model– not really applicable to your situation.
The SQL dialect you’re using on the job is different from the one you learned in class, or you are working with multiple systems.
You’re supporting code some long gone employee wrote, which doesn’t seem to work right and is full of stuff you’ve never seen before.
Some of the queries you see seem more complicated than necessary, and you wonder if you could do anything to improve performance. This book will help you tackle new assignments, read inherited code, and make improvements to it. Start by looking up a problem. Run the code, then modify a few things to make sure you understand how it works. Try applying the method to similar situations in your own database.
Contents

You don’t need to read the book from start to end–you can jump in at any point. If a topic is treated in one chapter and mentioned in another, the shorter treatment refers to the longer one. Chapters

Here are the chapters and their contents.

"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.
"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.
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.
"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.
"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.
"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.

"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.
Appendices

The appendices contain supplementary materials. "Understanding the Sample DB: MSDPN" provides information about the sample database, including hard copy of the scripts that create the database for the five test systems. These scripts are on the CD in electronic form. Finally, there are notes on transaction commands–SQL statements you can use to cancel data modifications (if you plan ahead) and on deleting data or dropping tables, should you need to start over again. "Comparing Datatypes and Functions" is all the little SQL dialect variant charts merged into a big one for your convenience. Here you’ll see datatype information and tables summarizing variations in character, number, date, convert, conditional, tuning, and system functions. There is also a table on outer join syntax and notes on environment. "Using Resources" includes books, Web sites, and newsgroups you might find interesting.

From the Back Cover

For those who are working with SQL systems--or preparing to do so--this book offers information organized by use rather than by feature. Therefore, readers can turn to specific business problems and learn how to solve them with the appropriate SQL features. In particular, the book focuses on the real-world challenges of dealing with legacy systems, inherited problematic code, dirty data, and query tuning for better performance.

The following important topics are addressed:

* Finding and fixing dirty data with LIKE, BETWEEN, and LOCATE
* Managing multiples and other complex data problems with ROWID, SIMILAR, GROUP BY, and HAVING
* Handling the multiple display formats of dates
* Translating values to expand compressed codes with CASE, DECODE, point functions, UNION, and subqueries
* Manipulating numbers, including using auto-numbering mechanisms, finding the high value, locating every Nth, and more.
* Tuning queries for enhanced performance, focusing on indexes and the optimizer
* Comparing vendors' system catalogs and system functions
* Using SQL to generate SQL for cleanup and permission scripts as well as creating test data

A business-based working database serves as a running example throughout the book. It provides the context for numerous code samples, all of which are tested on multiple systems, including Adaptive Server Anywhere, Oracle, Informix, Microsoft SQL Server, and Sybase Adaptive Server Enterprise. The accompanying CD-ROM contains the full sample database as well as Adaptive Server Anywhere. 0201616386B04062001

Edition details

• Publisher: Addison-Wesley
• Publication date: 2000
• ISBN 10: 0201616386
• ISBN 13: 9780201616385
• Binding: Paperback
• Language: English
• Number of pages: 352