Friday, March 5, 2010

SQL Hacks

SQL Hacks Review



This book is a collection of 100 different hacks, ranging from the simple to the complex. Each hack involves a specific problem that you may have already seen before, but perhaps tackled in a way you wouldn't have considered. Where it is impossible to phrase a statement that is acceptable to all of MySQL, SQL Server, Oracle, and PostgreSQL, a form is used that is acceptable to at least two of the four. As MySQL is a relative newcomer, its designers have been able to build in compatibility with many of its competitors. For that reason, MySQL is usually one of the two systems that will accept the statement unchanged. That explains why most of the examples use the MySQL command-line utility. The MySQL examples are based around the version 5.0 release. However, many examples will work with the 4.2 release. Note that some hacks involve features such as referential integrity and transaction isolation, and these are implemented only for InnoDB tables and not for MyISAM tables.

The examples also work for Microsoft SQL Server 2005. The SQL Server 2000 version is good enough for all but those examples that use the RANK( ) function. PostgreSQL and Oracle users should have no problem using this book, and most of the hacks will run unchanged on both systems. Oracle has many so additional features that optimizations are not mentioned. There is also plenty here for Access users, but none of the required variations are mentioned for that database. The following is the table of contents:

Chapter 1, SQL Fundamentals - This is a gentle introduction to running SQL from the command line and programs. It also touches on simple SQL constructs. Even if you are already comfortable with SQL, you may find the flexibility of the SQL shown to be surprising and instructive.
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. Don't Perform the Same Calculation Over and Over

Chapter 2, Joins, Unions, and Views - The hacks in this chapter concentrate on ways to use more than one table in your SQL. Different strategies are examined and discussed. If you find yourself using subqueries more than JOIN, you may also find the methods for converting subqueries to JOINs helpful.
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

Chapter 3, Text Handling - This chapter contains a number of hacks focused on efficient and effective text querying.
Hack 15. Search for Keywords Without LIKE
Hack 16. Search for a String Across Columns
Hack 17. Solve Anagrams
Hack 18. Sort Your Email

Chapter 4, Date Handling - Suppose you want to calculate the second Tuesday of each month, or look for trends based on the day of the week. Both calculations are discussed, as well as other hacks involving date processing and report generation techniques.
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

Chapter 5, Number Crunching - This chapter contains a host of hacks for handling numbers, from report generation to complex spatial calculations. This was my favorite chapter.
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

Chapter 6, Online Applications - Databases can help drive web sites, be directly controlled from a browser, and help close the gap between client and data. This chapter looks at a variety of hacks for using database systems in web-based activities.
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

Chapter 7, Organizing Data - Hacks in this chapter are concerned with how data can be represented in a database, how data can be managed as it is stored and retrieved, and how errors in information can be detected, managed, and repaired.
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 Else's Data
Hack 56. Play Matchmaker
Hack 57. Generate Unique Sequential Numbers

Chapter 8, Storing Small Amounts of Data - It is useful to parameterize queries using variables, treating the queries as functions and plugging in variables as needed. You can use standard SQL to provide parameterization, including variable scoping on a per-user or per-application basis. This chapter also looks at queries without tables, and support for inline tables.
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

Chapter 9, Locking and Performance - This chapter examines some common issues, and presents a number of hacks on isolation levels, locking, query partitioning, and result set management, all aimed at improving query performance and minimizing delays.
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

Chapter 10, Reporting - SQL queries for report generation require a different approach than those used for real-time querying. This can allow you to trade query performance for readability and maintainability. This chapter includes a number of hacks for summarizing, processing, and analyzing report data.
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

Chapter 11, Users and Administration - This chapter presents a few useful hacks on user management, both in your applications and in your database systems. It also looks at the common pitfalls in packaging a database-enabled application for easy installation.
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

Chapter 12, Wider Access - This chapter look at how to manage a diverse range of users who have SQL-level access to your databases.
Hack 97. Allow an Anonymous Account
Hack 98. Find and Stop Long-Running Queries
Hack 99. Don't Run Out of Disk Space
Hack 100. Run SQL from a Web Page

It is assumed that the reader is already familiar with database theory and their own particular flavor of SQL and just needs some "recipes" to get their job done. If you fall into this category, this book will make a fine addition to your reference books.


SQL Hacks Feature



SQL Hacks Overview


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.


SQL Hacks Specifications



Available at Amazon Check Price Now!






*** Product Information and Prices Stored: Mar 05, 2010 03:45:05

Related : customer process affordable healthcare wine country gift baskets cheap notebooks high speed internet providers

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)