SQL Hacks

ISBN: 0596527993
EAN: 2147483647

Year: 2004
Pages: 147

Authors: Andrew Cumming, Gordon Russell

BUY ON AMAZON

SQL Fundamentals

• 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. Dont Perform the Same Calculation Over and Over

Joins, Unions, and Views

• 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

Text Handling

• 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

Date Handling

• 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

Number Crunching

• 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

Online Applications

• 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

Organizing Data

• 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 Elses Data
• Hack 56. Play Matchmaker
• Hack 57. Generate Unique Sequential Numbers

Storing Small Amounts of Data

• 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

Locking and Performance

• 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

Reporting

• 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

Users and Administration

• 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

Wider Access

• Wider Access
• Sharing Data Across the Internet
• Hack 97. Allow an Anonymous Account
• Hack 98. Find and Stop Long-Running Queries
• Hack 99. Dont Run Out of Disk Space
• Hack 100. Run SQL from a Web Page

Index