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 names and year of construction for the mills of 'Grondzeiler' type?
assistant
SELECT name , built_year FROM mill WHERE TYPE = 'Grondzeiler'
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
What are the names of the mills which are not located in 'Donceel'?
assistant
SELECT name FROM mill WHERE LOCATION != 'Donceel'
human
How many architects haven't built a mill before year 1850?
assistant
SELECT count(*) FROM architect WHERE id NOT IN ( SELECT architect_id FROM mill WHERE built_year < 1850 );
human
What are the distinct types of mills that are built by American or Canadian architects?
assistant
SELECT DISTINCT T1.type FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id = T2.id WHERE T2.nationality = 'American' OR T2.nationality = 'Canadian'
human
What is the most common mill type, and how many are there?
assistant
SELECT TYPE , COUNT( * ) FROM mill GROUP BY TYPE ORDER BY COUNT( * ) DESC LIMIT 1 ;