
CREATE TABLE camera_lens (
	id INTEGER, 
	brand TEXT, 
	name TEXT, 
	focal_length_mm REAL, 
	max_aperture REAL, 
	PRIMARY KEY (id)
)


CREATE TABLE mountain (
	id INTEGER, 
	name TEXT, 
	"Height" REAL, 
	"Prominence" REAL, 
	"Range" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE photos (
	id INTEGER, 
	camera_lens_id INTEGER, 
	mountain_id INTEGER, 
	color TEXT, 
	name TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(mountain_id) REFERENCES mountain (id), 
	FOREIGN KEY(camera_lens_id) REFERENCES camera_lens (id)
)

CREATE TABLE camera_lens (
	id INTEGER, 
	brand TEXT, 
	name TEXT, 
	focal_length_mm REAL, 
	max_aperture REAL, 
	PRIMARY KEY (id)
)

/*
2 rows from camera_lens table:
id	brand	name	focal_length_mm	max_aperture
1	Olympus	Olympus 15mm f/8.0 Body Cap	15.0	8.0
2	Olympus	Olympus M.Zuiko Digital ED 45mm f/1.8	45.0	1.8
*/


CREATE TABLE mountain (
	id INTEGER, 
	name TEXT, 
	"Height" REAL, 
	"Prominence" REAL, 
	"Range" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from mountain table:
id	name	Height	Prominence	Range	Country
1	Abune Yosef / Guliba Amba	4260.0	1909.0	Lasta Massif	Ethiopia
2	Ioll / Yoll	4220.0	350.0	Kollo Massif	Ethiopia
*/


CREATE TABLE photos (
	id INTEGER, 
	camera_lens_id INTEGER, 
	mountain_id INTEGER, 
	color TEXT, 
	name TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(mountain_id) REFERENCES mountain (id), 
	FOREIGN KEY(camera_lens_id) REFERENCES camera_lens (id)
)

/*
2 rows from photos table:
id	camera_lens_id	mountain_id	color	name
1	1	20	RBG	monkey
2	10	2	RBG	rabbits
*/
Find the brand and name for each camera lens, and sort in descending order of maximum aperture.
SELECT brand ,  name FROM camera_lens ORDER BY max_aperture DESC
[{'brand': 'Olympus', 'name': 'Olympus 15mm f/8.0 Body Cap'}, {'brand': 'Panasonic', 'name': 'Panasonic Lumix G 150mm f /2.8'}, {'brand': 'Sigma', 'name': 'Sigma 19mm f2.8 DN'}, {'brand': 'Sigma', 'name': 'Sigma 19mm f2.8 EX DN'}, {'brand': 'Sigma', 'name': 'Sigma 30mm f2.8 DN'}, {'brand': 'Schneider Kreuznach', 'name': 'Schneider Kreuznach Super Angulon 14mm f /2.0'}, {'brand': 'Olympus', 'name': 'Olympus M.Zuiko Digital ED 45mm f/1.8'}, {'brand': 'Olympus', 'name': 'Olympus M.Zuiko Digital ED 75mm f/1.8'}, {'brand': 'Panasonic', 'name': 'Panasonic Leica DG Summilux 25mm f /1.4 Asph.'}, {'brand': 'Schneider Kreuznach', 'name': 'Schneider Kreuznach Xenon 30mm f /1.4'}, {'brand': 'Panasonic', 'name': 'Panasonic Leica DG Nocticron 42.5mm f /1.2'}]