Errata

Replace 'mysql -u lrngsql -p;' with 'mysql -u lrngsql -p' (without the semicolon)

Reason:

lrngsql is a user with password, as specified in step 5:

> mysql -u lrngsql -p;
ERROR 1045 (28000): Access denied for user 'lrngsql'@'localhost' (using password: YES)

> mysql -u lrngsql -p
Enter password: ***
Welcome to the MySQL monitor. Commands end with ; or \g.

In the file "LearningSQLExample.sql",the statement "alter table employee add constraint fk_e_emp_id foreign key (superior_emp_id) references employee (emp_id);" is redundant.If contains it,when executing sql script we will meet error tips like follow:

sql> drop table emp_tmp
[2018-10-30 17:28:10] completed in 4 ms
sql> alter table employee add constraint fk_e_emp_id
foreign key (superior_emp_id) references employee (emp_id)
[2018-10-30 17:28:10] [23000][1022] Can't write; duplicate key in table '#sql-f3c_11'

Please remove that statement from the file "LearningSQLExample.sql",thanks.

The following SELECT statement references the "individual" table:

SELECT t. txn_id, t.txn_type_cd, t.txn_date, t.amount
FROM individual i ...

"individual i" should probably be replaced by "customer c" in order to match the database schema provided in Figure 1-3.

Also, the following check of that same statement:

"... AND p.name = 'checking account';"

should probably be switched to

"... AND p.name = 'Checking';

in order to match Figure 1-3.

The SELECT example:

SELECT cust_id, fname
FROM individual
WHERE lname = ' Smith';

should change "individual" to "customer" in order to match Fgiure 1-3.

At the end of the query there is a ";" missing.

The provided link was not working as of 11/24/09. The results of accessing http://dev.mysql.com/downloads/mysql/6.0.html produced:
"The page you are looking for has been removed."

Two problems in Table 2-1:

Before step 5, it is necessary to create the user 'lrngsql'@'loclahost' before granting them privileges - at least on my version of mysql version 8.

Step 7 should not have a semicolon at the end of the line because it is a shell command, not a mysql command.

2. Type source c:\temp\LearningSQLExample.sql;
SQL uses a singel \ as an escape character the correct method is
c:\\temp\LearningSQLExample.sql;

When I try to input the sample language base used for the book. I get the following error:

ERROR 1005 (HY000): Can't create table 'bank.#sql-544_3' (errno: 121)

Google suggest this has to do with foreign keys. I've tried loading it under Windows 7 x64 using MySQL 5.1.58

In MySQL v 5.6.7.2, an attempt to enter in the statement
ALTER TABLE person MODIFY person_id smallint unsigned auto_increment;
is met wtih the following error, a result of the constraint defined at the top of page 30:
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'

The command shown at the bottom of pg. 31:

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

generates the following error in MySQL 5.6:

ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'

Presumably, this error would not have occurred if I had not already created the favorite_foods table as instructed in the text.

Changing the "person_id" to "auto_increment" cannot be done like this:
---------------------------------------------------------
mysql> alter table person modify person_id smallint unsigned auto_increment
-> ;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'
---------------------------------------------------------
This is what worked for me:
---------------------------------------------------------
mysql> lock tables
-> favorite_food write,
-> person write;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table favorite_food
-> drop foreign key fk_fav_food_person_id,
-> modify person_id smallint unsigned;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table person modify person_id smallint unsigned auto_increment;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table favorite_food
-> add constraint fk_fav_food_person_id foreign key (person_id) references person (person_id);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> unlock tables;
Query OK, 0 rows affected (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 | |
| street | 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.00 sec)

---------------------------------------------------------
Reason:
"The type and definition of foreign key field and reference must be equal. This
means your foreign key disallows changing the type of your field.
Corresponding columns in the foreign key and the referenced key must have similar internal
data types inside InnoDB so that they can be compared without a type conversion. The size and
sign of integer types must be the same. The length of string types need not be the same.
For nonbinary (character) string columns, the character set and collation must be the same."

"you will need to create a row in parent before you can enter data into favorite_food."

the parent should be person.

The word "Transaction" is a reserved word so it should not be used in the table name.

The author suggests that the following two statements are logically equivalent.

= 'Teller' OR start_date < '2003-01-01')

1) on page 57:
NOT (title = 'Teller' OR start_date < '2003-01-01')
2) on page 58:
(title != 'Teller' OR start_date > '2003-01-01')

Actually, the second option needs to read "AND" rather than "OR".
(title != 'Teller' AND start_date > '2003-01-01')

In the Range Conditions example, the start dates shown in the example do not match the dataset provided with the text. The query asks you to filter for all employees hired in 2005 and 2006, but in the dataset no employee has a start date of after 2003. In the printed example 11 employees are returned.

The author claims that SQL Server allows for regular expressions using the LIKE operator. This is not true; there is only relatively weak pattern matching capabilities with the LIKE operator using SQL Server. See, e.g., http://msdn.microsoft.com/en-us/library/ms179859.aspx.

The query result at the top of page 82 says
2 rows in set

but the text on page 83 says there are 3 departments, and the query results spanning pages 82 & 83 clearly confirm that.

The query result at the top of page 82 should therefore say
3 rows in set.

Simple mistake, but caused me some confusion initially.

example query :
mysql> SELECT emp_id, fname, lname, start_date -> FROM employee
-> WHERE start_date < '2007-01-01';

Does not return the same amount of results as in the PDF. The database supplied with this book is different from whichever database was used to run the result.

Also query:
mysql> SELECT emp_id, fname, lname, start_date -> FROM employee
-> WHERE start_date < '2007-01-01'
-> AND start_date >= '2005-01-01';

returns zero results because the database supplied with this book has zero employees that were hired from 2005 to 2006.

The first paragraph should read
"This query generates total balances for each active product, but then the filter condition in the having clause excludes all products for which the minimum balance is greater than or equal to $1,000 or the maximum balance is less than or equal to $10,000."

there are 3 customers, actually is 13

The start and end dates listed in the subquery are too low to produce interesting results (no "workers" or "mentors"). As the start dates in the employee table begin in 2004.

I am enjoying the book!

Thanks

It's ProcessList, not Process_List

According to description of the notation, there should be a 1-to-many relationship between customer and individual, and customer and business. The line from customer to individual uses the "crows foot" with a zero, indicating that for a particular customer there can be zero, one, or "many" individuals. (And same for business)

However, according to the schema, the Foreign Key (FK) for individual is also the primary key meaning that there can only be one row with that value. And there must be at least one because the primary key can't be null. So the ERD should show this as a 1-to-1 relationship between customer and individual (or customer and business) rather than 1-to-many.

Incomplete answer.

On Page 60 Exercise 3-4, there is a "-> ORDER BY <4>, <5>;". However, the answer does not have the Order By completed.

So what is the complete answer?

The answer for Example 3-4 on page 288 doesn't match the example shown on page 60. The example on page 60 has an ORDER BY clause and two "arguments" (i.e. <4> and <5>) that are missing from the answer page.