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.
Sprache
Verlagsort
Zielgruppe
Produkt-Hinweis
Broschur/Paperback
Klebebindung
Maße
Höhe: 228 mm
Breite: 154 mm
Dicke: 32 mm
Gewicht
ISBN-13
978-0-596-52799-0 (9780596527990)
Schweitzer Klassifikation
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.
Inhaltsverzeichnis
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
