Question

Consider the Goldstar database. Choose the query that will display all of the position titles and the number of employees that hold that position.

SELECT title, COUNT(ssn) AS TotalEmployees FROM position, employee WHERE position.location_id = employee.position_id GROUP BY title;

SELECT title, SUM(ssn) AS TotalEmployees FROM position, employee WHERE position.position_id = employee.position_id GROUP BY title;

SELECT title, COUNT(ssn) AS TotalEmployees FROM position, employee WHERE position.position_id = employee.position_id GROUP BY title;

SELECT title, SUM(ssn) AS TotalEmployees FROM position, employee WHERE position.location_id = employee.position_id GROUP BY title;

Answer 1

The answer is:

SELECT title, COUNT(ssn) AS TotalEmployees FROM position, employee WHERE position.position_id = employee.position_id GROUP BY title;

Explanation:

• The select statement is selecting the title and the count of employees which is required as the output of the query
• The tables are position and employees
• we need to extract those employees using the WHERE statement where the position ids are the same. This is the link between the two tables
• Grouping again is by title

Additional notes - The reasons other options were eliminated:

• We need to take the count of SSN and not the sum of SSN
• Here location is not important as we need to display all of the position titles and the number of employees that hold that position.

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