SQL Data retrieval

Question

I am trying to write a SELECT statement to retrieve records from emp table on Scott user. But not getting answer.

The question is :

display empno, ename, sal, max_sal_in_his_dept, sal+12% on existing salary of those employes whose salary is below 1/3rd of maximum salary in their respective departments.

i tried group by with where clause, didnt work, tried group by with having but not working,

Plz help

Answer 1

0

Here is my shot at that, but I am not sure of it. I could have test it if some test data would be there.

WITH DepatmentSalary as
(
SELECT MAX(salary) as maxsalary, department
from employee
group by department
)


SELECT empno, ename, sal, max_sal
from employees
CROSS APPLY
(
SELECT TOP 1 MAX(salary) max_sal FROM DepatmentSalary
WHERE employees.department = DepatmentSalary.department
)
where salary < ( select TOP 1 maxsalary/3 from DepatmentSalary
WHERE employees.department = DepatmentSalary.department)

answered Jan 20 '12 at 02:31 AM

robbin
1.6k ● 1 ● 3 ● 5

(comments are locked)

SQL Data retrieval

Follow this question

Related Questions