Need a SQL query to find out company name who gives highest salary to their employees

Question

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 13-Jan-17 19:59pm

sazzad37

Updated 10-Oct-17 23:40pm

Add a Solution

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. :-)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2017-01-13T21:10:00

Solution 1

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?

Posted 13-Jan-17 21:10pm

Wendelius

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

Member 13458387 · Answer 2 · 2017-10-10T22:39:00

Solution 2

select company_name from company natural join salary where salary = (select max(salary) from salary);

Posted 10-Oct-17 22:39pm

Member 13458387

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!

vijay vk · Answer 3 · 2017-10-10T23:40:00

Solution 3

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

Posted 10-Oct-17 23:40pm

vijay vk

Updated 10-Oct-17 23:44pm

v2