So I need help modifying my query to not just include one movie from each city, but all movies from each individual city. Here is my professor's question: Find the average rating of all movies playing in each city. Display the city name and the computed rating. Order the results descending by rating.
Here is my definition for each table:
CREATE TABLE Theatres (
Name varchar2(50) not null,
City varchar2(50) not null,
State varchar2(50) not null,
Zip number not null,
Phone varchar2(50) not null,
PRIMARY KEY (Name)
);
CREATE TABLE Movies (
Title varchar2(100) not null,
Rating NUMBER not null,
Length NUMBER not null,
ReleaseDate date not null,
PRIMARY KEY (Title),
CHECK (Rating BETWEEN 0 AND 10),
CHECK (Length > 0),
CHECK (ReleaseDate > to_date('1/January/1900', 'DD/MONTH/YYYY'))
);
CREATE TABLE ShownAt (
TheatreName varchar2(50) not null,
MovieTitle varchar2(100) not null,
PRIMARY KEY (TheatreName, MovieTitle),
FOREIGN KEY (TheatreName) REFERENCES Theatres(Name),
FOREIGN KEY (MovieTitle) REFERENCES Movies(Title)
);
Here is the Select statement I have so far:
SELECT AVG(m.rating) AS avg_rating,
t.city
FROM THEATRES t
JOIN SHOWNAT sa ON sa.theatrename = t.name
JOIN MOVIES m ON m.title = sa.movietitle
GROUP BY t.city
ORDER BY avg_rating DESC
I feel bad - I completely do not understand this homework - I've asked almost every question on this site - but I learn by example, so everything I see here I usually then understand and can apply to other questions. Thanks everyone for their help!