SQL Hacks

book cover
SQL Hacks
By Andrew Cumming, Gordon Russell
Publisher: O'Reilly
Pub Date: November 01, 2006
ISBN-10: 0-596-52799-3
ISBN-13: 978-0-596-52799-0
Pages: 304

Table of Contents  | Index

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.

book cover
SQL Hacks
By Andrew Cumming, Gordon Russell
Publisher: O'Reilly
Pub Date: November 01, 2006
ISBN-10: 0-596-52799-3
ISBN-13: 978-0-596-52799-0
Pages: 304

Table of Contents  | Index

   credits Credits
    Chapter 1.  SQL Fundamentals
      Hack 1.  Run SQL from the Command Line
      Hack 2.  Connect to SQL from a Program
      Hack 3.  Perform Conditional INSERTs
      Hack 4.  UPDATE the Database
      Hack 5.  Solve a Crossword Puzzle Using SQL
      Hack 6.  Don't Perform the Same Calculation Over and Over
    Chapter 2.  Joins, Unions, and Views
      Hack 7.  Modify a Schema Without Breaking Existing Queries
      Hack 8.  Filter Rows and Columns
      Hack 9.  Filter on Indexed Columns
      Hack 10.  Convert Subqueries to JOINs
      Hack 11.  Convert Aggregate Subqueries to JOINs
      Hack 12.  Simplify Complicated Updates
      Hack 13.  Choose the Right Join Style for Your Relationships
      Hack 14.  Generate Combinations
    Chapter 3.  Text Handling
      Hack 15.  Search for Keywords Without LIKE
      Hack 16.  Search for a String Across Columns
      Hack 17.  Solve Anagrams
      Hack 18.  Sort Your Email
    Chapter 4.  Date Handling
      Hack 19.  Convert Strings to Dates
      Hack 20.  Uncover Trends in Your Data
      Hack 21.  Report on Any Date Criteria
      Hack 22.  Generate Quarterly Reports
      Hack 23.  Second Tuesday of the Month
    Chapter 5.  Number Crunching
      Hack 24.  Multiply Across a Result Set
      Hack 25.  Keep a Running Total
      Hack 26.  Include the Rows Your JOIN Forgot
      Hack 27.  Identify Overlapping Ranges
      Hack 28.  Avoid Dividing by Zero
      Hack 29.  Other Ways to COUNT
      Hack 30.  Calculate the Maximum of Two Fields
      Hack 31.  Disaggregate a COUNT
      Hack 32.  Cope with Rounding Errors
      Hack 33.  Get Values and Subtotals in One Shot
      Hack 34.  Calculate the Median
      Hack 35.  Tally Results into a Chart
      Hack 36.  Calculate the Distance Between GPS Locations
      Hack 37.  Reconcile Invoices and Remittances
      Hack 38.  Find Transposition Errors
      Hack 39.  Apply a Progressive Tax
      Hack 40.  Calculate Rank
    Chapter 6.  Online Applications
      Hack 41.  Copy Web Pages into a Table
      Hack 42.  Present Data Graphically Using SVG
      Hack 43.  Add Navigation Features to Web Applications
      Hack 44.  Tunnel into MySQL from Microsoft Access
      Hack 45.  Process Web Server Logs
      Hack 46.  Store Images in a Database
      Hack 47.  Exploit an SQL Injection Vulnerability
      Hack 48.  Prevent an SQL Injection Attack
    Chapter 7.  Organizing Data
      Hack 49.  Keep Track of Infrequently Changing Values
      Hack 50.  Combine Tables Containing Different Data
      Hack 51.  Display Rows As Columns
      Hack 52.  Display Columns As Rows
      Hack 53.  Clean Inconsistent Records
      Hack 54.  Denormalize Your Tables
      Hack 55.  Import Someone Else's Data
      Hack 56.  Play Matchmaker
      Hack 57.  Generate Unique Sequential Numbers
    Chapter 8.  Storing Small Amounts of Data
      Hack 58.  Store Parameters in the Database
      Hack 59.  Define Personalized Parameters
      Hack 60.  Create a List of Personalized Parameters
      Hack 61.  Set Security Based on Rows
      Hack 62.  Issue Queries Without Using a Table
      Hack 63.  Generate Rows Without Tables
    Chapter 9.  Locking and Performance
      Hack 64.  Determine Your Isolation Level
      Hack 65.  Use Pessimistic Locking
      Hack 66.  Use Optimistic Locking
      Hack 67.  Lock Implicitly Within Transactions
      Hack 68.  Cope with Unexpected Redo
      Hack 69.  Execute Functions in the Database
      Hack 70.  Combine Your Queries
      Hack 71.  Extract Lots of Rows
      Hack 72.  Extract a Subset of the Results
      Hack 73.  Mix File and Database Storage
      Hack 74.  Compare and Synchronize Tables
      Hack 75.  Minimize Bandwidth in One-to-Many Joins
      Hack 76.  Compress to Avoid LOBs
    Chapter 10.  Reporting
      Hack 77.  Fill in Missing Values in a Pivot Table
      Hack 78.  Break It Down by Range
      Hack 79.  Identify Updates Uniquely
      Hack 80.  Play Six Degrees of Kevin Bacon
      Hack 81.  Build Decision Tables
      Hack 82.  Generate Sequential or Missing Data
      Hack 83.  Find the Top n in Each Group
      Hack 84.  Store Comma-Delimited Lists in a Column
      Hack 85.  Traverse a Simple Tree
      Hack 86.  Set Up Queuing in the Database
      Hack 87.  Generate a Calendar
      Hack 88.  Test Two Values from a Subquery
      Hack 89.  Choose Any Three of Five
    Chapter 11.  Users and Administration
      Hack 90.  Implement Application-Level Accounts
      Hack 91.  Export and Import Table Definitions
      Hack 92.  Deploy Applications
      Hack 93.  Auto-Create Database Users
      Hack 94.  Create Users and Administrators
      Hack 95.  Issue Automatic Updates
      Hack 96.  Create an Audit Trail
    Chapter 12.  Wider Access
      Section 12.1.  Sharing Data Across the Internet
      Hack 97.  Allow an Anonymous Account
      Hack 98.  Find and Stop Long-Running Queries
      Hack 99.  Don't Run Out of Disk Space
      Hack 100.  Run SQL from a Web Page