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
List the camera lens names containing substring 'Digital'.
assistant
SELECT name FROM camera_lens WHERE name LIKE '%Digital%'
human
How many distinct kinds of camera lenses are used to take photos of mountains in the country 'Ethiopia'?
assistant
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'
human
How many camera lenses have a focal length longer than 15 mm?
assistant
SELECT count(*) FROM camera_lens WHERE focal_length_mm > 15
human
How many camera lenses are not used in taking any photos?
assistant
SELECT count(*) FROM camera_lens WHERE id NOT IN ( SELECT camera_lens_id FROM photos )
human
What are the names of photos taken with the lens brand 'Sigma' or 'Olympus'?
assistant
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'
human
How many different kinds of lens brands are there?
assistant
SELECT COUNT( DISTINCT brand ) FROM camera_lens ;