9780134578897

Effective SQL 61 Specific Ways to Write Better SQL

by ; ;
  • ISBN13:

    9780134578897

  • ISBN10:

    0134578899

  • Edition: 1st
  • Format: Paperback
  • Copyright: 12/23/2016
  • Publisher: Addison-Wesley Professional
  • Purchase Benefits
  • Free Shipping On Orders Over $59!
    Your order must be $59 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $44.99 Save up to $6.75
  • Buy New
    $38.24

    NOT YET PRINTED. PLACE AN ORDER AND WE WILL SHIP IT AS SOON AS IT ARRIVES.

Supplemental Materials

What is included with this book?

  • The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

Summary

“Given the authors’ reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.”
–Roger Carlson, Microsoft Access MVP (2006-2015)

“Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself a favor: Buy this book.”
—Dave Stokes, MySQL Community Manager, Oracle Corporation

Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It’s the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.

Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL’s basics, this guide will help you become a world-class SQL problem-solver.
  • Craft better logical data models, and fix flawed models
  • Implement indexes that improve query performance
  • Handle external data from sources you don’t control
  • Extract and aggregate the information you need, as efficiently as possible
  • Write more flexible subqueries
  • Analyze and retrieve metadata using your database platform of choice
  • Use Cartesian Products and Tally Tables to solve problems you can’t address with conventional JOINs
  • Model hierarchical data: managing SQL’s tradeoffs and shortcomings

Author Biography

John L. Viescas is an independent database consultant with more than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems. He spent six years at Applied Data Research in Dallas, Texas, where he directed a staff of more than 30 people and was responsible for research, product development, and customer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree in business finance at the University of Texas at Dallas, graduating cum laude.


John joined Tandem Computers, Inc., in 1988, where he was responsible for the development and implementation of database marketing programs in Tandem’s U.S. Western Sales region. He developed and delivered technical seminars on Tandem’s relational database management system, NonStop SQL. John wrote his first book, A Quick Reference Guide to SQL (Microsoft Press, 1989), as a research project to document the similarities in the syntax among the ANSI-86 SQL standard, IBM’s DB2, Microsoft’s SQL Server, Oracle Corporation’s Oracle, and Tandem’s NonStop SQL. He wrote the first edition of Running Microsoft® Access (Microsoft Press, 1992) while on sabbatical from Tandem. He has since written four editions of Running, three editions of Microsoft® Office Access Inside Out (Microsoft Press, 2003, 2007 and 2010–the successor to the Running series), and Building Microsoft® Access Applications (Microsoft Press, 2005). He is also the best-selling author of SQL Queries for Mere Mortals®, Third Edition (Addison-Wesley, 2014). John currently holds the record for the most consecutive years being awarded MVP from Microsoft, having received the award from 1993-2015. John makes his home with his wife of more than 30 years in Paris, France.

 

Douglas J. Steele has been working with computers, both mainframe and PC, for almost 45 years. (Yes, he did use punch cards in the beginning!) He worked for a large international oil company for more than 31 years before retiring in 2012. Databases and data modeling were a focus for most of that time, although he finished his career by developing the SCCM task sequence to roll Windows 7 out to over 100,000 computers worldwide.


Recognized by Microsoft as an MVP (Most Valuable Professional) for more than 16 years, Doug has authored numerous articles on Access, was co-author of Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs (Wiley Publishing, 2010), and has been technical editor for a number of books.


Doug holds a master’s degree in systems design engineering from the University of Waterloo (Ontario, Canada), where his research centered on designing user interfaces for non-traditional computer users. (Of course, this was in the late ’70s, so few people were traditional computer users at the time!) This research stemmed from his background in music (he holds an associateship in piano performance from the Royal Conservatory of Music, Toronto). He is also obsessed with beer, and is a graduate of the brewmaster and brewery operations management program at Niagara College (Niagara-on-the-Lake, Ontario).


Doug is married, the father of two daughters, and the grandfather of two granddaughters. He lives with his lovely wife of over 34 years in St. Catharines, Ontario. Doug can be reached at AccessMVPHelp@gmail.com.


Ben G. Clothier is a Solution Architect with IT Impact, Inc., a premier Access and SQL Server development shop based in Chicago, Illinois. He has worked as a freelance consultant with notable shops including J Street Technology and Advisicon, and has worked on Access projects from small, one-man solutions to company-wide line of business applications. Notable projects include job tracking and inventory for a cement company, a Medicare insurance plan generator for an insurance provider, and order management for an international shipping company. Ben is an administrator at UtterAccess and was a coauthor, with Teresa Hennig, George Hepworth and Doug Yudovich of Microsoft ® Access 2013 Programming (Wiley 2013), and with Tim Runcie and George Hepworth, of Microsoft ® Access in a SharePoint World (Advisicon, 2011), and a contributing author for Access 2010 Programmer’s Reference (Wiley, 2010). He holds certifications for Microsoft SQL Server 2012 Solution Associate and MySQL 5.0 Certified Developer, among others. He has been a Microsoft MVP since 2009.


Ben lives in San Antonio, Texas, with his wife, Suzanne, and his son, Harry.

 

Table of Contents

Part I: Database Design
Section 1: Data Model Design
1. Verify All Tables Have a Unique Identifier
2. Eliminate Redundant Storage of Data Items
3. Get Rid of Repeating Groups
4. Store Only One Attribute Per Field
5. Avoid Storing Calculated Data
6. Use Foreign Keys to Protect Referential Integrity
7. Make Sure the Table Relationships Make Sense
8. When 3nf Is Not Enough, Normalize More
9. Use Denormalization for Information Warehouses

 

Section 2: Programmability and Index Design
10. Factor in Nulls When Creating Indices
11. Verify That Your Indices Are in Fact Used By the Engine
12. Don't Let the Engine Scan Your Data
13. Use Indexes for More than Just Filtering
14. Don't Go Overboard with Triggers
15. Consider Using Filtered Index to Include or Exclude a Subset of Data
16. Understand Why You Should Use Declarative Constraints Instead of Programming Checks
17. Know Which ANSI SQL Your Product Is Using and How to Conform
18. Know When to Use Calculated Results in Indexes

 

Section 3: When You Can't Change the Design
19. Use Views to Hide Ugly Trolls
20. Use ETL to Provide a Data Model That Works for You
21. Create Summary Tables and Maintain Them
22. Use Union Statements to Un-Pivot Un-Normalized Tables

 

Part II: Query Topics
Section 1: Filtering and Finding Data
23. Find Non-Matches or Missing Records
24. Know When to Use Case to Solve a Problem
25. Know Techniques to Solve Multiple Criteria Problems
26. Divide Your Data If You Need a Perfect Match
27. For Date Comparisons, You Should Use >= and <= With (End + 1), Rather Than Using Between And
28. Write Sargable Query to Ensure that the Engine Will Use Indexes
29. Correctly Filter the Right Table of a Left Join

 

Section 2: Aggregation
30. Keep the Group By Clause Small
31. Find All Products That Performed Better Than a Given Reproduce
32. Find Maximum or Minimum Values Without Group By
33. Avoid Getting Erroneous Count of 1 When Using an Outer Join
34. Include 0 Rows When Testing for Having Count(X) < Some Number
35. Understand How Group By Works and What Needs to Go Into Group By
36. Use Distinct to Get Distinct Counts
37. Know How to Use Window Functions to Obtain Results That Depends on Adjacent Rows (E.G. Numbering, Ranking, Etc.)

Section 3: Subqueries
38. Know the Difference Between Correlated and Non-Correlated Subqueries
39. Know Different Places to Put a Subquery In
40. If Possible, Use Common Table Expressions Instead of Subqueries
41. Create More Efficient Queries Using Joins Rather Than Subqueries

 

Section 4: Analyzing and Getting Metadata
42. Learn to Use Your Systems Query Analyzer
43. Use Information Schema to Get Metadata about Your Database
44. Understand How Execution Plan Works

 

Part III: Solving Specific Problems
Section 1: Cartesian & Non-Equi Joins
45. Solve Problems Using Cartesian Products
46. List Customers and All Products and Flag the Products the Customers Has Already Purchased
47. List All Students Who Completed a Specific Course (or Category of Courses) and Calculated the Students Quintile Ranking Based on Grade
48. Create a League Schedule Where a Team Plays All Other Teams But Not Themselves
49. Produce a List of All Products in Stock with One Row Per Item in Stock

 

Section 2: Tally Table
50. Create an Appointment Calendar Table with All Dates Enumerated in a Range
51. Break Out and Sequence Sales into Individual Units of Product for Fifo or Lifo Processing
52. Display a Letter Grade Next to a Numerical Score for Students in All Courses
53. Pivot Sales by Month into Columns
54. Give a Table That Lists Customers First, Second and Third Preference for Certain Product Categories; List All Categories and Count of Number of Customers That Have Specified That Category as First, Second or Third
55. List All Bookings by Week
56. Print Mailing Labels but Skip the First N Already Used Labels on the First Page Based on a Parameter
57. Produce One 10% Off Coupon for Each $500 Worth of Products Ordered in the Last Quarter by Customer
58. Use a Date Table to Simplify Date Calculation

Section 3: Hierarchical Data
59. For Simple Hierarchy with Minimum Querying, Use Adjacency List Model
60. Use Nested Sets for Fast Querying Performance with Infrequent Updates
61. Use Materialized Path or Ancestry Traverse Closure for a Balance between Updates to Hierarchy and Good Querying Performance

Rewards Program

Write a Review