Recently in the "Oracle 11 g database administrator guide" this book, found a mistake in the book,
Example: assume the request according to the department of statistics, the wages of employees to participate in statistical employee wages must be greater than 1000 yuan, among all the statistical results meet the minimum wage in 900 yuan of above, and total wages department statistics is shown in 7000 yuan of above, according to the sorted by total wages from big to small, if pay the same sum, then according to the smallest division,
For statements are given in the book: select deptno as department, avg (sal) as the average salary, min (sal) as the minimum wage, Max (sal) as the highest wages, sum (sal) as total wages from emp where sal> The 1000 group by deptno having min (sal) & gt; 900 and the sum (sal) & gt; 7000 order by Max (sal) desc, deptno asc.
Question: in the where clause has data to eliminate employee salary less than 1000 yuan, not participate in the statistics, so the output data of sal are all more than 1000 yuan, as a result, in the having clause, department of the minimum wage in 900 yuan of above conditions is redundant, not achieve the purpose of sample request, a great god, please give the correct statement,

CodePudding user response:

Give the landlord a praise, cheer for you in the spirit of practical,
Your analysis is right

CodePudding user response:

According to problem description, someone how much salary if there is a greater than 1000 pay can query the individual, but he still has a minimum wage, so in having the filter again, will filter out at least a salary of less than 900 people,

CodePudding user response:

More than 1000 personal wages, ruled out is, & gt; 900 is division of the minimum wage, eliminate the is department,

CodePudding user response:

reference fengzhiqiang1981 reply: 3/f

is greater than 1000 personal wages, ruled out of a person, & gt; 900 the minimum wage is department, eliminate department,

That's what the sample request, but from the point of statement execution, which the sample is short of, because in the where clause, have all employees salary less than 1000 yuan to eliminate, so in the having clause, all departments of the minimum wage is not less than 1000 yuan, I just want to know now, if you want to achieve the purpose of sample required, SQL statements should be how to write, I all the sample reference table is in the oracle 11 g SCOTT user's own example table,

CodePudding user response:

Select deptno as department, avg (sal) as the average salary, min (sal) as the minimum wage, Max (sal) as the highest wages, sum (sal) as wage sum
The from emp where sal> 1000 and
Deptno in (select deptno from emp group by deptno having min (sal) & gt; 900 and the sum (sal) & gt; 7000)
Group by deptno
The order by Max (sal) desc, deptno asc.