question

Meena avatar image
Meena asked

SQL Data retrieval

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
select
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please provide some test data for this?
0 Likes 0 ·

1 Answer

· Write an Answer
robbin avatar image
robbin answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.