SQL Hacks · Tips & Tools for Digging Into Your Data by Cumming, Andrew -- Read -- Imperial Library of Trantor

Index

SQL Hacks A Note Regarding Supplemental Files Credits

About the Authors Contributors Acknowledgments

Preface

Why SQL Hacks? How to Use This Book

SQL Conventions

How This Book Is Organized Conventions Using Code Examples How to Contact Us Got a Hack? Safari Enabled

1. SQL Fundamentals

Run SQL from the Command Line

Pipe into SQL Switches Microsoft SQL Server Oracle

Getting into Oracle List your tables in Oracle Import a file of SQL into Oracle

MySQL

Getting into MySQL List your tables in MySQL Import a file of SQL into MySQL

Access

Getting into SQL in Access Import a file of SQL commands

PostgreSQL

Getting into SQL in PostgreSQL List your tables in Postgres Import a file into PostgreSQL

DB2

Import SQL into DB2

Connect to SQL from a Program

Compiling C# Other C# considerations

Java

Running Java

Perl

Running Perl

PHP

Running PHP

Ruby

Running Ruby

Perform Conditional INSERTs

INSERT ... VALUES INSERT ... SELECT

UPDATE the Database

MySQL Differences

Solve a Crossword Puzzle Using SQL

SQL Server Variation Access Variation PostgreSQL Variation Filling a Table with Words

Don’t Perform the Same Calculation Over and Over

Use a VIEW Hacking the Hack

2. Joins, Unions, and Views

Modify a Schema Without Breaking Existing Queries

Copy the Database Alter the Table Create a View to Replace a Table

Filter Rows and Columns

Perl Java Ruby C# PHP

Filter on Indexed Columns Convert Subqueries to JOINs

Looking for What’s Not There

Convert Aggregate Subqueries to JOINs Simplify Complicated Updates

Using a Cursor Using a VIEW

Choose the Right Join Style for Your Relationships

A JOIN Chain A JOIN Star

Generate Combinations

3. Text Handling

Search for Keywords Without LIKE

MySQL PostgreSQL SQL Server Oracle

Search for a String Across Columns Solve Anagrams

Choose a Hash Function

A linear hash function A quadratic hash function An exponential hash function

Sort Your Email

Implementation-Specific Variations

SQL Server: extract the domain name Oracle: extract the domain name

Extract the Top-Level Domain

MySQL: extract the top-level domain SQL Server: extract the top-level domain Oracle: extract the top-level domain PostgreSQL: extract the top-level domain

Hacking the Hack

SQL Server: calculated index Oracle: calculated index PostgreSQL: calculated index

4. Date Handling

Convert Strings to Dates

Convert Your Dates Parse Dates with Oracle Parse Dates with MySQL Parse Dates with SQL Server

Uncover Trends in Your Data

Modular Arithmetic SQL Server Access Oracle

Report on Any Date Criteria

Monthly Totals Current Month

SQL Server Oracle

Year-to-Date Totals

Fiscal year to date

Generate Quarterly Reports

Hacking the Hack

Second Tuesday of the Month

Day-of-Week Function The Formula

MySQL Oracle SQL Server PostgreSQL

Hacking the Hack: The Last Thursday of the Month See Also

5. Number Crunching

Multiply Across a Result Set Keep a Running Total

Hacking the Hack

SQL Server MySQL Oracle

See Also

Include the Rows Your JOIN Forgot Identify Overlapping Ranges

Hacking the Hack

Avoid Dividing by Zero Other Ways to COUNT

Counting with a Condition

Access

Calculate the Maximum of Two Fields

Minimum of Two Values Alternative Functions Hacking the Hack

Disaggregate a COUNT Cope with Rounding Errors Get Values and Subtotals in One Shot

ROLLUP and GROUPING SETS

SQL Server, MySQL, and DB2 Oracle and DB2

Hacking the Hack

Calculate the Median

Create a Temporary Table Fill the Temporary Table

MySQL SQL Server Oracle

Find the Middle Row or Rows

Tally Results into a Chart Calculate the Distance Between GPS Locations

Hacking the Hack

Reconcile Invoices and Remittances

Find the Exact Matches Invoice Numbers Do Not Match Find Possible Matches

Find Transposition Errors

SQL Server Oracle Single Query

Apply a Progressive Tax

Working Without LEAST

Calculate Rank

6. Online Applications

Copy Web Pages into a Table

XSLT Processing The Input Document gross.xsl Running the Hack

Processing required

Hacking the Hack

Present Data Graphically Using SVG

Vendor-Specific XML Features

SQL Server Oracle MySQL

Add Navigation Features to Web Applications

SQL Server and PostgreSQL Oracle Access Running the Hack Hacking the Hack

Tunnel into MySQL from Microsoft Access

Create a Secure Tunnel Obtain the MySQL ODBC Connector Start the Tunnel Using Visual Basic Stop the Tunnel Test the Connection Connecting to Other Databases

Process Web Server Logs

Queries

Check for broken links Investigate users’ actions

Hacking the Hack

Store Images in a Database

Use a BLOB Use a File for Your Image

Exploit an SQL Injection Vulnerability

Fix the Bug Exploit the Bug Asking Yes/No Questions

Some informative questions and their injection phrase

Asking for Strings Getting the Metadata Summary See Also

Prevent an SQL Injection Attack

Escaping in Perl Escaping in C# Escaping in PHP Escaping in Java Nonstring Data JavaScript Validation, Cookies, and Hidden Variables Exploits Using Hidden Variables and Cookies Restrict the Rights of the SQL Account Don’t Overreact See Also

7. Organizing Data

Keep Track of Infrequently Changing Values

Record Price Changes

Find the current price Find the price at a specified date List all prices at a specific date

Combine Tables Containing Different Data

Oracle and PostgreSQL MySQL SQL Server and Access

Display Rows As Columns

Using a Self-Join Using the CASE Statement

Display Columns As Rows

Ungroup Data with Repeating Columns

Clean Inconsistent Records

Normalize the Data

Denormalize Your Tables Import Someone Else’s Data Play Matchmaker Generate Unique Sequential Numbers

Mind the Gap

Raw speed

Multiuser Considerations Use System-Generated Numbers

MySQL: AUTO_INCREMENT column SQL Server: IDENTITY column Oracle: SEQUENCE PostgreSQL: SEQUENCE

Choosing a Primary Key Hacking the Hack

8. Storing Small Amounts of Data

Store Parameters in the Database

Running the Hack

Define Personalized Parameters

Adding New Users Platform-Specific Variations

Create a List of Personalized Parameters Set Security Based on Rows

Make Use of Usernames Hacking the Hack

One-way trap Supervisor mode

Issue Queries Without Using a Table

Some Useful Static Functions

MySQL SQL Server Oracle PostgreSQL

Generate Rows Without Tables

Hacking the Hack

Combo box from a table in Access Pop-up list from a table in Perl

9. Locking and Performance

Determine Your Isolation Level

Autocommit Concurrency Issues

Phantom reads Nonrepeatable reads Dirty reads

Isolation Level Enforcing Isolation Querying the Isolation Level

Use Pessimistic Locking Use Optimistic Locking Lock Implicitly Within Transactions Cope with Unexpected Redo

Shopping Baskets

Execute Functions in the Database Combine Your Queries Extract Lots of Rows

Use a Big Buffer Use Variable Binding Make a Series of Round Trips

Extract a Subset of the Results

Hacking the Hack

Mix File and Database Storage

Adding and Removing Files Too Many Files Hacking the Hack

Compare and Synchronize Tables

Hacking the Hack

Minimize Bandwidth in One-to-Many Joins

Retrieve an Article and Its Comments The UNION Query Results Does This Always Work?

Compress to Avoid LOBs

10. Reporting

Fill in Missing Values in a Pivot Table

Include Missing Values Use a Union

Break It Down by Range

Reduce the Precision of a Number Reduce the Precision of a Date

MySQL Oracle SQL Server PostgreSQL

Hacking the Hack

Identify Updates Uniquely

Cope with Duplicate Batches Create Transactions at the Branch Update at the Central Server

Play Six Degrees of Kevin Bacon Build Decision Tables

Hacking the Hack

Generate Sequential or Missing Data

Create and Populate an integers Table Generate Sequential Data

Numbers 0–99 Letters A–Z SQL Server Oracle Date ranges Oracle and PostgreSQL SQL Server

Provide Missing Data in an OUTER JOIN

Counts for each letter Data for consecutive dates

Hacking the Hack

Oracle PostgreSQL SQL Server

Find the Top n in Each Group

Last Three Articles Top n Rows in Each Group Ties See Also

Store Comma-Delimited Lists in a Column

Advantages of Comma-Delimited Lists Disadvantages of Comma-Delimited Lists Joining on a Comma-Delimited List See Also

Traverse a Simple Tree

Tree Visualization Oracle Recursive Extensions

Set Up Queuing in the Database Generate a Calendar

Variations

SQL Server Oracle PostgreSQL

Test Two Values from a Subquery Choose Any Three of Five

A JOIN Solution A GROUP BY Solution

11. Users and Administration

Implement Application-Level Accounts

Storing User-Specific Information Hash Your Passwords Oracle SQL Server In the Programming Language

Perl PHP