Organizing Data
When representing complex data you have lots of choices to make. For any given problem there will be a variety of different schemas that you can use. The choices you make will impact three factors: performance, complexity, and redundancy. It would be nice to be able to optimize all three of these factors, but just as with "fast, cheap, and reliable," you will generally need to "pick any two."
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
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 147
Pages: 147
Authors: Andrew Cumming, Gordon Russell
