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 |
| PDF |
Page 51
1st paragraph (section Double-FROM) |
In the section Double-FROM the text introduces a constructs from T-SQL that allow the use of a second FROM clause to perfom a delete operation on a table based on a more elaborate search that involves more than one table. This is an extremely good feature but SQL Server is not the only want to support this.
MySQL offers two multi-table syntax for performing delete operations that do well for the semantic described in the text.
this is an excerpt from the documentation on
http://dev.mysql.com/doc/refman/5.0/en/delete.html
"For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:"
The MySql version of the T-SQL code given in the book.
DELETE FROM gov_unit
USING gov_unit JOIN county ON gov_unit.id = county.id
WHERE county.population IS NULL;
best regards
Note from the Author or Editor:
Thanks for pointing out MySQL's support for multiple-table syntax. I had missed it, and you caught it, which is good.
It's a real challenge sometimes, to deal with similar functionality implemented using different keywords in different vendor versions of SQL. It's too easy sometimes to look at something like SQL Server's double-FROM syntax, check for that same syntax in the other products, and yet miss that some other product offers the same functionality using different syntax.
What's in the book is correct. The reader in this case is actually suggesting some additional content relating to MySQL. It's a good suggestion best left for the fourth edition |
Horacio Nuñez |
Dec 06, 2010 |
|
| PDF |
Page 53
Table 6. Common Aggregate Functions |
In the last row of the table 6 the text declares that:
VARIANCE(x) [Function] Returns the statistical variance. Use VAR in SQL Server. Not available in MySQL.
According to the MySQL Documentation the VARIANCE function have been available before the version 5.0.3 in which the standard function VAR_POP was added and now VARIANCE is an alias for VAR_POP.
I also notice that PostgreSQL and MySQL (since 5.0.3) uses VARIANCE as an alias for differents concepts. While PostgreSQL states that VARIANCE = VAR_SAMP (sample) as stated above in MySQL VARIANCE = VAR_POP (population).
I think this is worth be noted on the book since a copy-paste translation can arise incorrect results.
the following query explain it:
SELECT VARIANCE(u.county_id), VAR_SAMP(u.county_id), VAR_POP(u.county_id)
FROM upfall u
on MySQL the result is:
10.6094, 11.3167,10.6094
on PostgreSQL the result is:
11.3166666666666667, 11.3166666666666667, 10.6093750000000000
best regards
horacio
Note from the Author or Editor:
I agree. MySQL's slightly different interpretation of VARIANCE is indeed worth mentioning.
Let's make some changes here. Let's change the final entry in that table to read as follows:
VARIANCE(x) Returns the statistical variance. Is an alias to VAR_SAMP in PostgreSQL, and to VAR_POP in MySQL. Use VAR in SQL Server.
|
Horacio Nuñez |
Dec 06, 2010 |
Feb 01, 2011 |
| PDF |
Page 66
top |
SQL Server does not allow the use of functions in index definitions, so SQL Server should be excluded from the list of platforms on pg 66 that support the syntax on the top of page 67.
CREATE INDEX falls_name ON upfall (UPPER(name), open_to_public)
does not work on SQL Server, and returns the error message: "Incorrect syntax near '('."
Note from the Author or Editor:
On page 66, remove SQL Server from that sentence: "Oracle, PostgreSQL, and SQL Server also allow you to index column expressions:" |
wassaf |
Dec 13, 2010 |
Feb 01, 2011 |
| PDF |
Page 67/68
Inserting Data section |
Use the INSERT statement to insert new rows in a table. You
can insert one row, many rows (DB2 and MySQL), or the
results of a subquery.
Should Be:
Use the INSERT statement to insert new rows in a table. You
can insert one row, many rows (DB2, MySQL, SQL Server and PostgreSQL), or the results of a subquery.
Diff/Why:
Add SQL Server:
In the next page at section "Multirow Inserts" the SQL Server is mention as one of the engines that support this feature in contradiction (typo) with the current text in the page, and SQL Server do support it.
Add PostgreSQL:
PostgreSQL do support it too.
Note from the Author or Editor:
Good catch. I had updated the section "Multirow Inserts" on page 68, but somehow that parenthetical on page 67 escaped my notice. Thanks for pointing out the error.
Delete that parenthetical entirely. Then change the first sentence in "Multirow Inserts" to read as follows:
"Many platforms provide the ability to insert multiple rows..."
|
Horacio Nuñez |
Dec 09, 2010 |
Feb 01, 2011 |
| PDF |
Page 68
Multirow Inserts |
DB2, MySQL, and SQL Server 2008 provide the ability to insert
multiple rows via repeated value lists in the VALUES clause:
Should Be:
DB2, MySQL, SQL Server 2008 and PostgreSQL provide the ability to insert
multiple rows via repeated value lists in the VALUES clause:
Diff/Why:
Add PostgreSQL:
Since version 8.4 this database engine do support this function.
http://www.postgresql.org/docs/8.4/static/dml-insert.html
|
Horacio Nuñez |
Dec 09, 2010 |
Feb 01, 2011 |
| PDF |
Page 78
2nd paragraph |
For example, the following statement creates a table of years
via a subquery, and then it joins the table of months to
upfall based on the value of upfall's confirmed_date. This
particular example runs on MySQL and Oracle (because they
support date literals):
Should Be:
For example, the following statement creates a table of years
via a subquery, and then it joins the table of months to
upfall based on the value of upfall's confirmed_date. This
particular example runs on MySQL, PostgreSQL and Oracle (because they
support date literals):
Diff/Why:
Add PostgreSQL:
At least from version 7.3 PostgreSQL allows the use of the DATE function that accepts a date literal.
http://pgsqld.active-venture.com/functions-datetime.html
|
Horacio Nuñez |
Dec 09, 2010 |
Feb 01, 2011 |
| PDF |
Page 138
NOTE section in middle of page |
"NOTE
SQL Server began to support TRIM in SQL Server 2005."
'Trim' is not a recognized built-in function name in SQL Server, nor is it even a reserved keyword. TRIM is not supported by T-SQL in any version of SQL Server. RTRIM and LTRIM are the only similar functions supported. Though TRIM is usable in the SSIS expression language, it is not usable in the context of the book.
Note from the Author or Editor:
Remove the note. |
wassaf |
Dec 13, 2010 |
Feb 01, 2011 |