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
How many mills did each architect build?
assistant
SELECT COUNT( * ) FROM mill GROUP BY architect_id;
human
Which architect built the most?
assistant
SELECT * FROM architect AS T1 JOIN mill AS T2 ON T1.id = T2.architect_id GROUP BY T1.id ORDER BY COUNT( * ) DESC LIMIT 1;
human
What are the id, name and nationality of this architect?
assistant
SELECT T1.id , T1.name , T1.nationality FROM architect AS T1 JOIN mill AS T2 ON T1.id = T2.architect_id GROUP BY T1.id ORDER BY COUNT( * ) DESC LIMIT 1 ;