Practical SQL
The Sequel
Published on 29. November 2000
Book
Mixed media product
352 pages
978-0-201-61638-5 (ISBN)
Description
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.
For every intermediate-to-advanced level database and business professional who wants to use SQL more effectively.
More details
Language
English
Place of publication
Boston
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Dimensions
Height: 233 mm
Width: 187 mm
Thickness: 21 mm
Weight
723 gr
ISBN-13
978-0-201-61638-5 (9780201616385)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Person
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
0201616386AB04062001
Content
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
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