The errata list is a list of errors and their corrections that were found after the product was released.
The following errata were submitted by our customers and have not yet been approved or disproved by the author or editor. They solely represent the opinion of the customer.
Version |
Location |
Description |
Submitted by |
Date submitted |
Printed |
Page page 3
2nd example, result table |
The table showing the result of the query shows 'hiredate' with the wrong years printed. The 'hiredate' currently reads, "17-DEC-1980" and "12-JAN-1983"; it should read "17-DEC-2005" and "12-JAN-2008".
|
Anonymous |
Jan 30, 2024 |
Printed |
Page 11
5th paragraph |
Missing the word "to" in this sentence:
When specifying a numeric constant in the ORDER BY
clause, you are requesting that the sort be done according {to} the column in that ordinal position in the SELECT list.
|
Chris Holbrook |
Mar 31, 2022 |
PDF, O'Reilly learning platform |
Page 54
Both solution queries |
I am working my way through SQL Cookbook, 2nd Edition on oreilly.com, and I believe I’ve found an error in the solution for section "3.9 Performing Joins When Using Aggregates."
It looks like it would be page 54 in the PDF/printed version.
Specifically, the solutions:
1 select deptno,
2 sum(distinct sal) as total_sal,
3 sum(bonus) as total_bonus
4 from (
5 select e.empno,
6 e.ename,
7 e.sal,
8 e.deptno,
9 e.sal*case when eb.type = 1 then .1
10 when eb.type = 2 then .2
11 else .3
12 end as bonus
13 from emp e, emp_bonus eb
14 where e.empno = eb.empno
15 and e.deptno = 10
16 ) x
17 group by deptno
And
1 select distinct deptno,total_sal,total_bonus
2 from (
3 select e.empno,
4 e.ename,
5 sum(distinct e.sal) over
6 (partition by e.deptno) as total_sal,
7 e.deptno,
8 sum(e.sal*case when eb.type = 1 then .1
9 when eb.type = 2 then .2
10 else .3 end) over
11 (partition by deptno) as total_bonus
12 from emp e, emp_bonus eb
13 where e.empno = eb.empno
14 and e.deptno = 10
15 ) x
Both work for the sample table provided in the book, but they both fail if two employees in department 10 have the same salary.
I verified this by increasing employee 7934’s salary to $5000 to match employee 7839’s salary:
update emp e set sal = 5000.00 where empno = 7934;
which should have resulted in a net gain of $5000 - $1300 = $3700, but instead the department total decreased from $8750 to $7450, as I expected, since employee 7934’s $1300 salary isn’t included anymore, but the $5000 only gets counted once, even though two employees have that salary.
The correct way to compute this is to sum the bonus in the inner query and then sum the salary in the outer:
SELECT deptno,
SUM(sal) AS total_sal,
SUM(bonus) AS total_bonus
FROM (SELECT e.empno,
e.ename,
e.sal,
e.deptno,
SUM(e.sal * CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) AS bonus
FROM emp e,
emp_bonus eb
WHERE e.empno = eb.empno
AND e.deptno = 10
GROUP BY e.empno,
e.ename,
e.sal,
e.deptno) x
GROUP BY deptno
For the other query, I tried to figure out a way to write it without a subquery, but best I can do is pretty much the same thing as above, except using DISTINCT and PARTITION BY. And since the inner records are filtered by deptno = 10, the outer PARTITION BY is kind of useless.
SELECT DISTINCT deptno,
SUM(sal) OVER(PARTITION BY deptno) AS total_sal,
SUM(emp_bonus) OVER(PARTITION BY deptno) AS total_bonus
FROM (SELECT DISTINCT e.empno,
e.ename,
e.sal,
e.deptno,
SUM(e.sal * CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) OVER(PARTITION BY e.empno) AS emp_bonus
FROM emp e,
emp_bonus eb
WHERE e.empno = eb.empno
AND e.deptno = 10) x
Or this, which really isn’t any better:
SELECT DISTINCT e.deptno,
SUM(e.sal) OVER(PARTITION BY e.deptno) AS total_sal,
SUM(e.sal * x.emp_bonus_mult) OVER(PARTITION BY e.deptno) AS total_bonus
FROM emp e,
(SELECT DISTINCT eb.empno,
SUM(CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) OVER(PARTITION BY eb.empno) AS emp_bonus_mult
FROM emp_bonus eb) x
WHERE e.empno = x.empno
AND e.deptno = 10
|
James D Burnell |
Feb 14, 2024 |
Printed |
Page 55
DB2, Oracle, and SQL Server example at bottom |
The line:
sum(distinct e.sal) over (partition by e.deptno) as total_sal
throws an error using DB2 on Cloud which says there is an invalid use of distinct. Following the link to more information on IBM Cloud indicates the likely cause is "the function has been resolved as a scalar function" since the other explanations seem not to apply.
Is this a difference in how DB2 on Cloud works with windowing or does DB2 just not work this way?
|
James |
Apr 19, 2023 |
Printed |
Page 68
Discussion, 3rd paragraph |
It reads
[...] don't insert into every column, you are will create a row where [...]
"are" in the above sentence should be removed.
|
Ralph Schmieder |
Jan 04, 2022 |
PDF |
Page 82,
Solution section of chapter 4.11 |
Code as printed does not run on Microsoft SQL server.
Wrong code:
1 merge into emp_commission ec
2 using (select * from emp) emp
3 on (ec.empno=emp.empno)
4 when matched then
5 update set ec.comm = 1000
6 delete where (sal < 2000)
7 when not matched then
8 insert (ec.empno,ec.ename,ec.deptno,ec.comm)
9 values (emp.empno,emp.ename,emp.deptno,emp.comm)
Corrected code
MERGE INTO emp_commission ec
using (SELECT * FROM emp) emp
ON (ec.empno= emp.empno)
when matched and sal < 2000 then
delete
WHEN matched THEN
UPDATE SET ec.comm = 1000
when not matched then
insert (empno, ename, deptno, comm)
values (emp.empno, emp.ename, emp.deptno, emp.comm);
|
Tye Lokka |
Sep 20, 2022 |
Printed |
Page 112
Solution Section |
There is no MySQL recipe for 6.5 Separating numeric and character data
|
Evan |
Nov 12, 2021 |
Other Digital Version |
118
last line of code on the bottom part of the page |
The recipe for the MySQL solution as printed is:
SELECT data
from V
WHERE data regexp '[^0-9a-zA-Z]' = 0;
This returns an empty set, and does not match the desired results.
However, the following code does return the desired result.
SELECT data
FROM V
WHERE data regexp '[:punct:]' = 0;
|
Regis O'Connor |
Oct 08, 2021 |
Printed |
Page 121
Solution |
The solution does not print out the second period on the initial
|
Anonymous |
Dec 07, 2022 |
Printed, PDF |
Page 126
First paragraph |
1 select ename
2 from emp
3 order by substr(ename,length(ename)-1,)
line 3 requires a 2 before last paren to get the order by last 2 of ename string
replace line 3 with --->>> order by substr(ename,length(ename)-1,2)
|
Anonymous |
Nov 04, 2021 |
Printed |
Page 133
MySQL Solution Section |
I get an error when running the query as it is written in the book. Research online resulted in successfully running the query by removing the comma after separator. Also, group_concat uses a comma by default as the separator so for this recipe you could leave it out
|
Evan |
Nov 14, 2021 |
Printed |
Page 207
MySQL Solution Section |
Passing the earliest of the two dates in the function (as the book instructs) results in a negative value. several errors in this book
|
Evan |
Nov 18, 2021 |
Printed |
Page 216
MySQL Solution Section |
I get an error when running the solution for MySQL recipe 8.5. After reviewing the error message and the MySQL reference manual, the DATEDIFF function does not accept an interval parameter as the book states it does.
|
Evan |
Nov 19, 2021 |
Printed |
Page 269
MySQL Solution |
Recipe states to use datepart function but there is not date part function for MySQL
|
Evan |
Nov 21, 2021 |
Printed |
Page 269
MySQL Solution |
Recipe states to use the getdate function but there is no getdate function in MySQL
|
Evan |
Nov 21, 2021 |
Printed |
Page 328
10.5 Recipe |
No MySQL solution or discussion provided for recipe 10.5
|
Evan |
Nov 23, 2021 |
Printed |
Page 352
Solution Section |
Recipe 11.11 does not have a solution section for MySQL
|
Evan |
Nov 24, 2021 |
Printed |
Page 358
Solution Section |
For recipe 11.12, there is a "DB2, MySQL, and SQL Server" solution but there is also a separate section saying that there isn't a MySQL solution (page 359)
|
Evan |
Nov 24, 2021 |
Printed |
Page 386
2nd paragraph |
The book states that "The CEIL function will return the smallest whole number greater than the value passed to it". The ceil function however will return the smallest integer that is greater than or equal to the number passed to the function. The equal portion of that statement is especially relevant for this solution,
|
Evan |
Nov 26, 2021 |
Printed |
Page 430
MySQL Solution |
On page 420 (recipe 12.14 solution section) the book states "As of the time of this writing, MySQL doesn't support either CUBE or GROUPING." Then on page 430 (recipe 12.20 solution section) GROUPING is used in the MySQL solution. Many errors in this book I paid for.
|
Evan |
Nov 28, 2021 |