Errata

mysql Ver 14.12 Distrib 5.0.24a, for unknown-openbsd3.9 (i386) using readline 4.3

mysql> show tables;
+---------------------------+
| Tables_in_chapter2_person |
+---------------------------+
| favorite_food |
| person |
+---------------------------+
2 rows in set (0.00 sec)

mysql> desc favorite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | 0 | |
| food | varchar(20) | NO | PRI | | |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc person;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

mysql> select * from person;
+-----------+---------+--------+--------+------------+------------------+--------
+-------+---------+-------------+
| person_id | fname | lname | gender | birth_date | address | city |
state | country | postal_code |
+-----------+---------+--------+--------+------------+------------------+--------
+-------+---------+-------------+
| 1 | William | Turner | M | 1972-05-27 | 1224 Tremont St. | Boston | MA
| USA | 02138 |
+-----------+---------+--------+--------+------------+------------------+--------
+-------+---------+-------------+
1 row in set (0.00 sec)

mysql> select * from favorite_food;
+-----------+---------+
| person_id | food |
+-----------+---------+
| 1 | cookies |
| 1 | nachos |
| 1 | pizza |
+-----------+---------+
3 rows in set (0.00 sec)

mysql> insert into favorite_food (person_id, food) -> values (999, 'lasagna');
Query OK, 1 row affected (0.00 sec)

This last insert shouldn't happen, should it?

The assertion is made that the query at the two queries on the page are equivalent. Although they give the same results from the sample database, they are not equivalent. The purpose of the queries seems to be to retrieve accounts that are opened by Tellers and Head Tellers at the Wobern Branch. However, the second query contains a join between the branch and employee table. The join further restricts the query to tellers and head tellers that are currently assigned to the Woburn branch. Consider the case in which an employee had be reassigned to a different branch. The first query would include accounts opened by that employee at their old branch while the second would omit those accounts. An equivalent second query would be:
SELECT account_id, product_cd, cust_id
FROM account
WHERE (open_branch_id, open_emp_id) IN
(SELECT b.branch_id, e.emp_id
FROM branch b, employee e
WHERE b.name = 'Woburn Branch'
AND (e.title = 'Teller' OR e.title = 'Head Teller'));

This version of the query omits the join between branch and employee.

The first query on page 169 is supposed to return basically the same data as the longer query on the previous page (168). But it returns 3 extra rows, 14 rows vs. 12 for the page 168 query.

The error is that the shorter query on page 169 does not include a JOIN and WHERE clause that is included on page 168. The text states that the only reason for the JOINs on the page 168 query is to get the display text (ie, for employee names from the emp_id, etc), but the other reason for the JOINs is to include the WHERE clause mentioned above. If you include the missing WHERE clause, the query on page 169 returns the same results as the query on page 168. This modification worked perfectly for me on my system:

select a.product_cd, a.open_branch_id, a.open_emp_id, sum(a.avail_balance)
from account a
join product p
on a.product_cd = p.product_cd
where p.product_type_cd = 'ACCOUNT'
group by a.product_cd, a.open_branch_id, a.open_emp_id;

Also, the second query on page 169, which is also supposed to return the same data as the query on page 168 and first query on page 169, has a minor error in that the order of the result set is not sorted the same way as the result set returned from the query on page 168.

This is probably caused by the lack of an ORDER BY clause on both queries, and the details of the MYSQL implementation, due to the order of a result set being undefined in SQL without an ORDER BY clause. As far as I can see, the data for this query is correct, only the sort order is wrong; but its still confusing when trying to compare the query results, which should ideally be identical.

The first query on page 169 is supposed to return basically the same data as the longer query on the previous page (168). But it returns 3 extra rows, 14 rows vs. 12 for the page 168 query.

The error is that the shorter query on page 169 does not include a JOIN and WHERE clause that is included on page 168. The text states that the only reason for the JOINs on the page 168 query is to get the display text (ie, for employee names from the emp_id, etc), but the other reason for the JOINs is to include the WHERE clause mentioned above. If you include the missing WHERE clause, the query on page 169 returns the same results as the query on page 168. This modification worked perfectly for me on my system:

select a.product_cd, a.open_branch_id, a.open_emp_id, sum(a.avail_balance)
from account a
join product p
on a.product_cd = p.product_cd
where p.product_type_cd = 'ACCOUNT'
group by a.product_cd, a.open_branch_id, a.open_emp_id;

Also, the second query on page 169, which is also supposed to return the same data as the query on page 168 and first query on page 169, has a minor error in that the order of the result set is not sorted the same way as the result set returned from the query on page 168.

This is probably caused by the lack of an ORDER BY clause on both queries, and the details of the MYSQL implementation, due to the order of a result set being undefined in SQL without an ORDER BY clause. As far as I can see, the data for this query is correct, only the sort order is wrong; but its still confusing when trying to compare the query results, which should ideally be identical.