QUESTION 12 Given the schema of Figure 3.20 on Page 108 of textbook. Which of th

Question

QUESTION 12 Given the schema of Figure 3.20 on Page 108 of textbook. Which of the following is the correct answer to the query: Find the name of company whose employees have the highest average sala A select company_name from works group by company_name having avg(salary) >= ALL (select avg(salary) from works group by company_name B. Select company-name from works group by company_name having avg(salary) >= ALL (select avg(salary) from works) select company-name from works having avg(salary) >= ALL (select avg(salary) from works) select company_name from works group by company_name

Explanation / Answer

Option C: WRONG

The query is

select company_name

from works

having avg(salary>=ALL(select avg(salary) from works)

This does not group the data by company name. We need to group the data by company name to that we can get the average salaries for all the companies. So this option is wrong.

Option D/ Option B: WRONG

Query B:

Select company name

from works

group by company_name

having avg(salary>=ALL(select avg(salary) from works)

Query C:

Select company name

from works

group by company_name

having(max(avg(salary))

Both options B and option D has the same first three queries it basically groups all the data with the company name but suppose a condition in which we have duplicates suppose two rows with same company name A so we need to group by the company name with respect to the average salary to find the company with max average salary.

Option A: Correct

Select company name

from works

group by company_name

having avg(salary>=ALL(select avg(salary) from works group by company_name)

Here in the 3rd query, the table is grouped by the company_name then the again we use:

avg(salary>=ALL(select avg(salary) from works group by company_name)

This group by groups the company_name with respect to the average salary

for example, the table is something like this

comapany_name salary

A 10K

A 10K

A 10K

B 5K

C 5K

Average after the first group by is 8k but we have to again group by to get only three values company A, B and C.

so the Average salary for company A would be 10 and the overall average would be 20/3=6.66.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.