Need help with some Oracle SQL Queries

Question

Here is the question posed by a professor: Find the minimum and maximum length of movies playing in each city.

And here is how I have my tables structured:

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)
);

I've tried a few different queries, but keep getting issues. Here is what I have:

SELECT MIN(Movies.Length), MAX(Movies.Length), Theatres.Name
 FROM Theatres, Movies, ShownAt 
WHERE ShownAt.TheatreName = Theatres.Name AND 
ShownAt.MovieTitle = Movies.Title AND 
Theatres.City IN (SELECT UNIQUE City FROM Theatres);

Anybody see anything wrong? Thanks for the help!

Answer 1

I believe this would do the trick:

SELECT T.City, MIN(M.Length) AS MinLength, MAX(M.Length) AS MaxLength
  FROM Movies   AS M
  JOIN ShownAt  AS S ON S.MovieTitle  = M.Title
  JOIN Theatres AS T ON S.TheatreName = T.Name
 GROUP BY T.City

Answer 2

You were pretty close I think. Just missing GROUP BY

SELECT 
     MIN(Movies.Length) AS Shortest, 
     MAX(Movies.Length) AS Longest, 
     Theatres.City 
FROM Theatres
JOIN ShownAt ON ShownAt.TheatreName = Theatres.Name
JOIN Movies ON ShownAt.MovieTitle = Movies.Title
GROUP BY Theatres.City