SQL Hacks

Andrew Cumming is the Zoo Keeper at http://sqlzoo.net. He keeps a collection of tame SQL engines and presents these to the public. He cares for these beasts, protecting them from the abuse inflicted by SQL learners, and protecting SQL learners from the injuries that the wild engines can inflict. But mostly he shovels manure out the cages and hoses them down every now and then. Andrew also teaches at Napier University in Edinburgh, Scotland. Dr. Gordon Russell is a Computing lecturer in Napier University, Scotland. He teaches databases, Linux, and networking. He also builds educational websites, concentrating on technology-driven online learning environments. These include db.grussell.org, whose claim-to-fame is its automatic checking of SQL assessments, and linuxzoo.net, which offers online users root free access to Linux servers. But without database technology to support these sites, even he would be lost.

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