Practical SQL : The Sequel

Overview



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 ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (17) from $1.99   
  • New (3) from $34.36   
  • Used (14) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$34.36
Seller since 2010

Feedback rating:

(53)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
"New, ships through UPS and DHL. Excellent customer service. Satisfaction guaranteed!! "

Ships from: STERLING HEIGHTS, MI

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$46.68
Seller since 2014

Feedback rating:

(0)

Condition: New
Hardcover New in new dust jacket. Brand New US edition, 3-5 days shipping!

Ships from: foxboro, MA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$70.00
Seller since 2014

Feedback rating:

(146)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview



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
Read More Show Less

Editorial Reviews

From Barnes & Noble
The Barnes & Noble Review
Ever notice that real-world SQL isn't the same as the SQL in the introductory books? If you're trying to put your basic SQL knowledge to work solving real business problems, real-world SQL is what you need, and here's where to find it.

Practical SQL: The Sequel was carefully crafted to move you from classroom to reality. To begin with, it's organized around the tasks you're likely to perform, not abstract SQL features. For example, you'll learn about SQL functions and predicates in the context of a discussion of cleaning dirty data containing case problems, size problems, or embedded garbage. In a chapter on translating values, you'll start with simple DECODE and CASE techniques, and move on to characteristic functions, UNION, joins, outer joins, embedded subqueries, and conditional elements

The book's many examples have been tested on enterprise database platforms from Oracle, Microsoft, Sybase, and Informix. Where platform differences exist, as in the case of date conversions, they're duly explained -- making the book a handy resource for anyone switching platforms. All the examples run against the same database -- which is provided on the CD-ROM, along with all the files and scripts needed to recreate that database on each platform.

Whether you're trying to get past the limitations of your report writer, or support queries written just before your predecessor skipped town, Practical SQL: The Sequel is one SQL book that tells it like it is. (Bill Camarda)

Bill Camarda is a consultant and writer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

Booknews
Database specialist Bowman takes off where her 1996 leaves off, going beyond the basic query structure and simple examples to explore the complexities of using Structured Query Language to meet real-world business needs. She describes dealing with legacy systems, inherited problematic code, dirty data, and query tuning for better performance. She assumes readers to have a solid foundation in databases and the language. The accompanying disk contains the entire database used as an example throughout the text. Annotation c. Book News, Inc., Portland, OR (booknews.com)
Read More Show Less

Product Details

  • ISBN-13: 9780201616385
  • Publisher: Pearson Education
  • Publication date: 11/20/2000
  • Edition description: BK&CD-ROM
  • Pages: 336
  • Product dimensions: 7.37 (w) x 9.18 (h) x 0.95 (d)

Read an Excerpt

PREFACE:

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 isFor

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.

  1. "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.

  2. "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.

  3. 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.

  4. "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.

  5. "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.

  6. "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.


  7. "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.

  1. "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.
  2. "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.
  3. "Using Resources" includes books, Web sites, and newsgroups you might find interesting.
Read More Show Less

Table of Contents

List of Tables
List of Figures
Acknowledgments
Ch. 1 Introduction 1
Ch. 2 Handling Dirty Data 19
Ch. 3 Translating Values 63
Ch. 4 Managing Multiples 105
Ch. 5 Navigating Numbers 157
Ch. 6 Tuning Queries 203
Ch. 7 Using SQL to Write SQL 241
App. A Understanding the Sample DB: msdpn 267
App. B Comparing Datatypes and Functions 295
App. C: Using Resources 313
Index 317
Read More Show Less

Preface

PREFACE:

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 BookisFor

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.

  1. "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.

  2. "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.

  3. 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.

  4. "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.

  5. "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.

  6. "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.


  7. "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.

  1. "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.
  2. "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.
  3. "Using Resources" includes books, Web sites, and newsgroups you might find interesting.
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)