Errata

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.

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'

> 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'

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.

selectadd_months
should be
select add_months

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

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.

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;.