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.
| 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 |