1

Given database of the website "eMovies" that manage data about cinema movies.

Write in SQL query that finds the names and the year of the movies that have at least 5 actors.

enter image description here

My attempt:

The idea: to do a natural joint between "Movies" and "ActorsMovies" tables, and then to count the rows with the same MovieID, and then to check if count>5

I succeded to count the rows thank's to @Phil's advice, but still I need the name of the movies and the year,

Here is my start:

SELECT DISTINCT Year, MovieName
FROM Movies, ActrorsMovies
SELECT Count(ActorsMovies.MovieID) 
FROM ActorsMovies
GROUP BY ActorsMovies.MovieID
HAVING (((Count(ActorsMovies.ActorID))>5)); 

This code does not work


EDIT: another attempt

 SELECT DISTINCT Movies.Year, Movies.MovieName
 FROM Movies, ActorsMovies
 GROUP BY ActorsMovies.MovieID
 HAVING Count(ActorsMovies.ActorID)>5;
3
  • You need a JOIN condition(among other things) in there otherwise you get a cardinal join(every row is multiplied with every other row from the second table)
    – Mihai
    Jan 3, 2016 at 18:01
  • 1
    Also, you shouldn't have deleted the previous question and posted this one.
    – Philᵀᴹ
    Jan 3, 2016 at 18:12
  • "At least 5" would translate to me as "> 4" or ">= 5" I think.
    – wBob
    Jan 3, 2016 at 22:55

2 Answers 2

1

You were close

SELECT M.MovieID, M.MovieName, M.Year 
  FROM Movies M
 INNER JOIN ActorsMovies AM 
         ON AM.MovieID = M.MovieID
 GROUP BY M.MovieID, M.MovieName, M.Year
HAVING COUNT(AM.ActorID) > 5

You could probably just use count(*) > 5

0
1

This code works in T-SQL and should work in Ms Access:

SELECT COUNT(M.MovieID) as 'Count', M.MovieName, M.Year FROM Movies M
INNER JOIN ActorsMovies AM ON AM.MovieID = M.MovieID
INNER JOIN Actors A ON AM.ActorID = A.ActorID
GROUP BY M.MovieID, M.MovieName, M.Year
HAVING COUNT(M.MovieID) > 5

I also recommend not to use Year as a field name because it's a reserverd keyword in Ms Access and may cause problems: https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx

1
  • This probably works but join to Actors is not necessary
    – paparazzo
    Jan 3, 2016 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.