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 and names of all architects?
assistant
SELECT id , name FROM architect;
human
How many of those architects built less than 3 bridges?
assistant
SELECT COUNT( * ) FROM ( 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
How many built 3 or more bridges?
assistant
SELECT COUNT( * ) FROM ( 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
Please list their ids and names.
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 ;