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.
Book Description
Editorial Reviews
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
Product Details
Would you like to update product info or give feedback on images?
|
More About the Author
Discover books, learn about writers, read author blogs, and more.
Customer Reviews
|
Share your thoughts with other customers:
|
||||||||||||||||||||||
|
Most Helpful Customer Reviews
13 of 14 people found the following review helpful:
5.0 out of 5 stars
Practical SQL, Practical Examples,
By
This review is from: Practical SQL The Sequel (With CD-ROM) (Paperback)
This book takes a different and useful approach of focusing on a smaller number of queries but comparing different SQL dialects. Other books, such as "SQL Queries for Mere Mortals" and "Mastering SQL" cover SQL in more depth, but focus on the SQL standard, and don't try to show the variations found in different products.
13 of 14 people found the following review helpful:
5.0 out of 5 stars
Excellent companion to the series,
By
This review is from: Practical SQL The Sequel (With CD-ROM) (Paperback)
In this companion to Practical SQL: The Handbook, Practical SQL: The Sequel picks up where the handbook left off and explores tasks related to the daily life of a database. The author takes a hands-on approach to taking basic knowledge of SQL and extending it to solving real world problems. Like most programming languages, SQL is a language that is best learned by doing. Entering queries and seeing what happens will get your further along than textbooks full of relational theory. It's a bit like walking: if you were to think about all the bones in your leg and how they work, you would probably fall down. There are plenty of examples for you to try, which later become prototypes for your own queries.Working on a daily basis with web applications, the Practical SQL Handbook is always within reach and is the reference I turn to whenever I run into a SQL query that tests the limits of my knowledge, despite several years of SQL experience. Writing SQL can often be tricky. Many concepts are difficult to grasp even for the experienced, such as working with aggregates or joins and unions, which often have me returning to the handbook. I expect Practical SQL: The Sequel will become a well-thumbed companion to the handbook. This book covers several important issues often overlooked by general SQL tutorials or theory and not covered at all by vendor manuals. This book is aimed at the person who has learned basic SQL and is moving on to greater things. You will not find basic relational database theory in this companion to Practical SQL: The Handbook, but you will find a balanced guide to learning the practical techniques for solving the SQL puzzles that crop up in the real world outside the classroom. Most working database applications employ SQL that bears little resemblance to the relational concepts and idealized examples found in textbooks or classrooms. Yet at the other extreme, the new SQL user is often frustrated to discover existing references are little more than technical manuals or vendor brochures touting the latest gimmick. This text helps you make the transition from the idealized forms of the classroom to the practical solutions used in everyday database work. It should not be dismissed as a sort of "vocational" text only for those who get their hands dirty. This book offers solid advice for anyone working with relational database systems. A chapter is devoted to recognizing, finding, correcting and removing "dirty data." Such as duplicate information, or nearly duplicate information, such as you are probably familiar with junk mail that comes to your mailbox with several slightly different spellings of your name. In the real world you often inherit data that has been collected without concern for integrity checks. Much of the work a database is expected to do requires searching through data for items matching some condition. This is executed through the WHERE clause, and is the place to start looking for ways you can improve SQL performance merely by paying attention to how you write SQL. In her foray into query tuning, the author explains how to avoid unnecessary table scans provoked by a variety of queries where a subtle difference of usage enables or disables employment of speed enhancing indexes. Or when indexing a column has no practical result. Most database systems provide some method for automatically generating unique numbers as data is inserted. Unfortunately, there is no standard among vendors for how autonumbering is implemented. The author compares examples from the most popular enterprise systems and shows examples of usage. The concept of finding the top-N items in a result set is discussed, which is central to many web applications user interfaces. Useful for navigating the database in "browse n-items" displays, data hiding, "top ten" lists, finding the five best customers, the lowest selling books and etc. A chapter is devoted to translating values, an often overlooked subject. Translation is mapping the arcane codes used to represent values into something readable by human beings. In order to save space and make references clearer to computers, frequently numbers or letter-number codes are used to signify a particular value. Making theses codes readable by people is often a chore, one that requires an inordinate amount of the programmer's attention that might be better spent elsewhere. The text covers various methods of translation, ranging from automatic CASE/DECODE features to doing it yourself using "point functions," which make life easier for the database programmer. A chapter on using "system catalogs" (tables that describe the database itself) shows you how to obtain descriptions of tables, list what tables exist in the database and describe existing indexes are useful to understanding the database environment and orienting yourself within the database. I use these commands on a daily basis and it has become my habit to issue a "show tables" command on logging in just to jog my memory. I have to admit the "enterprise" orientation of the Practical SQL series can sometimes be distracting, expecting the reader will rarely be working directly with advanced query formulation or have access to system-level functions. This is atypical for the database programmer working on small business or non-profit websites where they have full access to the SQL server. Rarely will they generating simple reports and often are tasked with crafting complex queries by hand. Oddly, this make the companion edition more appropriate for the programmer, because they will often face dirty data or legacy code working on websites. Do not expect this book to teach you how to create database driven website applications. There are other books that delve into database programming. I trust the Practical SQL books for clear explanations of complicated SQL concepts in plain language tempered by common sense and practicality, not specific solutions.
2.0 out of 5 stars
Below aberage,
Amazon Verified Purchase(What's this?)
This review is from: Practical SQL The Sequel (With CD-ROM) (Paperback)
This book simply did not serve the purpose for whihc I purchased it. It was very shallow and not very well written. It did not serve the purpose...
Share your thoughts with other customers: Create your own review
|
|
What Other Items Do Customers Buy After Viewing This Item?
Tags Customers Associate with This Product(What's this?)Click on a tag to find related items, discussions, and people.
|
Sell a Digital Version of This Book in the Kindle Store
If you are a publisher or author and hold the digital rights to a book, you can sell a digital version of it in our Kindle Store.
Learn more
