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

more ▼

asked Jan 20, 2012 at 01:58 AM in Default

avatar image

21 1 1 1

Can you please provide some test data for this?

Jan 20, 2012 at 02:28 AM robbin
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
 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)
more ▼

answered Jan 20, 2012 at 02:31 AM

avatar image

1.7k 1 5 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 20, 2012 at 01:58 AM

Seen: 690 times

Last Updated: Jan 20, 2012 at 01:58 AM

Copyright 2018 Redgate Software. Privacy Policy