human
Schema:
architect (id, name, nationality, gender)
bridge (architect_id, id, name, location, length_meters, length_feet)
mill (architect_id, id, location, name, type, built_year, notes)
Please answer with SQL.
assistant
Sure, I understand.
human
Can I please have a list of architects who have built at least two bridges, or at least one mill? | What attributes of the architects do you want? | Can you please tell me the names and the ids of the architects who have built two bridges?
assistant
SELECT T1.id , T1.name FROM architect AS T1 JOIN bridge AS T2 ON T1.id = T2.architect_id GROUP BY T1.id HAVING COUNT( * ) = 2;
human
Great. Would you please list the ids, names and genders of architects have built one mill?
assistant
SELECT T1.id , T1.name , T1.gender FROM architect AS T1 JOIN mill AS T2 ON T1.id = T2.architect_id GROUP BY T1.id HAVING COUNT( * ) = 1 ;