Question

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

Answer 1

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'.