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
Show the ids and names of all mountains.
assistant
SELECT id , name FROM mountain;
human
Show the ids and names of those that have photos.
assistant
SELECT T1.id , T1.name FROM mountain AS T1 JOIN photos AS T2 ON T1.id = T2.mountain_id;
human
Which ones 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 ;