15.6k views
2 votes
write an sql statement to list the lastname and firstname of employees who have worked on a property in seattle. use a subquery.

1 Answer

1 vote

Final answer:

The SQL statement provided lists the lastname and firstname of employees who have worked on a property in Seattle using a subquery that filters records based on the association between employees, properties, and the works_on table, with a condition for the property's city.

Step-by-step explanation:

To answer the student's question about writing an SQL statement to list the lastname and firstname of employees who have worked on a property in Seattle, you can use the following SQL query with a subquery:

SELECT e.lastname, e.firstname
FROM employees e
WHERE EXISTS (
SELECT *
FROM properties p
INNER JOIN works_on w ON p.property_id = w.property_id
WHERE w.employee_id = e.employee_id
AND p.city = 'Seattle'
)

Step-by-step explanation:

  1. The main query is selecting the lastname and firstname from the employees table.
  2. The WHERE EXISTS clause uses a subquery to check for employee IDs in the works_on table that are linked to properties in Seattle through the properties table.
  3. The subquery correlates the main query's employee employee_id with the employee_id in the works_on table and filters properties with the city equal to 'Seattle'.
User Tiago Alves
by
8.5k points
Welcome to QAmmunity.org, where you can ask questions and receive answers from other members of our community.

9.4m questions

12.2m answers

Categories