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.
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.
List of Tables.
List of Figures.
Acknowledgments.
1. Introduction.
In This Chapter.Why This Book?Use, Not Feature.Lots of
Examples.Multiple Systems.Legacy.Tuning.Who This Book Is
For.Contents.Chapters.Appendices.Speaking Multiple SQLs.SQL
Engines.SQL Dialects.Following Conventions.Text.Code.Understanding
the MSDPN Database.Table
Details.Customer.Supplier.Product.Ordermaster.Orderdetail.Employee.Using
the Examples.Summary.
2. Handling Dirty Data.
In This Chapter.Dirty Data.Case.Sort Order.UPPER and LOWER in
Searches.UPPER and LOWER in UPDATE.UPPER and LOWER with
Dates.INITCAP.Space.Removing Spaces.Additional Features.Size.Actual
Size.Defined Size.LIKE.Quoting Wildcards with ESCAPE.LIKE
Variants.Datatypes and LIKE.Position of Expression in
Expression.How LOCATE Works.Using LOCATE Functions.Sounds
Like.BETWEEN.Dealing with Dates.Converting Dates (and Other
Datatypes).Doing Math on Dates.Finding Dates.Summary.
3. Translating Values.
In This Chapter.Why Translate?CASE/DECODE.CASE.DECODE.CASE/DECODE
Variations.Translating NULL.COALESCE/ISNULL/NVL.Finding the First
Non-NULL: COALESCE.NULLIF.Point Functions.Getting CASE Effects from
Functions and Column Values.Getting CASE Effects from Multiple
Functions.UNION.How UNION Works.UNION and NULL.UNION Problems?Joins
and Outer Joins.Joins and NULL Values.Outer
Joins.Subqueries.Subqueries and Displays.Embedded Correlated
Subqueries.Correlated and Non-Correlated Subqueries.Another
Conditional Expression: TRANSLATE.Summary.
4. Managing Multiples.
In This Chapter.What's the Issue with
Multiples?Duplicates.Duplicates and a Holding Table.Using ROWID to
Remove Duplicates.Near Duplicates.Self Join.Unequal
Joins.SIMILAR.Locating Disconnected Rows.Using Outer Joins.Using
NOT IN Subqueries.Using MINUS.Counting Items.Grouping by a
Subset.Locating the Critical Element.Groups and Outer
Joins.Figuring Distribution.Checking Detail against Master.Two
Products Together?HAVING.Summary.
5. Navigating Numbers.
In This Chapter.Auto-Numbering.ASA: Default.Transact-SQL: Column
Property.Microsoft SQL Server: IDENTITY (start, increment).Sybase
Adaptive Server Enterprise: IDENTITY.Oracle: CREATE
SEQUENCE.Informix: SERIAL Datatype.Associated Issues.Finding the
High Value.GROUP BY, COUNT, HAVING MAX (COUNT).FROM Subquery.Row
Counts of Various Sorts.Row Numbers.System Numbers.You Make the
Numbers.Top N: Six Approaches.1: Row Limits and ORDER BY.2: Row
Numbers and HAVING.3: Subquery.4: Nested Subqueries.5: Aggregates &
Many Copies.6: Cursors.Finding Every Nth.What Modulo Is.Modulo in
WHERE and HAVING.Back to Nth Row.Running Total.Summary.
6. Tuning Queries.
In This Chapter.Perform, #%&#@!The Basic Problem.The Optimizer
and Tools for Understanding It.Getting Information on
Indexes.Checking the Optimizer.SQL Conventions.WHERE Clause.Why a
Table Scan?Data Distribution Statistics.Disabling an Index with a
Bad WHERE.Comparing Columns in the Same Table.Using Non-Selective
Indexes.Doing Math on a Column.Using Functions.Finding Ranges with
BETWEEN.Matching with LIKE.Comparing to NULL.Negating with
NOT.Converting Values.Using OR.Finding Sets of Values with IN.Using
Multi-Column Indexes.Covering Indexes.Joining
Columns.Sorting.DISTINCT.UNION.WHERE.HAVING and WHERE.Views.Forcing
Indexes.Summary.Asking Performance Questions.USING SQL TO WRITE
SQL.In This Chapter.Systematically Speaking.System Catalogs.Listing
System Catalogs.Writing Queries Using System Catalogs.System
Functions.Getting Administrative Information.Finding Today's
Date.Writing SQL with SQL.GRANTing Permissions.Removing Junk
Objects.Creating Test Data.Summary.
Appendix A. Understanding the Sample.
In This Appendix.MegaSysDataProNet Co.Collecting the CREATE
Scripts.Adaptive Server Anywhere.Sybase Adaptive Server
Enterprise.Microsoft SQL Server.Oracle.Informix.Explaining the
INSERT Scripts.INSERT INTO customer.INSERT INTO supplier.INSERT
INTO product.INSERT INTO employee.INSERT INTO ordermaster.INSERT
INTO orderdetail.Experimenting and Transaction Management.Removing
Data and Objects: DROP and DELETE Commands.Remove Database.DROP
Commands.DELETE FROM Command.Summary.
Appendix B. Comparing Datatypes and Functions.
In This Appendix.Comparatively Speaking.Datatype
Comparison.Function Comparison.Character (String) Functions.Number
Functions.Date Functions.Conditional Functions.Sequential Number
Methods.Row Number and Row ID Methods.Other Functions.Tuning
Functions.System Functions.Join Syntax Comparison.Notes on
Environment and Display.Summary.
Appendix C. Using Resources.
In This Appendix.Books.General.Informix.Microsoft SQL
Server.mSQL/MySQL.Oracle.Sybase.Transact-SQL.Web Sites.Database
Vendors.Other Offerings.Newsgroups.
Index. 0201616386T04062001
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.
Judith S. Bowman has been a database professional since
the early 1980s. She is currently an independent consultant
specializing in SQL and relational database issues and is the
author of Practical SQL: The Sequel (Addison-Wesley, 2001).
0201616386AB04062001
|
Ask a Question About this Product More... |
|
