Practical SQL: The Sequel with Cdrom
Judith S. Bowman · Addison-Wesley Longman Publishing Co., Inc. eBooks · 2000
From the Book: PREFACE: 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 database applications dont use basic SQL. After you read an introductory book or get some training, youre thrown into a world of complex code and told to maintain thisdont 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 systemsAdaptive Server Anywhere (on the CD), Oracle, Informix, Microsoft 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 BookisFor This book is for you, the user who understands the basics and wants to know more. Youve been using a GUI report writer, and youre trying to do things it cant; or youd 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 dialect youre using on the job is different from the one you learned in class, or you are with multiple systems. Youre supporting code some long gone employee wrote, which doesnt seem to work right and is full of stuff youve 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 dont need to read the book from start to endyou 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 youve already started your career. It explains the book approach, organization, and conventions, and lists the systems used. It provides a brief summary of the sample database. Handling Dirty Data explores functions and predicates, with suggestions on using them for finding and fixing dirty datadata 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 datesdoing 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). Heres where you learn about DECODE and CASE. Youll also find explanations of other methods of doing the same thingcharacteristic functions, UNION, joins and outer joins, and embedded subqueries. The chapter includes a summary of other conditional elements, including ISNULL, NVL, COALESCE, and TRANSLATE. 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. Youll 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, youll practice some important techniques, including GROUP BY, aggregates, self-joins, unequal joins, MINUS, HAVING, and outer joins. Youll 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 cant, urging the 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 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 to generate SQLa 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 commandsSQL 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 dialect variant charts merged into a big one for your convenience. Here youll 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.