Oracle SQL Question

Question

So I've been doing this all night - can't quite understand my homework, and sadly my professor is unavailable on the weekend. Here it goes: Find the titles of the newest movies shown in each city. Display the city name and the newest movie title ordered by city name and movie title.

Here are my table declares (and thank you to EVERYONE helping me out tonight, I owe you big time).

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 what I have so far (based on help from other StackOverflow members from earlier questions):

   SELECT m.title AS m_title, 
          t.city,
          m.title
     FROM THEATRES t
     JOIN SHOWNAT sa ON sa.theatrename = t.name
     JOIN MOVIES m ON m.title = sa.movietitle
 GROUP BY t.city, m.title
 ORDER BY m_title DESC

Obviously my issue is in declaring the newest movies - how can I account for this? I learn by example - Once someone shows me one way, I can apply it to everything else - Please help.

Answer 1

The question is really poor, because the only way to know what is "newest" is by the MOVIE.releasedate value, which will be the same for all cities because the release date should've been stored in the SHOWNAT table for each combination of city & movie.

This will list all the movies in the database, the newest movie at the top:

  SELECT m.title,
         t.city
    FROM THEATRES t
    JOIN SHOWNAT sa ON sa.theatrename = t.name
    JOIN MOVIES m ON m.title = sa.movietitle
ORDER BY m.releasedate DESC, m.title, t.city

To get only the newest movies, I'd use analytic functions (supported 8i+, but likely beyond the scope of your class):

  SELECT x.city, 
         x.title             
    FROM (SELECT t.city,
                 m.title,
                 ROW_NUMBER() OVER(PARTITION BY t.city
                                       ORDER BY m.releasedate) AS rank
            FROM THEATRES t
            JOIN SHOWNAT sa ON sa.theatrename = t.name
            JOIN MOVIES m ON m.title = sa.movietitle) x
   WHERE x.rank = 1
ORDER BY x.city, x.title

To get more than the topmost movie, change WHERE x.rank = 1 to:

WHERE x.rank <= 3

...to get the top three most recently released movies. Change to suit your needs.

Answer 2

Select m.Title, t.city 
from Theatres t 
inner join ShownAt s ON t.Name = s.TheatreName  
inner join Movies m ON s.MovieTitle = m.Title
order by m.ReleaseDate Desc, t.Name, m.Title 

Answer 3

you will use the MAX aggregate function to find the largest releasedDate value for each movie that was shown.

MAX will require a GROUP BY clause where you say which set to use when finding the max values.

Answer 4

Inner select to get the latest show for each city, outer select to get all the movies that match.

select m.title, t.city, m.releasedate
from theatres t, movies m, shownat s
where t.name = s.theatrename and m.title = s.movietitle 
and (t.city, m.releasedate) in (
  select t1.city, max(m1.releasedate)
  from theatres t1, movies m1, shownat s1
  where t1.name = s1.theatrename and m1.title = s1.movietitle 
  group by t1.city
);

Answer 5

'OVER PARTITION BY' does not work if two movies have the same newest releasedate because only one of them will be returned. Same goes if (n + 1) movies have the same date when 'WHERE x.rank <= n' is used.