SQL Hacks
Be the First to Write a Review and tell the world about this title!People who purchase this book frequently purchase: - SQL Cookbook; Anthony Molinaro, $23.95, 40% Off!
- Head First Object-Oriented Analysis and Design; Brett McLaughlin, et al, $29.95, 40% Off!
- Mastering Regular Expressions, 3rd Edition; Jeffrey Friedl, $26.95, 40% Off!
- CSS: The Definitive Guide, 3rd Edition; Eric Meyer, $26.95, 40% Off!
Books on similar topics, in best-seller order:Books from the same publisher, in best-seller order:
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.
Media Reviews
"SQL Hacks skips most of the tutelage and shows you very specific ways
for doing specific chores, with more explanation of how to adapt it than theory
behind it...aside from one 'sort-of', the authors nailed my entire wish list.
That's amazing — I've never had that happen before, actually. "
-- Scott Walters, Slashdot.org
"The authors did their homework, and SQL Hack's strengths are the depth,
detail, and level of knowledge with which each database system is covered, and
the book's willingness to get down and gritty. There's never an impression that
juicy details were omitted because the authors didn't want to expend the effort
to pick a colleague's brain or hunt down a factoid that never got documented
elsewhere...The polish is top notch. Writing a book is a huge undertaking, and
the economics of book publishing gives publishers little margin for advances.
A book that reads like its third release but is actually in its first can only
be the product of an exceptional level of dedication by the authors. "
-- Scrottie, Use Perl;
"SH (SQL Hacks) is simultaneously a programming romp and deadly serious
to the people who think about a million customers or sales or measurements at
a time. Cumming and Russell meet the challenge of balancing these two aspects
— the exuberant and pragmatic — well...you could even use SH as
a tutorial, especially if, like most database programmers, you do more reading
than writing. SH is not a reference, but it's methodical enough that an alert
and experienced programmer with no prior knowledge of SQL could probably learn
all of the language he needs for most purposes. They even tackle the preliminaries
of auditing, deployment, and administration, topics some programming introductions
leave out. "
-- Cameron Laird, UnixReview.com
Table of Contents
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
About the Authors
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.
|
