SQL Hacks

Errata for SQL Hacks

Submit your own errata for this product.


The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page 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)

Note from the Author or Editor:
Hack #2, p15 In the very last SQL query about "SQL Server", the line: "libraryReturn(book,member,returnDate,fine)" needs to be replaced with: "libraryReturn(member,book,returnDate,fine)" As the submitter says, the other queries are fine.

Anonymous    Sep 13, 2013
Printed
Page 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)

Note from the Author or Editor:
Hack #11, pp37,38 In paragraph 1, add in the following clarification text after "shown in Table 2-6.". "This shows the total number of items ordered by a customer on a particular day." In the SQL shown in the bottom of page 37, replace "o1.whn" with "o1.totalitems". replace "MAX(whn)" with "MAX(o2.totalitems)". In the SQL shown at the top of page 38. replace "HAVING o1.whn = max(o2.whn)" with "HAVING o1.totalitems = MAX(o2.totalitems)"

Anonymous    Sep 13, 2013
Printed
Page 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)

Note from the Author or Editor:
Hack #12, pp39 Suggest inserting an aside note box with the following contents: A reader had problems running the CREATE VIEW query in the middle of page 39. They were using the HSQLDB database engine, which is not one of the ones the authors looked at when the original hack was written. The reader has submitted an alternative SQL statement using a derived table (see Hack #6). CREATE VIEW newSalary AS SELECT id, CASE WHEN dt = 0 THEN salary+100 WHEN dt > 1 THEN salary-100 ELSE salary END AS v FROM employee LEFT JOIN (SELECT id AS bar, COUNT(emp) as dt FROM employee LEFT JOIN disciplinary ON (id=emp) GROUP BY id) ON (id = bar) The reader also asks about performance issues in doing this. As the derived table is recalculated for each row of employee, there is a cost greater that that of the original query. However the derived table does get processed quickly, as it is constrained using id=emp, which will no doubt use an index. Thus the performance impact it likely to be small on a well designed database engine.

Anonymous   
Printed
Page 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.

Note from the Author or Editor:
Hack #13, p41 In table 2-7, row starting "TR02", in the "budget" column: "CTH22" should be replaced with "CT22"

Anonymous    Sep 13, 2013
Printed
Page 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.

Note from the Author or Editor:
Hack #13, p42 In the last text paragraph "Because TR02 has a NULL value for the budget, the join condition filters that row out.", the string "TR02" should be replaced with "TR01".

Anonymous    Sep 13, 2013
Printed
Page 56
A quadratic hash function example

The SQL example for the quadratic hash function in the book is the following: UPDATE dict SET h = (SELECT SUM(ORD(SUBSTRING(w, i, 1)) * ORD(SUBSTRING(w, i, 1))) FROM integers WHERE <= LENGTH(w)); Actually running this SQL will throw syntax error, the letter 'i' is missing in the WHERE clause before the <= operator so the last line correctly would be: WHERE i <= LENGTH(w));

Note from the Author or Editor:
Hack #17, p56 In the first SQL statement on this page, the line "-> WHERE <= LENGTH(w));" should have "WHERE <= LENGTH(w));" replaced with "WHERE i <= LENGTH(w));"

bpgergo  May 23, 2009  Sep 13, 2013
Printed
Page 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.

Note from the Author or Editor:
Hack #17, p58 In the second SQL query in the middle of this page, involving the anagram search using the word "tango", the current SQL line "WHERE LENGTH(a.h) = LENGTH(b.h);" should be replaced with "WHERE LENGTH(a.w) = LENGTH(b.w);"

Anonymous    Sep 13, 2013
Printed
Page 72
2nd paragraph

"Oracle also has the TO_DATE function, so it allows TO_DATE(whn, 'yyyymm'), for example." Since whn is a date column, the function mentioned should be TO_CHAR, not TO_DATE. It's OK on page 73, though.

Note from the Author or Editor:
Hack #21, p72 In paragraph 2, "Oracle also has the TO_DATE function, so it allows TO_DATE(whn,'yyymm'), for example.". Both occurrences of "TO_DATE" should be replaced with "TO_CHAR".

Anonymous    Sep 13, 2013
Printed, ePub
Page 411.5
Example given Just before "optimistic locking"

SELECT chairid WHERE booked IS NULL AND location='front' FOR UPDATE no from clause -- should be: SELECT chairid FROM seat WHERE booked IS NULL AND location='front' FOR UPDATE;

Note from the Author or Editor:
page 231 of printed version. The new SQL probably won't fit on one line. The phrase "AND location='front'" should still be in bold SELECT chairid FROM seat WHERE booked IS NULL AND location='front' FOR UPDATE;

Nat Howard  Jul 26, 2013  Sep 13, 2013