f. Find all employees in the database who do not work for the First Bank Corpora
ID: 3754359 • Letter: F
Question
f. Find all employees in the database who do not work for the First Bank Corporation.
g. Find all employees in the database who earn more than each employee of Small Bank Corporation.
h. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.
i. Find all employees who earn more than the average salary of all employees of their company.
j. Find the company that has the most employees.
k. Find the company that has the smallest payroll.
l. Find those companies whose employees earn a higher salary, on average, than the average of First Bank Corporation.
employee(employee-id, employee-name, street, city)
works(employee-id, company-id, salary)
company(company-id, company-name, city)
manages(employee-id, manager-id)
Figure 2. Employee database
Explanation / Answer
If you have any doubts, please give me comment...
-- f)
SELECT `employee-name`
FROM employee
WHERE `employee-name` NOT IN(
SELECT `employee-name`
FROM works
WHERE `company-name` = 'First Bank Corporation'
);
-- g)
SELECT `employee-name`
FROM works
WHERE salary > ALL(
SELECT salary
FROM works
WHERE `company-name` = 'Small Bank Corporation'
);
-- h)
SELECT S.`company-name`
FROM company S
WHERE NOT EXISTS (
(
SELECT city
FROM company
WHERE `company-name` = 'Small Bank Corporation'
)
EXCEPT
(
SELECT city
FROM company T
WHERE S.`company-name` = T.`company-name`
)
);
-- i)
SELECT `employee-name`
FROM works t
WHERE salary > (
SELECT AVG(salary)
FROM works s
WHERE t.`company-name` = s.`company-name`
);
-- j)
SELECT `company-name`
FROM works
GROUP BY `company-name`
HAVING COUNT(DISTINCT `employee-name`) >= ALL(
SELECT COUNT(DISTINCT `employee-name`)
FROM works
GROUP BY `company-name`
);
-- k)
SELECT `company-name`
FROm works
GROUP BY `company-name`
HAVING SUM(salary) <= ALL(
SELECT SUM(salary)
FROM works
GROUP BY `company-name`
);
-- l)
SELECT `company-name`
FROM works
GROUP BY `company-name`
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM works
WHERE `company-name` = 'First Bank Corporation'
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.