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 id of all the camera lenses used for taking photos.
assistant
SELECT camera_lens_id FROM photos;
human
Which camera lenses were not used for taking any photos?
assistant
SELECT * FROM camera_lens WHERE id NOT IN ( SELECT camera_lens_id FROM photos );
human
How many are there?
assistant
SELECT COUNT( * ) FROM camera_lens WHERE id NOT IN ( SELECT camera_lens_id FROM photos ) ;