该题需要进行以下几个步骤:
(1)对电影进行分类(category_id),选取分类后电影总数量大于等于5( COUNT(film_id)>=5);
(2)连接三个表,在电影总数大于5的分类中查询(film.description)中包含robot的电影;
(3)列出对应的分类名称(category.name)以及电影数目(count(film.film_id))。
SELECT c.name,count(f.film_id) FROM film AS f,category AS c,film_category AS fc WHERE f.film_id=fc.film_id AND c.category_id=fc.category_id AND f.description LIKE'%robot%' AND c.category_id IN(SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(film_id)>=5);