Advertisement
AliaksandrLet

v4

Jun 19th, 2022 (edited)
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     c.NAME AS name_category,
  3.     COUNT(fc.film_id) AS total_films
  4. FROM
  5.     category AS c
  6.     LEFT OUTER JOIN film_category AS fc ON fc.category_id = c.category_id
  7.     LEFT OUTER JOIN movie AS m ON m.film_id = fc.film_id
  8.     LEFT OUTER JOIN film_actor AS fa ON fa.film_id = m.film_id
  9. WHERE
  10.     fa.actor_id IN (
  11.         -- Актеры снимающиеся более 7 раз в фильмах после 2013 года
  12.         SELECT
  13.             film_actor.actor_id
  14.         FROM
  15.             movie
  16.             INNER JOIN film_actor ON film_actor.film_id = movie.film_id
  17.         WHERE
  18.             movie.film_id IN (
  19.                 -- Фильмы снятые после 2013
  20.                 SELECT
  21.                     movie.film_id
  22.                 FROM
  23.                     movie
  24.                 WHERE
  25.                     release_year > 2013
  26.             )
  27.         GROUP BY
  28.             film_actor.actor_id
  29.         HAVING
  30.             COUNT(movie.film_id) > 7
  31.     )
  32. GROUP BY
  33.     name_category
  34. ORDER BY
  35.     total_films DESC,
  36.     name_category
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement