question

benanne avatar image
benanne asked

SQL query help pls

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;
mysqlsql query
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Your syntax is all over the place here. You have aggregates but no group by. You need to go back and discuss most of this with your professor because you are missing the basic fundamentals here.
0 Likes 0 ·

1 Answer

· Write an Answer
sjimmo avatar image
sjimmo answered
Check your join Example: FROM Purchasing.ProductVendor JOIN Purchasing.Vendor ON (ProductVendor.BusinessEntityID = Vendor.BusinessEntityID) See https://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.