SQL Hacks
by Andrew Cumming, Gordon Russell

The unconfirmed error reports are from readers.  They have not yet been 
approved or disproved by the author or editor and represent solely the
opinion of the reader.

Here's a key to the markup:
        [page-number]: serious technical mistake
        {page-number}: minor technical mistake
        <page-number>: important language/formatting problem
        (page-number): language change or minor formatting problem
        ?page-number?: reader question or request for clarification

This page was updated June 3, 2008.


UNCONFIRMED errors and comments from readers: 

{15} bottom of page;

There are two SQL INSERT examples at the bottom of the page. In the first one, the order of the first two column names is correct and corresponds with the order of the values given to be inserted.

In the second one, the first two column names are transposed both in relation to the first example and in relation to the values given later in the statement to be inserted:

The former specifies

libraryReturn(member,book,returnDate,fine)

but the latter erroneously specifies

libraryReturn(book,member,returnDate,fine)


[37-38] Hack #11;
The Hack says that the SQL gets the most totalitems, but it simply gets the latest whn (a date) for each customer's orders


[37-38] Hack #11;
The query is supposed to return the greatest number of items for each person. It does not. It brings back the latest date that each person purchased something.

The SQL *should* be:

SELECT o1.customer,o1.whn,o1.totalitems
FROM orders o1 JOIN orders o2 on (o1.customer = o2.customer)
GROUP BY o1.customer,o1.whn,o1.totalitems
HAVING o1.totalitems = max(o2.totalitems)


[39] CREATE VIEW example SQL in middle of page;
The view can be created but select * from newSalary fails with refernce to a boolean error. Not sure if this is due to using HSQLDB.

As an alternate solution, to calculate the new salaries based on the number of discrepancies per employee, I did the following:

select id , case when v = 0 then salary + 100
when v > 1 then salary - 100
else salary
end as adj_salary
from employee join
(select id as bar, count(emp) as v
from employee left join disciplinary on (id=emp)
group by id )
on (id = bar)

The view named newSalary could then be created using the above. This apporach may have performance issues (would like to have your opinion on this). However, it was fun to write and leveraged the use of a derived table (as per page 25, Hack #6)


{41} p41, Table 2-7, tripID=TR02, @budget column;
First mySQL example at top of p42 would not match because the budget column (foreign key) does not match up to primary key on trip table.


(42) paragraph below second table;
"Because TR02 has a NULL value for the budget, the join condition filters that row out." TR01, not TR02, has the NULL value.


{58}  SQL example for second paragraph ("If you check the LENGTH ...").;
The SQL snippet

  mysql> SELECT a.w, a.h FROM dict a
      -> JOIN dict b ON (a.h=b.h AND b.w='tango')
      -> WHERE LENGTH(a.h) = LENGTH(b.h);

should be

  mysql> SELECT a.w, a.h FROM dict a
      -> JOIN dict b ON (a.h=b.h AND b.w='tango')
      -> WHERE LENGTH(a.w) = LENGTH(b.w);

since you would want to test the lengths of the words, not the hashes. 
If the word hashes match then the hash lengths probably match, too.

The reason for adding this test was to filter out words with matching 
hashes but dissimilar lengths, such as the example of 'aaaa' and 'b' 
given in the text.