SQL in easy steps, 3rd Edition errata

I was reading through SQL in easy steps, 3rd Edition and made it to page 152 where it covered Sub-query calculated fields. It’s a fancy way of saying a SELECT statement within a SELECT statement, but isn’t that hard to grasp if you’re familiar with how variable interpolation works with Bash or similar shells.

My problem was understanding how even though a column wasn’t listed as one to be returned by SELECT it was seemingly returned as a byproduct of an ORDER BY statement. I couldn’t find this mentioned in any of the MySQL documentation or another MySQL book I have so I was getting pretty frustrated. I fired my MySQL this morning and opened up subquery-calc.sql and had a good laugh: The mental block I was having was due to an error in the book’s code that wasn’t present in the SQL file.

I fired off an errata report in the hopes that it will be correct in future printings so others won’t be as confused as I was.

Errata report for SQL in easy steps, 3rd Edition
Page 152 & 153

The SELECT query and sub-query on page 152 is incorrect.

Instead of:

SELECT name,
  ( SELECT COUNT(*) FROM orders
    WHERE orders.acc_num = customers.acc_num )
AS number_of_orders FROM customers
ORDER BY customers.acc_num;

it should be:

SELECT name, customers.acc_num,
  ( SELECT COUNT(*) FROM orders
    WHERE orders.acc_num = customers.acc_num )
AS number_of_orders FROM customers
ORDER BY customers.acc_num;

As it was, it fails to return the customers.acc_num column.

On page 153, the equivalent query to page 152 is also incorrect:

SELECT name, COUNT(*) AS number_of_orders
FROM  customers, orders
WHERE customers.acc_num = orders.acc_num
GROUP BY name
ORDER BY customers.acc_num;

but is correct in the subquery-calc.sql file:

SELECT name, customers.acc_num, COUNT(*) AS number_of_orders
FROM  customers, orders
WHERE customers.acc_num = orders.acc_num
GROUP BY name
ORDER BY customers.acc_num;