Errata

SQL Cookbook

Errata for SQL Cookbook

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.

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.

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

Version Location Description Submitted by Date submitted
Mobi Page xviii
Error Location :147 in mobi file

"setoriented problem-solving for" should be "set oriented problem-solving for"

Anonymous  Jul 08, 2014 
Printed, PDF, ePub Page 8.3
3rd paragraph

8.3. Determining the Number of Business Days Between Two Dates Nowhere the pivot table T500 exists. If the the table is missing there is no way to understand the structure. Please ask the author to include the T500 pivot table

Mahmudul Mithun  Dec 08, 2017 
Printed Page 14+
Page footer

For ease of use, I had my copy's binding cut and spiral bound. I was surprised to find that Chapter 2 started on an even numbered page rather an odd numbered page as is standard.

Alex Campbell  Nov 11, 2019 
Printed Page 30
Section 3.2 under "Solution"

At the above location the author introduces a completely new notation .... e.ename, d.loc, e.deptno and d.deptno and proceeds to use it continually thereon. What does it mean to put a letter and a dot in front of an established column name? I find no explanation of this peculiar practice anywhere in the book. Could you please explain this practice? It is very frustrating to this beginning SQL user.

Stan Haavik  Oct 18, 2017 
Printed Page 43-46
pages are missing

Pages 43-46 are missing. I'm not sure if it is just a page numbering problem, or if material is actually missing. The discussion is about "determining whether two tables have the same data", and it sort of flows from page 42 to page 47, but I think there is something missing as I can't follow the author's logic on this topic. I did notice that my version of the book does have many of the corrections listed the errata section, but that the page numbers are different for where the topic is than what is given in the errata page number. I'm really confused about the corrections being there, because my book say First Edition December 2005 on the first page, so I would think there would be no corrections in that version. I guess the corrections were made without changing the edition number. If there is a newer version of the book without the missing pages, how do I get a copy?

Anonymous  Jan 09, 2013 
Printed Page 50-55
MySQL Solution

There are logical errors in the query that makes the solution wont scale. These are the errors we might get: 1. If we add a new employee to dept 10 that have the same salary as existing dept 10 employee, the result of total salary will be wrong. 2. If we add a new employee to dept 10 that haven't received any bonus, the result of total salary will be wrong.

Aldian  Nov 29, 2017 
Printed Page 51
1st code example

Code examples on page 51 and subsequent that refer to the table emp_bonus will not work because that table does not exist. In order for the examples to work, that table must be created and populated but on pages xxiii and xxiv of the preface the author specifies that only two tables are needed for the examples to work and only the code to create and populate these two tables are included in the download file.

Doug Jacobsen  Dec 29, 2015 
Printed Page 53
script of two solutions of page 53

On page 53, it provides two solutions to section 3.9 Performing Joins When Using Aggregates. However, I don't think either the solution provided is 'good'. The first solution, there is a flaw which is, in case when there are two employees having earning the same salary within the same department then the using Sum(distinct salary) as total_sal won't produce the correct result given the final query is grouped by deptno. I think it's fair to assume that it's possible that two employees earning the same salary from the same department in real life. Though with the exam data the solution gives the desired result I think there is a serious flaw since if people take this query in a real life exam it could produce very misleading results. The way I suggest to fix this is as follow:( please note I created the tables with slightly different column names but it's very obvious to see their corresponding to the columns used in the book) SELECT Departmentid, SUM(Salary), as 'salary sum' SUM(Bonus) AS 'bonus sum' FROM ( SELECT DepartmentId, Name, sum(Salary) / Count(Name) as 'Salary', -- This will give a salary per employee even if it's duplicated, the sum is done in the outer query. sum(Salary * CASE WHEN [Type] = 1 THEN 0.1 WHEN [Type] = 2 THEN 0.2 ELSE 0.3 END) AS 'Bonus' FROM [dbo].[Employee] [emp] INNER JOIN [dbo].[EmployeeBonus][emb] ON [emp].[EmployeeId] = [emb].[EmployeeId] WHERE [emp].[DepartmentId] = 10 group by name,departmentid -- so here it's important to group by name as well as departmentid ) x GROUP BY Departmentid The second solution doesn't work with newer version of sql server and i don't know whether it worked for earlier version either. SQL Server doesn't allow DISTINCT used with OVER clause. Maybe there is a version issue and hence it's less serious than the first solution.

Tiantang Sun  Sep 24, 2014 
Mobi Page 112

Section 6.7 describes "Extracting Initials from a Name", but the DB2 and Oracle/PostgreSQL versions only work under specific conditions with a subset of names. 1. These functions only work with names in proper case. If names are entered in all uppercase letters (which is common enough that the following example [6.8] uses such a data set), the function fails. Example: "UNCLE VAUGHN" becomes "U.N.C.L.E.V.A.U.G.H.N." 2. These functions fail for names like "McCoy", "MacDonald", and "O'Brien" that contain more than one capital letter in the last name (or, less commonly, first name). Example: "Leonard McCoy" becomes "L.MCC." and "Jean-Claude O'Brien" becomes "J-C.O'B." 3. These also fail on names with special characters (René Descartes, Michael Peña, Zoë Saldana, etc.), though I think this may have been acknowledged as a limitation elsewhere for some of these hacks. These limitations should at least be noted, as they are all likely to be encountered in a data set of reasonable size. Note: The MySQL version does not appear to have either of these issues.

Anonymous  Feb 20, 2018 
Printed Page 113
last paragraph (edition 05/09)

Last paragraph says: "... neither TRANSLATE nor CONCAT_WS is supported in SQL Server." However there is a solution for the problem in that chapter similar to the MySQL one as follows: select case when cnt = 2 then (select SUBSTRING(name,1,1)+'.' +' '+ SUBSTRING(name,charindex(' ',name)+1,1)+'.' +' '+ RIGHT(SUBSTRING(REVERSE(name),charindex(' ',name),1),1)+'.' ) else (select SUBSTRING(name,1,1)+'.' +' '+ SUBSTRING(name,charindex(' ',name)+1,1)+'.' ) end as initials from ( select name, datalength(name) - datalength(replace(name,' ','')) as cnt from ( select replace('Stewie Griffin','.','') as name from t1 ) y ) x

Note from the Author or Editor:
Looks good, but I don't have time to test the accuracy of the proposed solution. Let's save this for a later time.

Anonymous  Apr 30, 2010 
PDF, Other Digital Version Page 133
Oracle solution, 6.11

The query has one extra instr(emps,',',1,iter.pos), there isn't a comma separating the arguments and the parameter would cause an incorrect result. 1 select empno,ename,sal,deptno 2 from emp 3 where empno in ( 4 select to_number( 5 rtrim( 6 substr(emps, 7 instr(emps,',',1,iter.pos)+1, 8 instr(emps,',',1,iter.pos+1) 9 instr(emps,',',1,iter.pos)),',')) emps 10 from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv, 11 (select rownum pos from emp) iter 12 where iter.pos <= ((length(csv.emps)- 13 length(replace(csv.emps,',')))/length(','))-1 14 )

Olavi Sau  Oct 05, 2016 
Printed Page 148
MySQL section, line 9

In line 9 of the MySQL code answer, there is not a +1 after length(src.name) - length(replace(src.name, ',' , '')) as there should be. There should be a +1 after this because as it currently reads the code returns data that cuts off the last name in the string (in this case the names 'curly' and 'leena'). For example, the 'where' clause without the +1 means that when there are 4 names in the string, the id=4, but there are only 3 commas. Thus, without the +1, only 3 names are returned instead of 4. This error does not impact the answer, since the question is looking for the 2nd name in the string. However, if we were looking for the third or fourth name(s) in the string, it would be a problem.

Bjorn Commers  Feb 17, 2014 
Printed Page 249

Today is December 20, 2012. I typed in and ran the DB2 Creating a calendar example. The result does not display correctly because the week nbr returned for the week starting with December 30th 2012 is week number 1. This caused the last week to appear as the first week in the calendar. I ran the query under DB2 for iSeries, version 7.1

Mark Thurlo  Dec 20, 2012 
PDF Page 299
Locating a Range of Consecutive Values --> Solution --> Oracle

...of the window function LEAD OVER to look at the “next” row’s BEGIN_DATE,... BEGIN_DATE should be PROJ_START

Anonymous  Feb 26, 2018 
Printed Page 313
Paragraph in middle of page

last sentence of the first paragraph under SOLUTION - reads, "Once you have identify the ranges,..." looks to me as if it should be "Once you have identified the ranges,..."

Anonymous  Apr 29, 2013 
Printed Page 425
In the solution item from section 12.16 - "Creating a Sparse Matrix"

The construction of the Sparse Matrix: select MAX(case deptno when 10 then ename end) as d10, MAX(case deptno when 20 then ename end) as d20, MAX(case deptno when 30 then ename end) as d30, MAX(case job when 'CLERK' then ename end) as clerks, MAX(case job when 'MANAGER' then ename end) as mgrs, MAX(case job when 'PRESIDENT' then ename end) as prez, MAX(case job when 'ANALYST' then ename end) as anal, MAX(case job when 'SALESMAN' then ename end) as sales from ( select deptno, job, ename, row_number() over(partition by deptno order by empno) rn from emp ) x GROUP BY has a failure. For example, if Allen was a Manager instead of a Sales then his position in the "job" column would be in conflict with the Clark one. It is easy to see applying the update in the emp table: UPDATE EMP SET JOB = 'MANAGER' WHERE EMPNO=7499;

Fabio Paolini  Sep 10, 2018 
Printed Page 568
SQL Server solution to Q5

SELECT sno,sname,age FROM ( SELECT sno,sname,age, DENSE_RANK( ) OVER (ORDER BY age) AS dr FROM student ) x WHERE dr <= 3 should be using RANK, not DENSE_RANK more details here : https://www.reddit.com/r/SQL/comments/ih11tj/an_error_in_the_sql_cookbook_anthony_molinaro/

Gabriel Le Gall  Aug 26, 2020 
Mobi Page 10672
text

In Table Names and Numbers are not aligned properly

Anonymous  Sep 23, 2019 
PDF, ePub, Mobi Page 12620
text

"setoriented problem-solving for" should be "set oriented problem-solving for"

Anonymous  Sep 23, 2019