How to write Relational Algebra

Question

Here the relation: WORKS(emp_name, company_name,salary)

Q. Write an expression Relational Algebra to find the company name that has the highest number of employee.

I tried to solve it in many ways but not finding the correct way.

Answer 1

Here is a query which should work across most RDBMS:

SELECT company_name
FROM WORKS
GROUP BY company_name
HAVING COUNT(*) = SELECT MAX(empCount) FROM
(
    SELECT COUNT(*) AS empCount
    FROM WORKS
    GROUP BY company_name
) t

If you are using MySQL, SQL Server, or any database which has a LIMIT keyword (or something like it), then the query gets easier:

SELECT company_name, COUNT(*) AS empCount
FROM WORKS
GROUP BY company_name
ORDER BY empCount DESC
LIMIT 1