|
SQL Hacks: Tips & Tools for Digging Into Your Data
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.
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
|
