Errata

The code for the inter table needs to have additional lines that select just one row from each half of the table.

where rank_sal>=0.5 should be followed by:
Limit 1
Order by rank_sal asc

And

where rank_sal<=0.5 should be followed by:
Limit 1
Order by rank_sal desc

Otherwise, the code as written will compute a simple average as inter contains all rows.

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/

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;

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

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.

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 )

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.

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?