Database: mountain_photos

CREATE TABLE IF NOT EXISTS "mountain" (
"id" int,
"name" text,
"Height" real,
"Prominence" real,
"Range" text,
"Country" text,
primary key("id")
);
CREATE TABLE IF NOT EXISTS "camera_lens" (
"id" int,
"brand" text,
"name" text,
"focal_length_mm" real,
"max_aperture" real,
primary key("id")
);
CREATE TABLE IF NOT EXISTS "photos" (
"id" int, 
"camera_lens_id" int,
"mountain_id" int,
"color" text, 
"name" text,
primary key("id"),
foreign key("camera_lens_id") references `camera_lens`("id"),
foreign key("mountain_id") references `mountain`("id")
);

Question: How many camera lenses have a focal length longer than 15 mm?
SQL: SELECT count(*) FROM camera_lens WHERE focal_length_mm  >  15

Question: Find the brand and name for each camera lens, and sort in descending order of maximum aperture.
SQL: SELECT brand ,  name FROM camera_lens ORDER BY max_aperture DESC

Question: List the id, color scheme, and name for all the photos.
SQL: SELECT id ,  color ,  name FROM photos

Question: What are the maximum and average height of the mountains?
SQL: SELECT max(height) ,  avg(height) FROM mountain

Question: What are the average prominence of the mountains in country 'Morocco'?
SQL: SELECT avg(prominence) FROM mountain WHERE country  =  'Morocco'

Question: What are the name, height and prominence of mountains which do not belong to the range 'Aberdare Range'?
SQL: SELECT name ,  height ,  prominence FROM mountain WHERE range != 'Aberdare Range'

Question: What are the id and name of the photos for mountains?
SQL: SELECT T1.id ,  T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id WHERE T1.height  >  4000

Question: What are the id and name of the mountains that have at least 2 photos?
SQL: SELECT T1.id ,  T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id GROUP BY T1.id HAVING count(*)  >=  2

Question: What are the names of the cameras that have taken picture of the most mountains?
SQL: SELECT T2.name FROM photos AS T1 JOIN camera_lens AS T2 ON T1.camera_lens_id  =  T2.id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1

Question: What are the names of photos taken with the lens brand 'Sigma' or 'Olympus'?
SQL: SELECT T1.name FROM camera_lens AS T1 JOIN photos AS T2 ON T2.camera_lens_id  =  T1.id WHERE T1.brand  =  'Sigma' OR T1.brand  =  'Olympus'

Question: How many different kinds of lens brands are there?
SQL: SELECT count(DISTINCT brand) FROM camera_lens

Question: How many camera lenses are not used in taking any photos?
SQL: SELECT count(*) FROM camera_lens WHERE id NOT IN ( SELECT camera_lens_id FROM photos )

Question: How many distinct kinds of camera lenses are used to take photos of mountains in the country 'Ethiopia'?
SQL: SELECT count(DISTINCT T2.camera_lens_id) FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id WHERE T1.country  =  'Ethiopia'

Question: List the brands of lenses that took both a picture of mountains with range 'Toubkal Atlas' and a picture of mountains with range 'Lasta Massif'
SQL: SELECT T3.brand FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T1.range  =  'Toubkal Atlas' INTERSECT SELECT T3.brand FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T1.range  =  'Lasta Massif'

Question: Show the name and prominence of the mountains whose picture is not taken by a lens of brand 'Sigma'.
SQL: SELECT name ,  prominence FROM mountain EXCEPT SELECT T1.name ,  T1.prominence FROM mountain AS T1 JOIN photos AS T2 ON T1.id  =  T2.mountain_id JOIN camera_lens AS T3 ON T2.camera_lens_id  =  T3.id WHERE T3.brand  =  'Sigma'

Question: List the camera lens names containing substring "Digital".
SQL: SELECT name FROM camera_lens WHERE name LIKE "%Digital%"

Question: What is the name of each camera lens and the number of photos taken by it? Order the result by the count of photos.
SQL: SELECT T1.name ,  count(*) FROM camera_lens AS T1 JOIN photos AS T2 ON T1.id  =  T2.camera_lens_id GROUP BY T1.id ORDER BY count(*)