Click here to Skip to main content
15,175,307 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
table employee(#emp_name, city, salary);
table salary(salary,#city);
table company(#Company_name, city);

Find out the Company Name who gives Highest Salary to their Employees.


What I have tried:

SELECT SUM(e.Salary) FROM employee AS e, salary AS s, company AS c WHERE e.city = s.city AND e.city = c.city
Posted
Updated 11-Oct-17 0:40am
Comments
Peter Leow 14-Jan-17 6:06am
   
When I read the title, I thought you were looking for the company that has the highest paid employee. However, your sql statement suggested that you are trying to find the company that paid the highest TOTAL amount of salary to all its employees, it that right? It doesn't make much sense. Companies with more employees tend to pay more in total as compared to those with fewer employees. What exactly do you want to achieve? Just curious.
Afzaal Ahmad Zeeshan 14-Jan-17 8:28am
   
Just kidding: Try fortune 500. :-)

Since this seems to be homework, I'm not writing the complete answer...

Well basically your're close. Just add company name to the SELECT list and GROUP BY it. Then order the set using ORDER BY clause. Since you're looking for the highest sum, order in descending order. And the last step, fetch only the first row. For this you can use TOP.

However, the table design looks a bit odd. Employee does not have direct relation to Company but the relation goes via city. Also you have a separate salary table, why is that?
   
Comments
sazzad37 14-Jan-17 5:29am
   
This is not homework, Actually I am learning SQL to prepare myself for job interview. I found this problem somewhere and try to solve it. Is the following modified query works right? Thank's for your valuable response.

SELECT Company_name, SUM(e.Salary) AS Highest_Salary FROM employee AS e, salary AS s, company AS c WHERE e.city = s.city AND e.city = c.city GROUP BY Company_name ORDER BY Highest_Salary DESC LIMIT 1
Wendelius 14-Jan-17 5:38am
   
Seems quite right. Depending on the database you use, it may be wrong to use AS for the table alias and some database use TOP and some LIMIT clause.
sazzad37 14-Jan-17 5:40am
   
Can you please share me the right one.
Wendelius 14-Jan-17 8:14am
   
I mean that the query looks fine. If you have the database, just try to run it.
sazzad37 14-Jan-17 21:58pm
   
Ok, Thanks
select company_name from company natural join salary where salary = (select max(salary) from salary);
   
Comments
Richard Deeming 12-Oct-17 14:21pm
   
If the OP is still waiting for someone to do his homework 10 months later, he's failed the course!
Is city common field in all the tables? if so you can use join query for company and salary table.

Try this query, it will work

SELECT C.COMPANY_NAME,SUM(S.SALARY) FROM COMPANY_NAME C LEFT JOIN SALARY S ON C.CITY = S.CITY GROUP BY C.COMPANY_NAME ORDER BY DESC

Thanks,
Vijay
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900