Customer comments on this selection.
 Must have for any body concerned with SQL...
Provide tips on overcoming most common issues in SQL.
 Fairly lousy book. I expected better from O'Reilly
Two problems:
br /
br /1. Huge quantity of typos and writing and technical errors.
br /
br /2. It's definitely not "hacking" anything. At best (if it were corrected and proofread) it would be a typical cookbook: a number of loosely connected fragments of code for all kinds of potential scenarios. That's not bad, btw! But hacking it isn't. The word 'hacking' brings to mind an image of a Mad Genius as it were, but this book has absolutely nothing at this level. It's pretty much hum-drum SQL programming -- and I can see how it could be quite useful btw -- provided it didn't have so many egregious errors and typos. Snafus start right from the beginnning, and are at times mind boggling.
br /
br /The book does have a lot of stuff, most of which is useful -- or rather it would be if you weren't afraid it's got errors in it. You can try of course, you can proofread this book yourself: and, paradoxically, this will be good learning. But personally, when it comes to technical books, I prefer to be able to trust that they're competent and well done, rather than play an amateur editor and try to fix it as I read it.
br /
br /My recommendation: there's tons of similar stuff, so begin by looking for something else; if still interested, get the book into your hands and read a bit. See if you like it, see if you see errors, see if they bother you. In other words, do not buy this book sight inseen -- chances are high you will be disappointed. I regret that I didn't send this book back; YMMV of course.
br /
br /Good luck.
br /
br /----------
br /
br /Examples:
br /
br /1. Hack 11, p. 37. The goal is, I quote, "to show the date on which each customer purchased the most _totalitems_" [a tableful of data shown with columns "customer", "whn" [when, that is], and "totalitems"]. Here's the solution:
br /
br /SELECT customer, whn, totalitems
br /FROM orders o1
br /WHERE o1.whn = (
br /SELECT MAX(whn)
br /FROM order o2
br /WHERE o1.customer = o2.customer
br /);
br /
br /Why are we MAX'in on date? The result shows -- no surprises -- the latest date in the table, even though more items were sold on another date.
br /
br /2. Hack 12, p 38. Here we adjust employee salaries based on their disciplinary record. None of this is important; what we do is produce a new salary which is the old one multiplied by something.
br /
br /The solution is as follows: first, let's create a view with a calculated field with the new salary so we can look at it:
br /
br /CREATE VIEW newSalary AS
br /SELECT id, CASE WHEN COUNT(emp) = 0 THEN salary + 100
br /WHEN COUNT(emp) 1 THEN salary - 100
br /ELSE salary
br /END AS v
br /FROM employee LEFT JOIN disciplinary ON (id = emp)
br /GROUP BY id, salary;
br /
br /So far so good, however: then we update the table itself, as follows:
br /
br /UPDATE employee
br /SET slary = (SELECT v FROM newSalary
br /WHERE newSalary.id = employee.id)
br /WHERE id IN (SELECT id FROM newSalary);
br /
br /Look at the second statement: what is the significance of the last WHERE check?
br /
br /The newSalary is a *view* on the employee table, and it is a view that does not exclude any records -- so what exactly are we checking now? If you've got a record to update, it is already in ! The view is on the table, it IS the table, it doesn't matter if you look at a record directly in the table or through a view, it's one and the same set element. You can't find a record in the table that's not in the view!
br /
br /3. Hack 13, "Choose the Right Join Style for Your Relationships", p.42 (typos): in the third paragraph, the reference to 'budget' table should have been to 'staff'; 'TRO2' should have been 'TR01'.
br /
br /Also, at the very beginning, "When a relationship between tables is optional, you need an OUTER JOIN. When querying over many changes, if you require an OUTER JOIN you sometimes have to change all the other INNER JOINs into OUTER JOINs."
br /
br /What does this mean? "When a relationship between tables is optional, you need an OUTER JOIN"? This doesn't mean a damn thing. Maybe it should be something like "an existing match" instead of "relationship"? I think so, but who knows...
 Mix of fun and useful information
I haven't found the book to be extremely useful, but it was a good read, with a few "that's another good way to do that" moments. It did get me interested in researching other topics I wasn't familiar with, bonus. If you work with SQL regularly you'll know a lot of this stuff. It is a fun easy read.
One of the best advanced SQL books I've seen
These hacks aren't for the beginning user, but for more advanced users. The authors explain the hacks, which can be written for any of a number of different databases, but then they explain necessary changes to allow a user of a differing db product to use the hacks as well.
br /
br /MySQL, SQL Server, Postgres, Oracle among the ones covered.
br /
br /If you are looking to make a step up from a decent database user to a very good one, this is a book for you.
Good set of SQL tricks and tips if you have some experience
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.
br /
br /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:
br /
br /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.
br /Hack 1. Run SQL from the Command Line
br /Hack 2. Connect to SQL from a Program
br /Hack 3. Perform Conditional INSERTs
br /Hack 4. UPDATE the Database
br /Hack 5. Solve a Crossword Puzzle Using SQL
br /Hack 6. Don't Perform the Same Calculation Over and Over
br /
br /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.
br /Hack 7. Modify a Schema Without Breaking Existing Queries
br /Hack 8. Filter Rows and Columns
br /Hack 9. Filter on Indexed Columns
br /Hack 10. Convert Subqueries to JOINs
br /Hack 11. Convert Aggregate Subqueries to JOINs
br /Hack 12. Simplify Complicated Updates
br /Hack 13. Choose the Right Join Style for Your Relationships
br /Hack 14. Generate Combinations
br /
br /Chapter 3, Text Handling - This chapter contains a number of hacks focused on efficient and effective text querying.
br /Hack 15. Search for Keywords Without LIKE
br /Hack 16. Search for a String Across Columns
br /Hack 17. Solve Anagrams
br /Hack 18. Sort Your Email
br /
br /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.
br /Hack 19. Convert Strings to Dates
br /Hack 20. Uncover Trends in Your Data
br /Hack 21. Report on Any Date Criteria
br /Hack 22. Generate Quarterly Reports
br /Hack 23. Second Tuesday of the Month
br /
br /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.
br /Hack 24. Multiply Across a Result Set
br /Hack 25. Keep a Running Total
br /Hack 26. Include the Rows Your JOIN Forgot
br /Hack 27. Identify Overlapping Ranges
br /Hack 28. Avoid Dividing by Zero
br /Hack 29. Other Ways to COUNT
br /Hack 30. Calculate the Maximum of Two Fields
br /Hack 31. Disaggregate a COUNT
br /Hack 32. Cope with Rounding Errors
br /Hack 33. Get Values and Subtotals in One Shot
br /Hack 34. Calculate the Median
br /Hack 35. Tally Results into a Chart
br /Hack 36. Calculate the Distance Between GPS Locations
br /Hack 37. Reconcile Invoices and Remittances
br /Hack 38. Find Transposition Errors
br /Hack 39. Apply a Progressive Tax
br /Hack 40. Calculate Rank
br /
br /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.
br /Hack 41. Copy Web Pages into a Table
br /Hack 42. Present Data Graphically Using SVG
br /Hack 43. Add Navigation Features to Web Applications
br /Hack 44. Tunnel into MySQL from Microsoft Access
br /Hack 45. Process Web Server Logs
br /Hack 46. Store Images in a Database
br /Hack 47. Exploit an SQL Injection Vulnerability
br /Hack 48. Prevent an SQL Injection Attack
br /
br /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.
br /Hack 49. Keep Track of Infrequently Changing Values
br /Hack 50. Combine Tables Containing Different Data
br /Hack 51. Display Rows As Columns
br /Hack 52. Display Columns As Rows
br /Hack 53. Clean Inconsistent Records
br /Hack 54. Denormalize Your Tables
br /Hack 55. Import Someone Else's Data
br /Hack 56. Play Matchmaker
br /Hack 57. Generate Unique Sequential Numbers
br /
br /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.
br /Hack 58. Store Parameters in the Database
br /Hack 59. Define Personalized Parameters
br /Hack 60. Create a List of Personalized Parameters
br /Hack 61. Set Security Based on Rows
br /Hack 62. Issue Queries Without Using a Table
br /Hack 63. Generate Rows Without Tables
br /
br /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.
br /Hack 64. Determine Your Isolation Level
br /Hack 65. Use Pessimistic Locking
br /Hack 66. Use Optimistic Locking
br /Hack 67. Lock Implicitly Within Transactions
br /Hack 68. Cope with Unexpected Redo
br /Hack 69. Execute Functions in the Database
br /Hack 70. Combine Your Queries
br /Hack 71. Extract Lots of Rows
br /Hack 72. Extract a Subset of the Results
br /Hack 73. Mix File and Database Storage
br /Hack 74. Compare and Synchronize Tables
br /Hack 75. Minimize Bandwidth in One-to-Many Joins
br /Hack 76. Compress to Avoid LOBs
br /
br /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.
br /Hack 77. Fill in Missing Values in a Pivot Table
br /Hack 78. Break It Down by Range
br /Hack 79. Identify Updates Uniquely
br /Hack 80. Play Six Degrees of Kevin Bacon
br /Hack 81. Build Decision Tables
br /Hack 82. Generate Sequential or Missing Data
br /Hack 83. Find the Top n in Each Group
br /Hack 84. Store Comma-Delimited Lists in a Column
br /Hack 85. Traverse a Simple Tree
br /Hack 86. Set Up Queuing in the Database
br /Hack 87. Generate a Calendar
br /Hack 88. Test Two Values from a Subquery
br /Hack 89. Choose Any Three of Five
br /
br /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.
br /Hack 90. Implement Application-Level Accounts
br /Hack 91. Export and Import Table Definitions
br /Hack 92. Deploy Applications
br /Hack 93. Auto-Create Database Users
br /Hack 94. Create Users and Administrators
br /Hack 95. Issue Automatic Updates
br /Hack 96. Create an Audit Trail
br /
br /Chapter 12, Wider Access - This chapter look at how to manage a diverse range of users who have SQL-level access to your databases.
br /Hack 97. Allow an Anonymous Account
br /Hack 98. Find and Stop Long-Running Queries
br /Hack 99. Don't Run Out of Disk Space
br /Hack 100. Run SQL from a Web Page
br /
br /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.
|
