Errata

On Page 106 the query is:

SELECT drink_name FROM drink_info

WHERE

calories BETWEEN 30 AND 60;


The results include "Soda and It."

However, based on the table on page 105, "Soda and It" only has 19 calories and should not be included in the results set.

The only ones that should be in the results are:

Blackthorn
Oh My Gosh
Greyhound
Indian Summer

doughnut_type is created for length 6.but the example table above that list has two entries using value 'cruller' which are too long for that field.

Note from the Author or Editor:
Change doughnut_type to VARCHAR(8)

Also, the text below that should be changed to 8 instead of 6.

The description of the decimal type is wrong and the example number actually doesn't fit the datatype provided.

The number before the comma in the DECIMAL data type is defined as the number of significant digits of the value. To store 5678.39, you would actually need DEC(6,2). MySQL accepts the value none the less, but a standards-compliant database should complain.

tax_rate must be defined as dec(5,3) to hold values from 0.000 to 100.000 (99.999 being the significant number here) to correctly hold all the percentages in the format shown in the example.

The provided solution dec(4,2) would not be sufficient to even hold the number provided in the question on page 25 because 3.755 requires three digits after the decimal point to store.

In http://oreilly.com/catalog/9780596526849/errata/9780596526849.1113 you state this needs to be DEC(5,3) for tax rates of 0.000 to 100.000. Obviously if you were to tax at 100.000, you'd need DEC(6,3).

Note from the Author or Editor:
Change DEC(5,3) to read DEC(6,3) in the fourth column in the row labeled "tax_rate"

The CREATE DATABASE and USE DATABASE commands need to end in a semicolon

This arrow should have a handwritten annotation that reads, "The total digits allowed are 3, with 1 before and 2 after the decimal."

doughnut_type is created for length 6.but the example table above that list has two entries using value 'cruller' which are too long for that field. (same problem as on page 19)

Note from the Author or Editor:
In the CREATE TABLE code, change doughnut_type to VARCHAR(8)

Added an annotation to the bottom part of the page: "You could also have used the obvious query SELECT drink_name FROM easy_drinks WHERE drink_name = 'Bull Frog';"

Added an annotation to the bottom part of the page: "You could also have used the obvious query SELECT drink_name FROM easy_drinks WHERE drink_name = 'Bull Frog';"

Solution to third query should be the following:
SELECT last_name, first_name, email
FROM my_contacts
WHERE location = 'San Antonio, TX'
AND gender = 'M'
AND status = 'single';

In the bottom left corner, => should be <=

In the bottom right of the page, the Greater to or Equal sign is listed as =>, should be >= (this is used correctly on the next page in demonstration)

Note from the Author or Editor:
I believe this was fixed in the last reprint. 2007 edition had this error.

The text, "...without using the names of the drinks in your query." should be added to the end of the last question on the page.

SELECT drink_name, color, ice FROM drink_info
WHERE
cost >=3.8;

Removed "=" from ">=" in solution. Now the solution query reads: SELECT drink_name, color, ice FROM drink_info WHERE cost > 3.8;

SELECT drink_name, color, ice FROM drink_info
WHERE
cost >=3.8;

Removed "=" from ">=" in solution. Now the solution query reads: SELECT drink_name, color, ice FROM drink_info WHERE cost > 3.8;

Sixth WHERE clause from the top should only have one semicolon at the
end. It should be:

WHERE state LIKE 'M%' or state LIKE 'A%';

The comment says, "We'll get drink names that begin with G and O, and all the letters in between."

Solution should read: "... drink_name BETWEEN 'G' AND 'P'...";
Changed the annotation to: "This one is a bit tricky! We need to use the letter after O to make sure we get drink names that begin with O. Test it out and see for yourself."

The comment says, "We'll get drink names that begin with G and O, and all the letters in between."

Solution should read: "... drink_name BETWEEN 'G' AND 'P'...";
Changed the annotation to: "This one is a bit tricky! We need to use the letter after O to make sure we get drink names that begin with O. Test it out and see for yourself."

Changed the solution to:
SELECT drink_name FROM easy_drinks
WHERE main IN ('peach nectar', 'soda');

Changed the solution to:
SELECT drink_name FROM easy_drinks
WHERE main IN ('peach nectar', 'soda');

Changed to: WHERE main BETWEEN 'P' and 'T';

Also, the first solution annotation was changed to:
"You could also have used this WHERE clause: WHERE (BETWEEN 'P' and 'T');
This would have worked because we don't have any other main ingredients that satisfy the condition. But in general when you have a large table in the real world, you won't know what is in it, which is why you are querying in the first place.

Changed to: WHERE main BETWEEN 'P' and 'T';

Also, the first solution annotation was changed to:
"You could also have used this WHERE clause: WHERE (BETWEEN 'P' and 'T');
This would have worked because we don't have any other main ingredients that satisfy the condition. But in general when you have a large table in the real world, you won't know what is in it, which is why you are querying in the first place.

Single quote needed before the word "cherry" in fifth set of
parentheses, Kiss on the Lips drink.

INSERT INTO clown_info
VALUES
('Sniffles', 'Tracy\'s, 'M, green and purple suit, pointy nose',
'climbing into tiny car');

Missing single quote after 'Tracy\'s Should be:

INSERT INTO clown_info
VALUES
('Sniffles', 'Tracy\'s', 'M, green and purple suit, pointy nose', 'climbing into tiny car')

Text currently reads:

"You can use WHERE clauses with DELETE statements the same way you can use them with INSERT statements."

In the statement above "INSERT" should be replaced with "SELECT".

Hence the corrected statement should be:

"You can use WHERE clauses with DELETE statements the same way you can use them with SELECT statements."

The final question on page 133 and 134 should be:
WHERE location = 'Snappy Bagel' OR rating = 3;

The arrows for Delete statement 4 point to the 1st, 3rd, and 4th row. They should point to the first, 2nd, and 4th row.

Delete statement 6 says "No Matches, did not DELETE." This is incorrect; the 2nd row would be deleted.

[NOTE: This correction to DELETE statement 6 is not needed if page 133 is corrected as listed above.]

This is an error made in the November 2007 reprint, not the original printing.
The line was changed to: WHERE location = 'Snappy Bagel' OR rating = 3;
The answer should be "No matches, did not DELETE" and should have no arrows pointing to the
table below.

This is an error made in the November 2007 reprint, not the original printing.
The line was changed to: WHERE location = 'Snappy Bagel' OR rating = 3;
The answer should be "No matches, did not DELETE" and should have no arrows pointing to the
table below.

Bottom big code block should be changed to:
WHERE
activities = 'yelling, dancing'
AND name = 'Clarabelle';

For this SQL query, the follow line should not end with a semicolon:

SET cost = cost + 1;

The semicolon causes the UPDATE query to update all rows.

Although the third query on 189 and 190 may work with some versions of MySQL, it throws a warning and some versions won't fix it. It should be changed to:

INSERT INTO your_table
VALUES (2, 'Bobby', 'Brady');

The third INSERT statement in Exercise 3 solution does not complete without error.

Annotated third insert statement:
This may or may not work for you. On some SQL versions it will give you an error, and on some just a warning such as
"Out of range value adjusted for column 'id' at row 1." If you get the error, Bobby's not in your table. With the warning, he will be.

The third INSERT statement in Exercise 3 solution does not complete without error.

Annotated third insert statement:
This may or may not work for you. On some SQL versions it will give you an error, and on some just a warning such as
"Out of range value adjusted for column 'id' at row 1." If you get the error, Bobby's not in your table. With the warning, he will be.

ALTER TABLE project_table

should be:

ALTER TABLE project_list

ALTER TABLE project_table

should be:

ALTER TABLE project_list

It reads yet:

ALTER TABLE project_table ...

should be

ALTER TABLE project_list ...

and the handwritten text above should read as

"Here's our table name"

It reads yet:

ALTER TABLE project_table ...

should be

ALTER TABLE project_list ...

and the handwritten text above should read as

"Here's our table name"

The VIN column is sized at 16 characters, but the example database listing (from page 218) uses 17 characters[1]. So, the VIN column should really be:

ADD COLUMN VIN VARCHAR(17) AFTER car_id

Notes:
[1] See http://en.wikipedia.org/wiki/Vehicle_Identification_Number

Point no. 4 says "Put the data we grabbed in step 2...... ". We haven't grabbed any data in step 2.

Note from the Author or Editor:
Change 4 to read
4. Put the data we grabbed in step 3 into one of the empty columns.

The categories I came up with differ from the book. If you run through the UPDATE queries, in the order provided on page 239, then:

"End of the line" should be in category "?" because it's affected by the drama, horror, sci-fi, and misc updates.

"Shark bait" should be labeled "family" because it's only affected by the "... family where for_kids = T" update.

"Angry Pirate" should be labeled "?" because it's affected by the comedy and misc updates.

The description of the SQL query:

"This query gives us a list of movies ordered by the
purchase date, with the newest ones first. For each
date, the movies purchased on that date are listed in
alphabetical order."

doesn't match the provided query:

SELECT title, purchased
FROM movie_table
ORDER BY title ASC, purchased DESC;

The query first orders by title then purchase date which doesn't match the (above) description. Either the description or the query needs to be changed to match the other.

Note from the Author or Editor:
Change the query to:

SELECT title, purchased
FROM movie_table
ORDER BY purchased DESC, title ASC;

The example query:

SELECT first_name, sales
FROM cookie_sales
ORDER BY first_name;

is supposed to order the results by first name (i.e. alphabetical order). The provided results, though, aren't in alphabetical order by first name (e.g. Nicole shows up before Britney).

The "Count the days" problem on the prior pages asks
"Write a query that will give us the number of days that each girl sold cookies"

The solution provided omits the statement

WHERE sales <>0

The proposed solution shows each girl selling on days when their total sold was zero. In other words without the above WHERE statement, all girls have the result of 7. With the WHERE statement you get the correct result.

Note from the Author or Editor:
Change third Sharpen answer to this:

SELECT first_name, COUNT(sale_date)
FROM cookie_sales
GROUP BY first_name
WHERE sales > 0;

Add annotation pointing at the last line (WHERE...) that says:
This weeds out days that the girls sold no coookies.

"To see songs 20 through 30..." to "Suppose we wanted to see songs 20 through 29..."

"To see songs 20 through 30..." to "Suppose we wanted to see songs 20 through 29..."

In the crossword puzzle, the clue for 10 down should be: "Use this in a SELECT to return the total value of results rather rather than the results themselves."

At the end of the CREATE TABLE command, the final closing parenthesis should come before the semicolon.

Final line of text in the instructions should read: "Finally, add these new columns to the appropriate tables: phone, manager, cost, and weight."

The values in the first column, toy_id, in the store_inventory table need to be changed to:

toy_id
1
2
3
4
5
6
7
8
9
10

Should be mc_prof NOT mcprof

Last line of query should be changed from "ON mc.contact_id = p.prof_id;" to "ON mc.prof_id = p.prof_id;"

Changed "INNER" to "NATURAL" in first and third answers.

Changed "INNER" to "NATURAL" in first and third answers.

The annotation for the table states, "The interests column is empty after we run the command." This is incorrect. The interests column still has the value "fourth" in it.

The annotation on the bottom of the page was changed to read "The interests column contains only the last interest at this point." Additionally, in the interests column on the table, the scratchy line was edited to only cross out the second and third part, leaving the fourth part unscratched.

The annotation for the table states, "The interests column is empty after we run the command." This is incorrect. The interests column still has the value "fourth" in it.

The annotation on the bottom of the page was changed to read "The interests column contains only the last interest at this point." Additionally, in the interests column on the table, the scratchy line was edited to only cross out the second and third part, leaving the fourth part unscratched.

The table in the answer for the 'Sharpen your pencil' exercise (on page 378) indicates that the information in the orginal 'interests' column will be gone. However, after the value from 'interests' has been pared down to just the fourth item, it is assigned to the 'interest4' column, but isn't modified afterword (like the SUBSTR function did for the previous three interests). The end result would have 'fourth' as the value in the 'interests' column once the query has been run.

Note from the Author or Editor:
pg 378 shoud still show the word "fourth" in the interests column, bottom of the page. Change annotation, pointing to it:

The interests column still contains "fourth" after we run the command.

contact_interest.interest_id should link up with interests.interest_id
and
contact_seeking.seeking_id should link to seeking_id

To correct this, I edited the arrow paths:
The right arrow that points at contact_interest.contact_id was shifted down to contact_interest.interest_id.
The right arrow that points at contact_seeking.contact_id was shifted down to contact_seeking.seeking_id.

contact_interest.interest_id should link up with interests.interest_id
and
contact_seeking.seeking_id should link to seeking_id

To correct this, I edited the arrow paths:
The right arrow that points at contact_interest.contact_id was shifted down to contact_interest.interest_id.
The right arrow that points at contact_seeking.contact_id was shifted down to contact_seeking.seeking_id.

The words 'FROM' and 'HAVING' are used in the solution, but are not among the list of words for the puzzle.

Query says "SELECT jc.salary ..... WHERE email = ''". It should be "SELECT jc.salary ..... WHERE mc.email = '........'". "mc." is missing.

Note from the Author or Editor:
Change third line of query to read

WHERE mc.email = 'andy@weatherorama.com';

Two of the annotations for the top query diagram should be changed to indicate that the subquery (inner query) is processed first. The top-left annotation should be changed from "The RDBMS processes this part first." to "The RDBMS processes this part second." And the bottom-right annotation should be changed from "This is processed second." to "This is processed first."

In the sentence "If two contact_id are true, a condition is met.", "true" should be "equal" or "identical"?

Note from the Author or Editor:
Change the second entry in the right column of What's My Purpose from:

If two contact_id are true, a condition is met.

to:

If two contact_id match, a condition is met.

The following statement seems incorrect to me:

" Think of the
results of the UNION like they?re the values from
each SELECT that ?overlap.?"

The UNION statement doesn't just provide the "overlap" but combines all the results (with duplicates removed).

Note from the Author or Editor:
Change the "Think of the..." sentence to

Think of the results of the UNION like they're the values from each individual SELECT combined with duplicates removed.

1) The job_listings.job_id field is listed as both a primary and foreign key even though it isn't being used as a foreign key.

2) The field interests.interest_id is mapped to contact_interest.contact_id instead of contact_interest.interest_id.

3) The field seeking.seeking_id is mapped to contact_seeking.contact_id instead of contact_seeking.seeking_id.

Note from the Author or Editor:
As above.

The second annotation on this page should be changed from "And here's the status_id that corresponds to 'married'." to "And here's the status_id that corresponds to 'single'."

Also, the value in the result table for status_id should be 3, not 4.

The AS keyword is in the wrong place. AS in the statement was moved after pb_quarters, to read:

CREATE VIEW pb_quarters AS
SELECT...

The AS keyword is in the wrong place. AS in the statement was moved after pb_quarters, to read:

CREATE VIEW pb_quarters AS
SELECT...

The dates in the INSERT command line should be quoted because the table CREATE command (on page 472) lists those values as CHAR(4).

Note from the Author or Editor:
Change the CREATE TABLE on page 472 from
coin_year CHAR(4)
to
coin_year INT

"...all NOT NULL rows of the base table..." should be
"...all NOT NULL columns of the base table..."

Solution says "REVOKE GRANT INSERT(location).....". Should be "REVOKE INSERT(location)......". GRANT is extra. Wrong syntax, i am a little bit confused.

Note from the Author or Editor:
get rid of the word "GRANT" in solution to last exercise.

Initial Grant statement is:
GRANT SELECT, INSERT, DELETE ON locations FROM elsie;

Answer in book: REVOKE INSERT, UPDATE, DELETE ON locations FROM elsie;

Answer should be: REVOKE INSERT, DELETE ON locations FROM elsie;

UPDATE permission was not given in the original GRANT statement.

Note from the Author or Editor:

Change first answer on page 508 to:
REVOKE INSERT, DELETE ON locations FROM elsie;

Used the '+' sign for subtraction and division. Should be '-' and '/', respectively.

'Not less than' operator should be '!<'
'Less than or equal to' operator should be '<='

the arrows from the annotation "these are only wildcards..." should point to the rows for _ and %.

Note from the Author or Editor:
As stated, the annotation arrows need to be redrawn:

the arrows from the annotation "these are only wildcards..." should point to the rows for _ and %.

The "what does it do?" text for the Log(x) and Log(x,y) functions should be changed to "Returns the natural logarithm of x, or with two parameters returns the log of y for base x."

" lose($conn);" should be
"mysql_close($conn);"