Question

Consider the Goldstar database. Choose the query that displays the average salary for all positions in the city of Chicago.

SELECT title, AVG(max_salary) AS AverageSalary FROM position, employee, location WHERE location.location_id = employee.location_id AND position.position_id = employee.position_id AND city = "Chicago" GROUP BY title;

SELECT title, AVG(salary) AS AverageSalary FROM position, employee, location WHERE position.position_id = employee.position_id AND location.location_id = employee.location_id AND city = "Chicago" GROUP BY title;

SELECT title, AVG(salary) AS AverageSalary FROM position, employee, location WHERE location.location_id = employee.location_id AND city = "Chicago" GROUP BY title;

SELECT title, AVG(max_salary) AS AverageSalary FROM position, employee, location WHERE location.location_id = employee.location_id AND position.position_id = employee.position_id OR city = "Chicago" GROUP BY title;

Answer 1

The answer is:

SELECT title, AVG(salary) AS AverageSalary FROM position, employee, location WHERE position.position_id = employee.position_id AND location.location_id = employee.location_id AND city = "Chicago" GROUP BY title;

Explanation

• The select statement is selecting the title and the average salary
• We have three tables here - position, employee and location
• The where statement is telling show the position for those employees in the city of Chicago
• Grouping is by title which is required

Let me know in the comments if anything is not clear. I will reply ASAP! Please do upvote if satisfied!