Find the company that has the smallest payroll

Question

Hi i have the following database scheema.

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)

I need to find Find the company that has the smallest payroll.

I have written the following query but my trainer tells me its wrong.

SELECT company-name
FROM works
GROUP BY company-name
HAVING sum(salary) < ( SELECT sum(salary)
FROM works
GROUP BY company-name)

Answer 1

I'll give you a further hint. The < is what's wrong (think carefully about what the subquery returns).

Answer 2

we can use < All in this case?

e.g. :

SELECT company-name
FROM works
GROUP BY company-name
HAVING sum(salary) < ALL( SELECT sum(salary)
FROM works
GROUP BY company-name)

Answer 3

You can find it as follows:

SELECT company_name, SUM (salary) AS lowest_payroll
    FROM works
GROUP BY company_name
  HAVING SUM (salary) = (SELECT MIN (payrl)     AS low_payroll
                           FROM (  SELECT company_name, SUM (salary) payrl
                                     FROM works
                                 GROUP BY company_name));

Answer 4

SELECT company-name
FROM works
GROUP BY company-name
HAVING sum(salary) < ( SELECT sum(salary)
FROM works
GROUP BY company-name)

You have to change < to <= all and not < all

SELECT company-name
FROM works
GROUP BY company-name
HAVING sum(salary) <= all ( SELECT sum(salary)
FROM works
GROUP BY company-name)

Because the minimum sum(salary) should be equated to true, example

HAVING 590000 <= all (750000 , 590000 , 620000) 

Should return true in this case as 590000 when compared with 590000 in () should return true i.e. 590000 <= 590000 -> true, where as 590000 < 590000 -> false so you wouldn't get any output so correct < all to <= all.