question

Given the following table Movie ( mID, title, year, director ) Reviewer ( rID, name, year, trustrating ) Rating ( rID, mID, rating ) 1. Write a query to list the average rating of all the movies for each year before a given year:1980 (and write NIL if there are no ratings for that movie) 2. Write a query to list the movies that have at least 1 numerical rating in common with a given movie:Dumbo I tried the following but no luck. Any guidance will be much appreciated. Thanks. SELECT Year, AVG(rating) FROM Ratings JOIN Movies WHERE Year < 1980; SELECT Year, AVG(rating) FROM Ratings JOIN Movies WHERE Year < 1980 in (WHERE Year is NULL); SELECT * AVG(rating) FROM Ratings JOIN Movies WHERE Year < 1980 in (WHERE Year is NULL); SELECT Year, AVG (rating) AS 'avg' FROM Ratings JOIN Movies GROUP BY Year WHERE Year < 1980; SELECT DISTINCT Year, AVG(rating) FROM Movies JOIN Ratings WHERE Year is NULL;