Errata

SQL Cookbook

Errata for SQL Cookbook, Second Edition

Submit your own errata for this product.

The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color key: Serious technical mistake Minor technical mistake Language or formatting error Typo Question Note Update

Version Location Description Submitted By Date submitted Date corrected
Page 17,18
1st paragraph

Item 2.3

The Desired output rows are not ordered by the last 2 characters in JOB, as was the stated goal.

In the Discussion of item 2.3 it states that you should Find the end of the string using LEN and subtract two. This isn’t correct. You should subtract 1. The sql code shown DOES just that.

Note from the Author or Editor:
The text on page 18 of the printed version which reads '...find the end of the string (which is the length of the string) and subtract two' should read '...and subtract one;.

Anonymous  Nov 29, 2020  Sep 24, 2021
Page 111
Solution section

In this part of the formula: translate(ename,'aaaaa','AEIOU') the second and third parameters should by swapped for SQL Server. Like this: translate(ename,'aeiou','aaaaa')

Note from the Author or Editor:
The 'from string' comes first and 'to string' comes second for SQL Server, Oracle and PostgreSQL. Line 2 should be 'replace (translate(ename,'aaaaa', 'AEIOU'), 'a', '') as stripped1, '

Zdeněk  Mar 10, 2021  Sep 24, 2021
Page 146
The last paragraph

Page 146, the last paragraph.
--------------------------------------
You’ll notice that each case statement is actually two case statements.
-----------------------------------
There are no CASE statements in the PostgreSQL solution. Also, there is no CNT variable in the solution. It seems like this discussion is from the 1st edition text.

Note from the Author or Editor:
It looks correct that the discussion refers to the 1st edition solution. For the 2nd edition solution, the STRING_AGG function introduced since the first edition performs the task of ordering a concatenated string via its ORDER BY clause. The subquery section is designed to feed the characters one at a time to allow the ORDER BY clause to function correctly.

Serguey  Dec 22, 2020  Sep 24, 2021
Page 156
Section DB2 in Discussion.

Page 156, Section DB2 in Discussion.
-----------------------------------
The syntax is slightly different between these two DBMSs, but the technique is the
same. We will use the solution for DB2 for the discussion.
--------------------------------------------
What "these two DBMSs"?
It appears that this has been retained from the 1st edition in which indeed the DB2 and SQL Server DBMSs are discussed.

Note from the Author or Editor:
Delete sentences 'The syntax is slightly different between these two DBMSs...We will use the solution for DB2 for the discussion.'
Replace the next sentence with 'For DB2 the strings are walked and the results are represented by inline view X'

Serguey  Dec 22, 2020  Sep 24, 2021
Page 169
CODE LINE 2 IN SOLUTION SECTION

CURRENTLY PRINTED AS

2 replace(translate(ename,'aaaaa','AEIOU'),'a','')

CORRECTION (POSTGRESQL)

2 replace(translate(ename,'AEIOU','aaaaa'),'a','')

Note from the Author or Editor:
The order of arguments in the TRANSLATE function is reversed for Oracle, PostgreSQL and SQL Server - the 'from' string should be first, then the 'to' string. Therefore, replace line 2 of the code with ' replace(translate(ename,'AEIOU','aaaaa'),'a','')' and note that DB2 is the exception where the 'to' string comes first.

PARDEEP KUMAR  Jun 19, 2021  Sep 24, 2021
Page 198
Solution section for SQL Server

I've discovered an issue with the final select statement in the Solution for SQL Server. But I think other RDBMS have the same type of mistake.The Solution as written in the book doesn't follow the explanation in the Discussion section on pages 200, 201.

I think the final Select should look like this:

SELECT ABS(SAL - median) / mad AS dev, SAL, ENAME, JOB
FROM MAD
JOIN EMP ON 1 = 1
JOIN median ON 1 = 1
ORDER BY SAL

to show the value of deviation. The change is in joining with the median CTE and subtracting the median value from SAL in the first column.

Note from the Author or Editor:
Cofirmed.
Replace 'ABS(sal - MAD)' with 'ABS(sal - median)' in each solution.

Zdeněk Haník  Mar 27, 2021  Sep 24, 2021
Page 202
Solution for Benfords Law

The solution for Benfords law refers to value field from T1. That field doesn't exist, only ID.

SQL should be referring to ID rather than value.


with
FirstDigits (FirstDigit)
as
(select left(cast(SAL as CHAR),1) as FirstDigit
from EMP),
TotalCount (Total)
as
(select count(*)
from EMP),
ExpectedBenford (Digit,Expected)
as
(select ID,(log10(ID + 1) - log10(ID)) as expected
from T10
where ID < 10)
select count(FirstDigit),Digit
,coalesce(count(*)/Total,0) as ActualProportion,Expected
From FirstDigits
Join TotalCount
Right Join ExpectedBenford
on FirstDigits.FirstDigit=ExpectedBenford.Digit
group by Digit
order by Digit

Note from the Author or Editor:
Confirmed - replace 'value' with 'ID'

Lee Hawthorn  Jul 20, 2021  Sep 24, 2021
Page 216
Page 216, Section SQL Server,

Page 216, Section SQL Server, this sentence:
----------------------------------------------------
Use the function DATEDIFF to find the difference between two dates, and use the DATEPART argument to specify months and years as the time units returned:
----------------------------------------------
What DATEPART argument? I don't see any such arguments. Also, as far as I know, DATEPART is a function in SQL Server.

Note from the Author or Editor:
Microsoft documentation refers to the argument which specifies the units of the datediff function e.g. days, weeks, months as datepart, albeit in lower case. See https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15#arguments

Change caps on DATEPART here to lower case.

Serguey  Jan 03, 2021  Sep 24, 2021
Page 240
The description of DB2

The description of DB2

> Use the recursive WITH clause to return each day in February. Use the aggregate function MAX to determine the last day in February:

doesn't look correct. Because you both WITH clause and aggregate function MAX are not used here.

Note from the Author or Editor:
Discussion with Ryoko established this error actually applied to the SQL Server solution on page 241 - the wording for the SQL Server solution is incorrect. Change wording to 'This solution uses SQL Server's DAY function to attempt the date the 29th of February in the current year. If the 29th of February is not valid, the DAY function will return a NULL. The COALESCE function will return the value from the DAY function if it is valid, or the integer 28 if it is not.'

Ryoko  Jul 08, 2021  Sep 24, 2021
Page 247
code snippet Oracle

selectadd_months
should be
select add_months

Note from the Author or Editor:
Confirm - should be 'select add_months'

Ryoko  Jul 08, 2021  Sep 24, 2021
Page 254
Page 254, section SQL Server

Page 254, section SQL Server, these two sentences:
----------------------------------------------------
Add one month to the current date, then subtract from it the value returned by DAY(GETDATE()) to get the last day of the current month.

Add one month to the current date, and then subtract from it the value returned by DAY(DATEADD(MONTH,1,GETDATE())) to get the last day of the current month.
----------------------------------------------------
Which one of these two methods is actually used? From the DATEADD mentioned previously, looks like the second one.

Note from the Author or Editor:
In the sentence "Add one month to the current date, then subtract from it the value returned by DAY(GETDATE()) to get the last day of the current month."
replace 'last' with 'first'.

Serguey  Jan 08, 2021  Sep 24, 2021
Page 256
last line of PostgreSQL code

14 where cast(extract(dow from dy) as integer) = 6
should be below?
14 where cast(extract(dow from dy) as integer) = 5

Because Friday in PostgreSQL dow is 5.

Note from the Author or Editor:
Agree - change 6 to 5 on page 256 per description.

Ryoko  Jul 08, 2021  Sep 24, 2021
Page 259
last line of PostgreSQL description

> The final step is to use the TO_CHAR function to keep only the Fridays.

TO_CHAR function is not used in the code on page 256.

Note from the Author or Editor:
Delete 'The final step is to use TO_CHAR to keep only the Fridays'

Ryoko  Jul 08, 2021  Sep 24, 2021
Page 275
last sentence of 1st paragraph of the Oracle description

> Instead, you can use a pivot table, such as T500 in the MySQL solution.

T500 is not used in the MySQL solution.

Note from the Author or Editor:
Change to 'Instead you can use a recursive CTE, as used in the solution for the other platforms.'

Ryoko  Jul 08, 2021  Sep 24, 2021
Page 290
PostgreSQL code snippet

I got an error with this code, because the first argument of substr() must be a string.
In order to avoid the error, I think we need to type cast yrq to a string in advance.
So, we should use

select substr(cast(yrq as varchar),1,4) yr, mod(yrq,10)*3 mth

instead of

select substr(yrq,1,4) yr, mod(yrq,10)*3 mth


The same can be said for the code on page 291.

Note from the Author or Editor:
For both the PostgreSQL solution on page 290 and the MySQL solution on page 291 replace 'yrq' with 'cast(yrq as varchar)'
e.g.
select substr(cast(yrq as varchar),1,4) yr, mod(yrq,10)*3 mth

instead of

select substr(yrq,1,4) yr, mod(yrq,10)*3 mth

PostgreSQL used to automatically 'cast' to a string if you applied a string function, however, they removed that capability because sometimes it caused errors. Hence this recipe used to work as written but no longer does. MySQL still does this 'automatic cast' but it makes sense to add the cast for portability and standardisation while we're here.

Ryoko  Aug 19, 2021  Sep 24, 2021
Page 376
Query (inline view)

In the inline view select statement rn_deptno is partitioning by job should be by deptno

row_number()over(partition by job order by ename) rn_deptno (incorrect)

row_number()over(partition by deptno order by ename) rn_deptno

Note from the Author or Editor:
Confirmed.
Change 'row_number()over(partition by job order by ename) rn_deptno'
to 'row_number()over(partition by deptno order by ename) rn_deptno'

on in code block on page 376.

Anonymous  Apr 17, 2021  Sep 24, 2021
Page 381
four_rows cte

In the last paragraph its mentioned we need to use RECURSIVE keyword for ORACLE but the query throws "missing_keyword" error. I am using Oracle version 18.4. When i wrote the query differently without the recursive keyword it ran fine (also need to use dual for the first part)

WITH four_rows (id) AS
(
SELECT 1
FROM DUAL
UNION ALL
SELECT id+1 FROM four_rows WHERE id < 5
)
SELECT * FROM four_rows;

Thanks

Note from the Author or Editor:
As Oracle doesn't accept 'fromless' select statements, we need to add 'FROM DUAL' between 'SELECT 1' and 'UNION ALL' in the CTE.

Ahsan  Apr 18, 2021  Sep 24, 2021
Page 440
paragraph DB2, PostgreSQL, and SQL Server

CHAPTER 13 Hierarchical Queries -> 13.2 Expressing a Child-Parent-Grandparent Relationship
This section says that in PostgreSQL, the recursive query syntax is the same as in DB2 and SQL server, but this is not true. In a recursive query for postgresql, you need to add the keyword "RECURSIVE", just like for MySQL

Note from the Author or Editor:
Confirmed.
Delete 'PostgreSQL' from subheader 'DB2, PostgreSQL and SQL Server' on page 440.
Add 'and PostgreSQL' to subheader 'MySQL' on page 441.

snaggletooth  Mar 11, 2021  Sep 24, 2021