human
Schema:
mountain (id, name, Height, Prominence, Range, Country)
camera_lens (id, brand, name, focal_length_mm, max_aperture)
photos (id, camera_lens_id, mountain_id, color, name)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the name, height and prominence of mountains which do not belong to the range 'Aberdare Range'?
assistant
SELECT name , height , prominence FROM mountain WHERE range != 'Aberdare Range'
human
Show the name and prominence of the mountains whose picture is not taken by a lens of brand 'Sigma'.
assistant
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'
human
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'
assistant
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'
human
What are the names of the cameras that have taken picture of the most mountains?
assistant
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
human
What are the id and name of the photos for mountains?
assistant
SELECT T1.id , T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id = T2.mountain_id WHERE T1.height > 4000
human
What are the id and name of the mountains that have at least 2 photos?
assistant
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 ;