The errata list is a list of errors and their corrections that were found after the product was released.
The following errata were submitted by our customers and have not yet been approved or disproved by the author or editor. They solely represent the opinion of the customer.
| Version |
Location |
Description |
Submitted By |
| 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)
|
Anonymous |
| Printed |
Page 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
|
Anonymous |
| 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)
|
Anonymous |
| Printed |
Page 37
Hack #11, first sentence under Table 2-6. |
This is the problem presented in hack #11:
"Suppose you have the orders table shown in Table 2-6. Now suppose you need to show the date on which each customer purchased the most totalitems:"
And the solution which comes next is for a totally different problem.
The solution in the book is the folowing:
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.whn = max(02.whn)
Actually the whole paragraph of this solution is consequently wrong,
there is an other wrong select statement and the the result table is wrong as well.
Of course the problem could be could be modified in order to match the solution:
"Suppose you have the orders table shown in Table 2-6. Now suppose you need to show the latest date on which a customer has purchased something:"
Or alternatively, the solution could be modified to match the original problem. The correct select statement for the original problem would 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(02.totalitems)
(Note that the other (the previous) select statement on the bottom of page 37. and the result table on the next page should be corrected as well.)
|
bpgergo |
| 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)
|
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.
|
Anonymous |
| 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.
|
Anonymous |
| 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));
|
bpgergo |
| 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.
|
Anonymous |
