28

题目：查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

这题连输出格式都不给。不浪费时间，直接看答案。看懂这道题就算过了。

方法：先根据film_category表构建分类下电影数大于5部的分类电影表，再进行多表用WHERE联查和GROUP BY分类
SELECT c.name, COUNT(fc.film_id)
FROM film AS f, film_category AS fc, category AS c,
(select category_id
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
GROUP BY c.category_id;
注意：最后的GROUP BY c.category_id是一定要的

改进：事实上，查出大于5部电影对应的分类表，放在WHERE里做不相关子查询就可以：
SELECT c.name, COUNT(fc.film_id)
FROM film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id IN (select category_id
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5)
GROUP BY c.category_id;