Query For getting the Salary of employee

user_11553587 0
Viewed 702
 Insert into Employee values (1,'Abdul Rehman','Street No 12','Kamra Kalan')
 Insert into Employee values (2,'Iram Bhatti','Street No 10','Attock')
 Insert into Employee values (3,'Danial Aziz','Street No 12','Kamra Kalan')
 Insert into Employee values (4,'Kashif Butt','Street No 10','Attock')
 Insert into Employee values (5,'Zohaib Butt','Street No 13','Peshawar')


 insert into Company values (1,'First Bank Co-Operation','Hydrabaad');
 insert into Company values (2,'Small Bank Co-Operation','Kashmir');



 Insert  into Works  values (1,2,5000)
 Insert  into Works  values (2,1,40000)
 Insert  into Works  values (1,3,56000)
 Insert  into Works  values (1,4,8000)
 Insert  into Works  values (2,2,78000)

Question: Write a query for getting the name of employees who earn more than every employee of Small Bank Co operation.

My query Solution:

 Select Employee.person_name from Works
 inner join Employee on Employee.person_Id
 =Works.Person_Id inner join Company on
 Company.Company_Id=Works.Company_Id
and  Salary>(Select Salary from works 
 Where Company.Company_name='Small Bank Co-Operation')

But This query not works for me how can I get this one?

sql-server-2008 sql-server sql
2 Answers

I think the simplest solution is just to join all of the tables and then find the employee who has a salary equal to the max salary from the works table.

SELECT e.person_name
FROM employee e inner join works w
on e.person_Id = w.Person_Id
inner join company c
on w.Company_Id = c.Company_Id
WHERE c.Company_name = 'Small Bank Co-Operation'
and w.Salary = (SELECT max(works.Salary)
                FROM works)
user_63527350

How much does an employee earn?

select person_id, sum(salary)
from works
group by person_id;

We can even extend this to see whether this is Small Bank employee:

select
  person_id,
  sum(salary),
  max(case when Company_Id = 
                (select Company_Id from company where company_name = 'Small Bank Co-Operation')
      then 1 else 0 end
  ) as is_small_banker
from works
group by person_id;

Now, use this to compare:

with salaries as
(
  select
    person_id,
    sum(salary) as total,
    max(case when Company_Id = 
                  (select Company_Id from company where company_name = 'Small Bank Co-Operation')
        then 1 else 0 end
    ) as is_small_banker
  from works
  group by person_id
)
select e.person_name
from employee e 
join salaries s on s.person_id = e.person_id
where total > all
(
  select total
  from salaries
  where is_small_banker = 1
)
order by e.person_name;

This is just one way to do this. You can do the same with NOT EXISTS for example (i.e. where not exists a Small Bank worker with the same or a higher salary).

Update

You have meanwhile tagged your request with SQL Server and told me that you are getting this error:

Msg 130, Level 15, State 1, Line 126 Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Obviously, SQL Server has problems with the conditional aggregation. You can circumvent this by joining the compabny table instead:

with salaries as
(
  select
    w.person_id,
    sum(w.salary) as total,
    max(case when c.company_name = 'Small Bank Co-Operation' then 1 else 0 end
       ) as is_small_banker
  from works w
  join company c on c.company_Id = w.company_Id
  group by w.person_id
);

Update 2

If the works table could only have one work (company and salary) per employee, the whole query would reduce to:

select e.person_name
from employee e 
join works w on w.person_id = e.person_id
where salary > all
(
  select salary
  from works
  where id_company =
    (select company_Id from company where company_name = 'Small Bank Co-Operation')
)
order by e.person_name;
user_22707620
Related