SQL Hacks

Whether you're running Access, MySQL, SQL Server, Oracle, or PostgreSQL, this book will help you push the limits of traditional SQL to squeeze data effectively from your database. The book offers 100 hacks -- unique tips and tools -- that bring you the knowledge of experts who apply what they know in the real world to help you take full advantage of the expressive power of SQL. You'll find practical techniques to address complex data manipulation problems. Learn how to:

• Wrangle data in the most efficient way possible
• Aggregate and organize your data for meaningful and accurate reporting
• Make the most of subqueries, joins, and unions
• Stay on top of the performance of your queries and the server that runs them
• Avoid common SQL security pitfalls, including the dreaded SQL injection attack

Let SQL Hacks serve as your toolbox for digging up and manipulating data. If you love to tinker and optimize, SQL is the perfect technology and SQL Hacks is the must-have book for you.

 

Table of Contents

Credits  

Preface  

Chapter 1. SQL Fundamentals 

      1. Run SQL from the Command Line  

      2. Connect to SQL from a Program  

      3. Perform Conditional INSERTs  

      4. UPDATE the Database  

      5. Solve a Crossword Puzzle Using SQL  

      6. Don't Perform the Same Calculation Over and Over  

Chapter 2. Joins, Unions, and Views 

      7. Modify a Schema Without Breaking Existing Queries
      8. Filter Rows and Columns  

      9. Filter on Indexed Columns  

      10. Convert Subqueries to JOINs  

      11. Convert Aggregate Subqueries to JOINs  

      12. Simplify Complicated Updates  

      13. Choose the Right Join Style for Your Relationships  

      14. Generate Combinations  

Chapter 3. Text Handling 

      15. Search for Keywords Without LIKE  

      16. Search for a String Across Columns  

      17. Solve Anagrams  

      18. Sort Your Email  

Chapter 4. Date Handling 

      19. Convert Strings to Dates  

      20. Uncover Trends in Your Data  

      21. Report on Any Date Criteria  

      22. Generate Quarterly Reports  

      23. Second Tuesday of the Month  

Chapter 5. Number Crunching 

      24. Multiply Across a Result Set  

      25. Keep a Running Total  

      26. Include the Rows Your JOIN Forgot  

      27. Identify Overlapping Ranges  

      28. Avoid Dividing by Zero  

      29. Other Ways to COUNT  

      30. Calculate the Maximum of Two Fields  

      31. Disaggregate a COUNT  

      32. Cope with Rounding Errors  

      33. Get Values and Subtotals in One Shot  

      34. Calculate the Median  

      35. Tally Results into a Chart  

      36. Calculate the Distance Between GPS Locations  

      37. Reconcile Invoices and Remittances  

      38. Find Transposition Errors  

      39. Apply a Progressive Tax  

      40. Calculate Rank  

Chapter 6. Online Applications 

      41. Copy Web Pages into a Table  

      42. Present Data Graphically Using SVG  

      43. Add Navigation Features to Web Applications  

      44. Tunnel into MySQL from Microsoft Access  

      45. Process Web Server Logs  

      46. Store Images in a Database  

      47. Exploit an SQL Injection Vulnerability  

      48. Prevent an SQL Injection Attack  

Chapter 7. Organizing Data 

      49. Keep Track of Infrequently Changing Values  

      50. Combine Tables Containing Different Data  

      51. Display Rows As Columns  

      52. Display Columns As Rows  

      53. Clean Inconsistent Records  

      54. Denormalize Your Tables  

      55. Import Someone Else's Data  

      56. Play Matchmaker  

      57. Generate Unique Sequential Numbers  

Chapter 8. Storing Small Amounts of Data 

      58. Store Parameters in the Database  

      59. Define Personalized Parameters  

      60. Create a List of Personalized Parameters  

      61. Set Security Based on Rows  

      62. Issue Queries Without Using a Table  

      63. Generate Rows Without Tables  

Chapter 9. Locking and Performance 

      64. Determine Your Isolation Level  

      65. Use Pessimistic Locking  

      66. Use Optimistic Locking  

      67. Lock Implicitly Within Transactions  

      68. Cope with Unexpected Redo  

      69. Execute Functions in the Database  

      70. Combine Your Queries  

      71. Extract Lots of Rows  

      72. Extract a Subset of the Results  

      73. Mix File and Database Storage  

      74. Compare and Synchronize Tables  

      75. Minimize Bandwidth in One-to-Many Joins  

      76. Compress to Avoid LOBs  

Chapter 10. Reporting 

      77. Fill in Missing Values in a Pivot Table  

      78. Break It Down by Range  

      79. Identify Updates Uniquely  

      80. Play Six Degrees of Kevin Bacon  

      81. Build Decision Tables  

      82. Generate Sequential or Missing Data  

      83. Find the Top n in Each Group  

      84. Store Comma-Delimited Lists in a Column  

      85. Traverse a Simple Tree  

      86. Set Up Queuing in the Database  

      87. Generate a Calendar  

      88. Test Two Values from a Subquery  

      89. Choose Any Three of Five  

Chapter 11. Users and Administration 

      90. Implement Application-Level Accounts  

      91. Export and Import Table Definitions  

      92. Deploy Applications  

      93. Auto-Create Database Users  

      94. Create Users and Administrators  

      95. Issue Automatic Updates  

      96. Create an Audit Trail  

Chapter 12. Wider Access 

      97. Allow an Anonymous Account  

      98. Find and Stop Long-Running Queries  

      99. Don't Run Out of Disk Space  

      100. Run SQL from a Web Page  

Index