Overview

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 ForThis 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.ContentsYou 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. ChaptersHere 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.AppendicesThe 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.