SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table Genre
(
    Genre_id decimal(12) NOT NULL Primary Key
    , Genre_name varchar(64) NOT NULL
)

Create Table Creator
(
    Creator_id decimal(12) not null Primary Key
    , first_name varchar(64) NOT NULL
    , last_name varchar(64) NOT NULL
)

Create Table Movie_series
(
    movie_series_id decimal(12)  not null Primary Key
    , genre_id decimal(12) NOT NULL Foreign key references Genre(genre_id)
    , creator_id decimal(12) NOT NULL Foreign key references Creator(Creator_id)
    , series_name varchar(255) NOT NULL
    , suggested_price decimal(8,2) NULL
)

Create Table Movie
(
    Movie_id decimal(12) not null Primary Key
    , movie_series_id decimal(12) NOT NULL Foreign key references Movie_series(movie_series_id)
    , movie_name varchar(64) NOT NULL
    , length_in_minutes decimal(4)
)
--Inserting data to Genre Table
Insert into Genre (Genre_id, Genre_name)
values
(1, 'Fantasy'),
(2, 'Family Film'),
(3, 'Action spy')

--Inserting data to Creator Table
Insert into Creator (Creator_id, first_name, last_name)
values
(1, 'George', 'Lucas'),
(2, 'John', 'Lasseter'),
(3, 'John', 'Tolkien'),
(4, 'Bruce', 'Gellar')

--Inserting data to Movie_series Table
Insert into Movie_series (movie_series_id, genre_id, creator_id,  series_name, suggested_price)
values
(1, 1, 1, 'Star Wars', 129.99),
(2, 2, 2, 'Toy Story', 22.13),
(3, 1, 3, 'Lord of the Rings', NULL),
(4, 3, 4, 'Mission Impossible', 50.00)

--Inserting data to Movie Table
Insert into Movie (Movie_id, movie_series_id, movie_name,  length_in_minutes)
values
(1, 1, 'Episode I: The Phantom Menace', 136),
(2, 1, 'Episode II: Attack of the Clones', 142),
(3, 1, 'Episode III: Revenge of the Sith', 140),
(4, 1, 'Episode IV: A New Hope', 121),
(5, 2, 'Toy Story', 121),
(6, 2, 'Toy Story 2', 135),
(7, 2, 'Toy Story 3', 148),
(8, 3, 'The Lord of the Rings: The Fellowship of the Ring', 228),
(9, 3, 'The Lord of the Rings: The Two Towers', 235),
(10, 3, 'The Lord of the Rings: The Return of the King', 200),

(11, 4, 'Mission: Impossible', 110),
(12, 4, 'Mission: Impossible 2', 123),
(13, 4, 'Mission: Impossible III', 126),
(14, 4, 'Mission: Impossible - Ghost Protocol', 133),
(15, 4, 'Mission: Impossible - Rogue Nation', 131),
(16, 4, 'Mission: Impossible - Fallout', 147)
27 rows affected

SELECT c.first_name,
       c.last_name,
       count(g.genre_id)
       FROM creator c
            LEFT JOIN movie_series ms
                      ON ms.creator_id = c.creator_id
            LEFT JOIN movie m
                      ON m.movie_series_id = ms.movie_series_id
            LEFT JOIN genre g
                      ON g.genre_id = ms.genre_id
                         AND g.genre_name = 'Family Film'
       GROUP BY c.creator_id,
                c.first_name,
                c.last_name
       ORDER BY count(g.genre_id) DESC;


first_name
last_name
(No column name)


John
Lasseter
3

John
Tolkien
0

Bruce
Gellar
0

George
Lucas
0


Warning: Null value is eliminated by an aggregate or other SET operation.