SQLPad
Instruction
- Return the name of the category that has the most films.
- If there are ties, return just one of them.
Table 1: category
Movie categories.
col_name | col_type -------------+-------------------------- category_id | integer name | text
Table 2: film_category
A film can only belong to one category
col_name | col_type -------------+-------------------------- film_id | smallint category_id | smallint
Sample results
name -------- Category Name
Expected results
Solution postgres
SELECT
C.name
FROM film_category FC
INNER JOIN category C
ON C.category_id = FC.category_id
GROUP BY C.name
ORDER BY COUNT(*) DESC
LIMIT 1;
Explanation
This query selects the name of the category that appears the most frequently in the film_category table. It does this by joining the film_category table with the category table on their category_id columns. Then, it groups the result by the name of the category and orders the groups by the count of rows in each group in descending order. Finally, it limits the result to only the first row, which will be the category with the highest count.
Copied
Your results