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 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
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 distinct name of the mills built by the architects who have also built a bridge longer than 80 meters?
assistant
SELECT DISTINCT T1.name FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id = T2.id JOIN bridge AS T3 ON T3.architect_id = T2.id WHERE T3.length_meters > 80
human
show the name of all bridges that was designed by american archtect, and sort the result by the bridge feet length.
assistant
SELECT t1.name FROM bridge AS t1 JOIN architect AS t2 ON t1.architect_id = t2.id WHERE t2.nationality = 'American' ORDER BY t1.length_feet
human
What is the maximum length in meters for the bridges and what are the architects' names?
assistant
SELECT max(T1.length_meters) , T2.name FROM bridge AS T1 JOIN architect AS T2 ON T1.architect_id = T2.id
human
What is the average length in feet of the bridges?
assistant
SELECT avg ( length_feet ) FROM bridge ;