Errata

Learning SQL

Errata for Learning SQL, Third Edition

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
Page xv, 18
Points 3 and 4 of bullet points

Source command on the book ends with a semicolon ( ; ). However, the source command cannot be ended with ; . Only if we remove the semicolon, can we run the command without mistakes.

Note from the Author or Editor:
At the top of page 18, please change #3 and #4 to remove the semicolons prior to the "and press Enter" directives.

Ivo Tavares  Oct 22, 2020  Jul 23, 2021
Page N/A
N/A

In Chapter 4, the third code sample under section "THE BETWEEN OPERATOR" is missing a semicolon at the end, preventing the query from being executed.

(The page number was omitted due to a lack of them on O'Reilly Online Learning)

Note from the Author or Editor:
On page 75, please add a semicolon at the end of the SELECT statement:
mysql> SELECT customer_id, rental_date
-> FROM rental
-> WHERE rental_date >= '2005-06-16'
-> AND rental_date <= '2005-06-14';
Empty set (0.01 sec)

Alexander Summers  Dec 20, 2020  Jul 23, 2021
Page Appendix B
Heading Exercise 3-2, 3rd line of example

This exercise is listed in Chapter 3, "Query Primer".

The solution to this exercise uses the IN keyword, but the IN keyword isn't covered in Chapter 3, it's covered in Chapter 4.

It seems an adequate replacement would be the OR keyword.

Note from the Author or Editor:
Code block at top of page 322 (Appendix B Exercise 3-2) should be:
mysql> SELECT actor_id, first_name, last_name
-> FROM actor
-> WHERE last_name = 'WILLIAMS' OR last_name = 'DAVIS';
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 4 | JENNIFER | DAVIS |
| 101 | SUSAN | DAVIS |
| 110 | SUSAN | DAVIS |
| 72 | SEAN | WILLIAMS |
| 137 | MORGAN | WILLIAMS |
| 172 | GROUCHO | WILLIAMS |
+----------+------------+-----------+
6 rows in set (0.01 sec)

Bradley Turek  Feb 11, 2021  Jul 23, 2021
Printed
Page xiv and 17
2nd paragraph

www.katacoda.com/mysql-db-sandbox/scenarios/mysql-sandbox
404 and link was not avaialbe.

Note from the Author or Editor:
Here are updated instructions:

Go to learning.oreilly.com/scenarios/mysql-sandbox/9781492079705 to access the MySQL Sandbox, which has the Sakila Sample database loaded in a MySQL instance. If you don't have one already, you’ll have to set up an O'Reilly account. Then, click on the Start button and begin.

sophia_chou  Nov 06, 2022  Dec 02, 2022
1
Chapter 2, GENERATING NUMERIC KEY DATA, ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Chapter 2, GENERATING NUMERIC KEY DATA

Statement fails with error Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'sakila.favorite_food'

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

In order for statement to work must use the statement
set foreign_key_checks=0;
prior to executing it, then change it back after executing it
set foreign_key_checks=1;

P.S. You don't provide page numbers online so requiring the page number of error for online doesn't work well

Note from the Author or Editor:
Reader is correct, and proposed fix is correct. I would like to add a warning box just after "ALTER TABLE person ..." example on page 34. It should say:
If you are running these statements in your database, you will first need to disable the foreign key constraint on the favorite_food table, and then re-enable the constraints when finished. The progression of statements would be:
set foreign_key_checks=0;
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
set foreign_key_checks=1;

Daniel Dunbar  May 09, 2020  Sep 04, 2020
Page 49
3rd paragraph, just below bullet points.

Instead of employee table, should be language table.

Note from the Author or Editor:
please change "employee" to "language" at the end of the sentence following the 4 bullet points in the middle of page 49.

Ivo Tavares  Oct 22, 2020  Jul 23, 2021
Page 100
Excercise 5-3

If you substitute 'a1.address <> a2.address' with 'a1.address < a2.address', then there will be no duplicates in the output.

Note from the Author or Editor:
The answer to exercise 5-3 in page 327 of Appendix B needs to be modified:
mysql> SELECT a1.address addr1, a2.address addr2, a1.city_id
-> FROM address a1
-> INNER JOIN address a2
-> ON a1.city_id = a2.city_id
-> AND a1.address < a2.address;
+----------------------+--------------------+---------+
| addr1 | addr2 | city_id |
+----------------------+--------------------+---------+
| 23 Workhaven Lane | 47 MySakila Drive | 300 |
| 1411 Lillydale Drive | 28 MySQL Boulevard | 576 |
| 1497 Yuzhou Drive | 548 Uruapan Street | 312 |
| 43 Vilnius Manor | 587 Benguela Manor | 42 |
+----------------------+--------------------+---------+
4 rows in set (0.01 sec)

Nik Gorylenko  Jul 16, 2021  Jul 23, 2021
Printed
Page 130
2nd paragraph (not counting table 7.1)

The following example calculates the remainder when 4 is divided into 10

it should instead be:

The following example calculates the remainder when 10 is divided into 4

Note from the Author or Editor:
My wording is correct, but perhaps confusing. I say "when 4 is divided into 10", but it would be clearer if the wording was "when 10 is divided by 4".
So, in the middle of page 130, please change the sentence to be:
The following example calculates the remainder when 10 is divided by 4:"

Anonymous  Aug 28, 2020  Sep 04, 2020
Printed
Page 153
"Using Expressions" section

select max(datediff(return_date, rental_date))
from rental;

returns 10 and not 33.

Note from the Author or Editor:
The code block in the middle of page 153 shows an answer of 33. The correct answer is 10, and the following sentence needs to be modified to say "which in this case is 10 days"

Ivo Tavares  Oct 31, 2020  Jul 23, 2021
Page 236
4th bullet point from the bottom

The bullet points list 'on update cascade' twice. The first one should be 'on delete cascade'.

Note from the Author or Editor:
there are 5 bullets at the bottom of page 236. The 2nd bullet should be:
on delete cascade

Terry Letsche  Nov 03, 2020  Jul 23, 2021
Page 269
SQL example for "Localized Sorting"

As written, this query returns:

ERROR 1055 (42000): Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'sakila.payment.payment_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It can be fixed by changing month(payment_date) in the last line to month(any_value(payment_date))




Note from the Author or Editor:
Please replace the code block on page 269 with the following:
mysql> SELECT quarter(payment_date) quarter,
-> monthname(payment_date) month_nm,
-> sum(amount) monthly_sales,
-> rank() over (order by sum(amount) desc) sales_rank
-> FROM payment
-> WHERE year(payment_date) = 2005
-> GROUP BY quarter(payment_date), monthname(payment_date)
-> ORDER BY 1,2;
+---------+----------+---------------+------------+
| quarter | month_nm | monthly_sales | sales_rank |
+---------+----------+---------------+------------+
| 2 | June | 9631.88 | 3 |
| 2 | May | 4824.43 | 4 |
| 3 | August | 24072.13 | 2 |
| 3 | July | 28373.89 | 1 |
+---------+----------+---------------+------------+
4 rows in set (0.03 sec)

Peter Fales  Apr 26, 2021  Jul 23, 2021