How to make a start on this SQL query homework? [closed]

A movie streaming app is currently being developed by a start-up. A movie can belong to one or more genres. Given below is the table definitions for movies and genres:

TABLE genres 
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE movies 
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE moviesGenres 
  movieId INTEGER REFERENCES moviesid
  genreId INTEGER REFERENCES genresid
  PRIMARY KEY(movieId, genreId)

A particular search parameter requires all movies which belong to more than two genres and none of the genres are ‘Comedy’.

Write a query for the above criteria that returns:

The movie’s name. The number of genres the movie belongs to.

write the query considering all scenarios: I have written this query but i am not able to satisfy one test case:

select m.name, count(mg.genreId)
from movies m
join 
moviesgenres mg
on m.id=mg.movieId
where mg.genreId not in (select id from genres where name='Comedy')
group by m.id,m.name
HAVING count(mg.genreId)>2

Mentioned below is the test case that I am missing:

Some movies have more genres, some movies are Comedy: Wrong answer

Answer

This solution uses a subquery and a variable (tested in sql server):

Declare @comedyGenreID int;
Select @comedyGenreID = id from genres where name='Comedy' 

Select name, genres
From (select m.name, count(g.id) as genres
      from movies m
      inner join moviesgenres mg on mg.movieid = m.id
      inner join genres g on g.id = mg.genreid
      where mg.genreid <> @comedyGenreID
      groupby m.name) as sq
Where genres > 2

I guess you could decide to not use the variable and use the ‘Comedy’ string directly in the subquery as well, like this:

where g.name <> 'Comedy'