SELECT actor.actor_id AS actor_id, GROUP_CONCAT(film_actor.film_id) AS films_ids
FROM actor
INNER JOIN film_actor
ON actor.actor_id=film_actor.actor_id
GROUP BY actor.actor_id
SELECT COUNT(*) cantidad_actores, f.title titulo_pelicula
FROM actor a
INNER JOIN film_actor fa
ON a.actor_id=fa.actor_id
INNER JOIN film f ON fa.film_id=f.film_id
GROUP BY f.title;
SELECT actor.actor_id, actor.first_name, COUNT(*) AS films_count
FROM actor
INNER JOIN film_actor
ON actor.actor_id = film_actor.actor_id
GROUP BY actor.actor_id
SELECT a.actor_id id, a.first_name nombre
FROM actor a
LEFT JOIN film_actor fa
ON fa.actor_id = a.actor_id
WHERE fa.film_id IS NULL;
FALLA, no se puede referenciar al alias films_count
...pero podriamos solucionarlo con alias
SELECT actor.actor_id, COUNT(film_actor.film_id) AS films_count
FROM actor
INNER JOIN film_actor
ON actor.actor_id=film_actor.actor_id
WHERE films_count >= (SELECT AVG(film.film_id) FROM actor INNER JOIN film_actor ON actor.actor_id=film_actor.actor_id)
GROUP BY actor.actor_id
Consulta a revisar por el docente...
SELECT COUNT(*) films, inventory.store_id
FROM inventory
GROUP BY inventory.store_id
SELECT COUNT(*) cantidad, f.title pelicula, i.store_id id_tienda
FROM film f
INNER JOIN inventory i
ON f.film_id=i.film_id
GROUP BY f.title, i.store_id;