
CREATE TABLE architect (
	id TEXT, 
	name TEXT, 
	nationality TEXT, 
	gender TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE bridge (
	architect_id INTEGER, 
	id INTEGER, 
	name TEXT, 
	location TEXT, 
	length_meters REAL, 
	length_feet REAL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(architect_id) REFERENCES architect (id)
)


CREATE TABLE mill (
	architect_id INTEGER, 
	id INTEGER, 
	location TEXT, 
	name TEXT, 
	type TEXT, 
	built_year INTEGER, 
	notes TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(architect_id) REFERENCES architect (id)
)

CREATE TABLE architect (
	id TEXT, 
	name TEXT, 
	nationality TEXT, 
	gender TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from architect table:
id	name	nationality	gender
1	Frank Lloyd Wright	American	male
2	Frank Gehry	Canadian	male
*/


CREATE TABLE bridge (
	architect_id INTEGER, 
	id INTEGER, 
	name TEXT, 
	location TEXT, 
	length_meters REAL, 
	length_feet REAL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(architect_id) REFERENCES architect (id)
)

/*
2 rows from bridge table:
architect_id	id	name	location	length_meters	length_feet
1	1	Xian Ren Qiao (Fairy Bridge)	Guangxi , China	121.0	400.0
2	2	Landscape Arch	Arches National Park , Utah , USA	88.0	290.0
*/


CREATE TABLE mill (
	architect_id INTEGER, 
	id INTEGER, 
	location TEXT, 
	name TEXT, 
	type TEXT, 
	built_year INTEGER, 
	notes TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(architect_id) REFERENCES architect (id)
)

/*
2 rows from mill table:
architect_id	id	location	name	type	built_year	notes
1	1	Coswarem	Le Vieux Molen	Grondzeiler	1840	Molenechos (Dutch)
1	2	Donceel	Moulin Bertrand	Grondzeiler	1890	Molenechos (Dutch)
*/
show the name of all bridges that was designed by american archtect, and sort the result by the bridge feet length.
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
[{'name': 'Hazarchishma Natural Bridge'}, {'name': 'Jiangzhou Arch'}, {'name': "Shipton's Arch"}, {'name': 'Xian Ren Qiao (Fairy Bridge)'}]