Question

This query refers to the sakila database, (in MySQL, make sure you run the command - USE sakila) (Ageregate Query)

For each film, display the film title and the number of actors that star in the film, Sort the results by title.

a) Show the SQL query.

b) Run the query in MySQL. How many rows are returned?

c) List just the first 2 rows from the results of the query. (You can cut and paste the query results; Otherwise, you can also type them, referring to the result of the query)

Answer 1

To display the film title and the number of actors that star in each film and sort the results by title, you can use a SQL query. By running the query in MySQL and using the LIMIT clause, you can obtain the desired results. This question involves the use of SQL queries with the sakila database.

Step-by-step explanation:

To display the film title and the number of actors that star in each film and sort the results by title, you can use the following SQL query:

SELECT film.title, COUNT(actor.actor_id) AS actor_count
FROM film
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id
ORDER BY film.title;

To run the query in MySQL, make sure you have selected the 'sakila' database by running the command:

USE sakila;

The number of rows returned will depend on your specific database, but it should display the film title and the corresponding number of actors for each film.

To list just the first 2 rows from the results of the query, you can use the LIMIT clause:

SELECT film.title, COUNT(actor.actor_id) AS actor_count
FROM film
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id
ORDER BY film.title
LIMIT 2;

This will show only the first 2 rows from the query results, which will include the film title and the number of actors.