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
What is the id, name and nationality of the architect who built most mills?
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
human
What are the ids and names of the architects who built at least 3 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(*) >= 3
human
What are the ids, names and genders of the architects who built two bridges or one mill?
assistant
SELECT T1.id , T1.name , T1.gender FROM architect AS T1 JOIN bridge AS T2 ON T1.id = T2.architect_id GROUP BY T1.id HAVING count(*) = 2 UNION 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
human
How many architects are female?
assistant
SELECT count(*) FROM architect WHERE gender = 'female'
human
What are the distinct names and nationalities of the architects who have ever built a mill?
assistant
SELECT DISTINCT T1.name , T1.nationality FROM architect AS T1 JOIN mill AS t2 ON T1.id = T2.architect_id
human
List the name, nationality and id of all male architects ordered by their names lexicographically.
assistant
SELECT name , nationality , id FROM architect WHERE gender = "male" ORDER BY name ;