List to be ordered from most to least; the creator who created the most family films will be at the top of the list

user_13046685 0
Viewed 120

I have a SQL homework question and hence I am not sure how to go about writing a query. Basically, this is the problem:

A research institution requests the names of all movie series’ creators, as well as the number of “Family Film” movies they have created (even if they created none). The institution wants the list to be ordered from most to least; the creator who created the most family films will be at the top of the list, and the one with the least will be at the bottom. Write a single query that gives this information, with useful columns.

These are the scripts to setup the table and data:

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)
)

This is the script to insert the data into the tables respectively

--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)

The output that I should see is this:

Creator - [Number of "Family Film" movies]
John Lasseter - 3
George Lucas - 0
John Tolkien - 0
Bruce Gellar - 0

However, I am not sure how I can use the joins to connect the data and the counts together. This is my attempted query:

select count(ms.genre_id), g.Genre_name, c.first_name + ' ' + c.last_name as [Creator]
from Movie m
inner join Movie_series ms on ms.movie_series_id = m.movie_series_id
inner join Creator c on ms.creator_id = c.Creator_id
left join Genre g on ms.genre_id = g.Genre_id and g.Genre_name = 'Family Film'
group by ms.genre_id, c.first_name, c.last_name, g.Genre_name

Any help would be greatly appreciated. Thanks in advance.

tsql sql-server sql
2 Answers

You want outer joins not inner joins. Start with creator and left join movie_series, movies and genre. By using left joins you make sure that all creators are in the result -- a left join keeps all of the rows from the left relation.

And you only want to group by the creator, not by genre. Make sure however to include creator_id in the GROUP BY clause as only using the name columns could merge two actually different creators that just share one name.

Simply use an ORDER BY clause to sort the result by the count.

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;

db<>fiddle

user_96614240

You need to start off with Creator as the driving row which you always have, and nest the Movie join inside a left join of Movie_series.

Since you always want that Genre name, you need to effectively cross join that one row first, then add a further join condition to the Movie_series join

Select count(ms.genre_id), g.Genre_name, c.first_name + ' ' + c.last_name as [Creator]
from
    Creator c
    inner join Genre g on g.Genre_name = 'Family Film'
    left join Movie_series ms
        inner join Movie m on ms.movie_series_id = m.movie_series_id
    on ms.creator_id = c.Creator_id and ms.genre_id = g.Genre_id
group by c.creator_id, c.first_name, c.last_name, g.Genre_name
order by count(ms.genre_id) desc;
user_148689970
Related