SQL Hacks

SQL Hacks

by Andrew Cumming, Gordon Russell
Released November 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596527990

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

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.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Credits
    1. About the Authors
    2. Contributors
    3. Acknowledgments
  3. Preface
    1. Why SQL Hacks?
    2. How to Use This Book
      1. SQL Conventions
    3. How This Book Is Organized
    4. Conventions
    5. Using Code Examples
    6. How to Contact Us
    7. Got a Hack?
    8. Safari Enabled
  4. 1. SQL Fundamentals
    1. Run SQL from the Command Line
      1. Pipe into SQL
      2. Switches
      3. Microsoft SQL Server
      4. Oracle
        1. Getting into Oracle
        2. List your tables in Oracle
        3. Import a file of SQL into Oracle
      5. MySQL
        1. Getting into MySQL
        2. List your tables in MySQL
        3. Import a file of SQL into MySQL
      6. Access
        1. Getting into SQL in Access
        2. Import a file of SQL commands
      7. PostgreSQL
        1. Getting into SQL in PostgreSQL
        2. List your tables in Postgres
        3. Import a file into PostgreSQL
      8. DB2
        1. Import SQL into DB2
    2. Connect to SQL from a Program
      1. C#
        1. Compiling C#
        2. Other C# considerations
      2. Java
        1. Running Java
      3. Perl
        1. Running Perl
      4. PHP
        1. Running PHP
      5. Ruby
        1. Running Ruby
    3. Perform Conditional INSERTs
      1. INSERT ... VALUES
      2. INSERT ... SELECT
    4. UPDATE the Database
      1. MySQL Differences
    5. Solve a Crossword Puzzle Using SQL
      1. SQL Server Variation
      2. Access Variation
      3. PostgreSQL Variation
      4. Filling a Table with Words
    6. Don’t Perform the Same Calculation Over and Over
      1. Use a VIEW
      2. Hacking the Hack
  5. 2. Joins, Unions, and Views
    1. Modify a Schema Without Breaking Existing Queries
      1. Copy the Database
      2. Alter the Table
      3. Create a View to Replace a Table
    2. Filter Rows and Columns
      1. Perl
      2. Java
      3. Ruby
      4. C#
      5. PHP
    3. Filter on Indexed Columns
    4. Convert Subqueries to JOINs
      1. Looking for What’s Not There
    5. Convert Aggregate Subqueries to JOINs
    6. Simplify Complicated Updates
      1. Using a Cursor
      2. Using a VIEW
    7. Choose the Right Join Style for Your Relationships
      1. A JOIN Chain
      2. A JOIN Star
    8. Generate Combinations
  6. 3. Text Handling
    1. Search for Keywords Without LIKE
      1. MySQL
      2. PostgreSQL
      3. SQL Server
      4. Oracle
    2. Search for a String Across Columns
    3. Solve Anagrams
      1. Choose a Hash Function
        1. A linear hash function
        2. A quadratic hash function
        3. An exponential hash function
    4. Sort Your Email
      1. Implementation-Specific Variations
        1. SQL Server: extract the domain name
        2. Oracle: extract the domain name
      2. Extract the Top-Level Domain
        1. MySQL: extract the top-level domain
        2. SQL Server: extract the top-level domain
        3. Oracle: extract the top-level domain
        4. PostgreSQL: extract the top-level domain
      3. Hacking the Hack
        1. SQL Server: calculated index
        2. Oracle: calculated index
        3. PostgreSQL: calculated index
  7. 4. Date Handling
    1. Convert Strings to Dates
      1. Convert Your Dates
      2. Parse Dates with Oracle
      3. Parse Dates with MySQL
      4. Parse Dates with SQL Server
    2. Uncover Trends in Your Data
      1. Modular Arithmetic
      2. SQL Server
      3. Access
      4. Oracle
    3. Report on Any Date Criteria
      1. Monthly Totals
      2. Current Month
        1. SQL Server
        2. Oracle
      3. Year-to-Date Totals
        1. Fiscal year to date
    4. Generate Quarterly Reports
      1. Hacking the Hack
    5. Second Tuesday of the Month
      1. Day-of-Week Function
      2. The Formula
        1. MySQL
        2. Oracle
        3. SQL Server
        4. PostgreSQL
      3. Hacking the Hack: The Last Thursday of the Month
      4. See Also
  8. 5. Number Crunching
    1. Multiply Across a Result Set
    2. Keep a Running Total
      1. Hacking the Hack
        1. SQL Server
        2. MySQL
        3. Oracle
      2. See Also
    3. Include the Rows Your JOIN Forgot
    4. Identify Overlapping Ranges
      1. Hacking the Hack
    5. Avoid Dividing by Zero
    6. Other Ways to COUNT
      1. Counting with a Condition
        1. Access
    7. Calculate the Maximum of Two Fields
      1. Minimum of Two Values
      2. Alternative Functions
      3. Hacking the Hack
    8. Disaggregate a COUNT
    9. Cope with Rounding Errors
    10. Get Values and Subtotals in One Shot
      1. ROLLUP and GROUPING SETS
        1. SQL Server, MySQL, and DB2
        2. Oracle and DB2
      2. Hacking the Hack
    11. Calculate the Median
      1. Create a Temporary Table
      2. Fill the Temporary Table
        1. MySQL
        2. SQL Server
        3. Oracle
      3. Find the Middle Row or Rows
    12. Tally Results into a Chart
    13. Calculate the Distance Between GPS Locations
      1. Hacking the Hack
    14. Reconcile Invoices and Remittances
      1. Find the Exact Matches
      2. Invoice Numbers Do Not Match
      3. Find Possible Matches
    15. Find Transposition Errors
      1. SQL Server
      2. Oracle
      3. Single Query
    16. Apply a Progressive Tax
      1. Working Without LEAST
    17. Calculate Rank
  9. 6. Online Applications
    1. Copy Web Pages into a Table
      1. XSLT Processing
      2. The Input Document
      3. gross.xsl
      4. Running the Hack
        1. Processing required
      5. Hacking the Hack
    2. Present Data Graphically Using SVG
      1. Vendor-Specific XML Features
        1. SQL Server
        2. Oracle
        3. MySQL
    3. Add Navigation Features to Web Applications
      1. SQL Server and PostgreSQL
      2. Oracle
      3. Access
      4. Running the Hack
      5. Hacking the Hack
    4. Tunnel into MySQL from Microsoft Access
      1. Create a Secure Tunnel
      2. Obtain the MySQL ODBC Connector
      3. Start the Tunnel Using Visual Basic
      4. Stop the Tunnel
      5. Test the Connection
      6. Connecting to Other Databases
    5. Process Web Server Logs
      1. Queries
        1. Check for broken links
        2. Investigate users’ actions
      2. Hacking the Hack
    6. Store Images in a Database
      1. Use a BLOB
      2. Use a File for Your Image
    7. Exploit an SQL Injection Vulnerability
      1. Fix the Bug
      2. Exploit the Bug
      3. Asking Yes/No Questions
        1. Some informative questions and their injection phrase
      4. Asking for Strings
      5. Getting the Metadata
      6. Summary
      7. See Also
    8. Prevent an SQL Injection Attack
      1. Escaping in Perl
      2. Escaping in C#
      3. Escaping in PHP
      4. Escaping in Java
      5. Nonstring Data
      6. JavaScript Validation, Cookies, and Hidden Variables
      7. Exploits Using Hidden Variables and Cookies
      8. Restrict the Rights of the SQL Account
      9. Don’t Overreact
      10. See Also
  10. 7. Organizing Data
    1. Keep Track of Infrequently Changing Values
      1. Record Price Changes
        1. Find the current price
        2. Find the price at a specified date
        3. List all prices at a specific date
    2. Combine Tables Containing Different Data
      1. Oracle and PostgreSQL
      2. MySQL
      3. SQL Server and Access
    3. Display Rows As Columns
      1. Using a Self-Join
      2. Using the CASE Statement
    4. Display Columns As Rows
      1. Ungroup Data with Repeating Columns
    5. Clean Inconsistent Records
      1. Normalize the Data
    6. Denormalize Your Tables
    7. Import Someone Else’s Data
    8. Play Matchmaker
    9. Generate Unique Sequential Numbers
      1. Mind the Gap
        1. Raw speed
      2. Multiuser Considerations
      3. Use System-Generated Numbers
        1. MySQL: AUTO_INCREMENT column
        2. SQL Server: IDENTITY column
        3. Oracle: SEQUENCE
        4. PostgreSQL: SEQUENCE
      4. Choosing a Primary Key
      5. Hacking the Hack
  11. 8. Storing Small Amounts of Data
    1. Store Parameters in the Database
      1. Running the Hack
    2. Define Personalized Parameters
      1. Adding New Users
      2. Platform-Specific Variations
    3. Create a List of Personalized Parameters
    4. Set Security Based on Rows
      1. Make Use of Usernames
      2. Hacking the Hack
        1. One-way trap
        2. Supervisor mode
    5. Issue Queries Without Using a Table
      1. Some Useful Static Functions
        1. MySQL
        2. SQL Server
        3. Oracle
        4. PostgreSQL
    6. Generate Rows Without Tables
      1. Hacking the Hack
        1. Combo box from a table in Access
        2. Pop-up list from a table in Perl
  12. 9. Locking and Performance
    1. Determine Your Isolation Level
      1. Autocommit
      2. Concurrency Issues
        1. Phantom reads
        2. Nonrepeatable reads
        3. Dirty reads
      3. Isolation Level
      4. Enforcing Isolation
      5. Querying the Isolation Level
    2. Use Pessimistic Locking
    3. Use Optimistic Locking
    4. Lock Implicitly Within Transactions
    5. Cope with Unexpected Redo
      1. Shopping Baskets
    6. Execute Functions in the Database
    7. Combine Your Queries
    8. Extract Lots of Rows
      1. Use a Big Buffer
      2. Use Variable Binding
      3. Make a Series of Round Trips
    9. Extract a Subset of the Results
      1. Hacking the Hack
    10. Mix File and Database Storage
      1. Adding and Removing Files
      2. Too Many Files
      3. Hacking the Hack
    11. Compare and Synchronize Tables
      1. Hacking the Hack
    12. Minimize Bandwidth in One-to-Many Joins
      1. Retrieve an Article and Its Comments
      2. The UNION Query
      3. Results
      4. Does This Always Work?
    13. Compress to Avoid LOBs
  13. 10. Reporting
    1. Fill in Missing Values in a Pivot Table
      1. Include Missing Values
      2. Use a Union
    2. Break It Down by Range
      1. Reduce the Precision of a Number
      2. Reduce the Precision of a Date
        1. MySQL
        2. Oracle
        3. SQL Server
        4. PostgreSQL
      3. Hacking the Hack
    3. Identify Updates Uniquely
      1. Cope with Duplicate Batches
      2. Create Transactions at the Branch
      3. Update at the Central Server
    4. Play Six Degrees of Kevin Bacon
    5. Build Decision Tables
      1. Hacking the Hack
    6. Generate Sequential or Missing Data
      1. Create and Populate an integers Table
      2. Generate Sequential Data
        1. Numbers 0–99
        2. Letters A–Z
        3. SQL Server
        4. Oracle
        5. Date ranges
        6. Oracle and PostgreSQL
        7. SQL Server
      3. Provide Missing Data in an OUTER JOIN
        1. Counts for each letter
        2. Data for consecutive dates
      4. Hacking the Hack
        1. Oracle
        2. PostgreSQL
        3. SQL Server
    7. Find the Top n in Each Group
      1. Last Three Articles
      2. Top n Rows in Each Group
      3. Ties
      4. See Also
    8. Store Comma-Delimited Lists in a Column
      1. Advantages of Comma-Delimited Lists
      2. Disadvantages of Comma-Delimited Lists
      3. Joining on a Comma-Delimited List
      4. See Also
    9. Traverse a Simple Tree
      1. Tree Visualization
      2. Oracle Recursive Extensions
    10. Set Up Queuing in the Database
    11. Generate a Calendar
      1. Variations
        1. SQL Server
        2. Oracle
        3. PostgreSQL
    12. Test Two Values from a Subquery
    13. Choose Any Three of Five
      1. A JOIN Solution
      2. A GROUP BY Solution
  14. 11. Users and Administration
    1. Implement Application-Level Accounts
      1. Storing User-Specific Information
      2. Hash Your Passwords
      3. Oracle
      4. SQL Server
      5. In the Programming Language
        1. Perl
        2. PHP
      6. See Also
    2. Export and Import Table Definitions
      1. MySQL
      2. PostgreSQL
      3. SQL Server
        1. Dates in SQL Server
      4. Oracle
      5. Access
      6. Potential Showstoppers
        1. Auto-numbers
        2. Spaces in table names and column names
        3. Nonstandard functions
    3. Deploy Applications
      1. Namespace Management
      2. Keep Your CREATE Script
        1. SQL Server
        2. MySQL
        3. Oracle
        4. PostgreSQL
      3. Portability
      4. DROP Avoiding Constraints
    4. Auto-Create Database Users
    5. Create Users and Administrators
      1. MySQL
      2. Oracle
      3. PostgreSQL
      4. SQL Server
    6. Issue Automatic Updates
      1. Oracle
      2. MySQL
      3. SQL Server
      4. PostgreSQL
    7. Create an Audit Trail
      1. SQL Server
      2. MySQL
      3. Oracle
      4. PostgreSQL
      5. Locking Down the Underlying Tables
      6. Processing the History Table
  15. 12. Wider Access
    1. Sharing Data Across the Internet
      1. Allowing Anonymous SQL Accounts
    2. Allow an Anonymous Account
      1. A Limited MySQL Account
      2. A Limited SQL Server Account
      3. A Limited PostgreSQL Account
      4. A Limited Oracle Account
    3. Find and Stop Long-Running Queries
      1. Oracle
      2. SQL Server
      3. MySQL
      4. PostgreSQL
      5. Hacking the Hack
        1. Oracle
        2. MySQL
        3. SQL Server
        4. PostgreSQL
    4. Don’t Run Out of Disk Space
      1. Oracle: Out of Temp Space
      2. Large Data Tables
        1. Oracle
        2. MySQL
        3. SQL Server
    5. Run SQL from a Web Page
      1. MySQL
      2. SQL Server
      3. Oracle
      4. PostgreSQL
      5. Hacking the Hack
      6. Using Other Web Interfaces
        1. Authentication
        2. CGI parameters
        3. Processing results
      7. Security
  16. Index
  17. About the Authors
  18. Colophon
  19. Copyright

Product information

  • Title: SQL Hacks
  • Author(s): Andrew Cumming, Gordon Russell
  • Release date: November 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596527990