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.

My issue is I am not sure of how to account for the NEWEST movies playing in each city. Not sure how to declare that in a SQL statement — Brian Lang
– Brian Lang, Commented Sep 12, 2010 at 3:15
You need to ORDER BY ReleaseDate, then choose the first N rows in the result to show the N newest movies. — David R Tribble
– David R Tribble, Commented Sep 12, 2010 at 3:37

OMG Ponies · Accepted Answer · 2010-09-12 03:24:19Z

4

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.

edited Sep 12, 2010 at 3:24

answered Sep 12, 2010 at 3:18

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

@Randy: If the movie didn't play in the city, then there won't be the combination of city & movie listing. Ordering by city or movie title first won't change that fact.

Brian Lang Over a year ago

This sounds right, albeit sounding a little advanced for where I am currently at in class, it produces the desired result, so I cannot complain - thanks so much. To be honest, I hadn't seen what you had done with Rank yet before, so that was quite cool to see how it was done :)

Brian Lang Over a year ago

Also, thanks for explaining everything with the rank and what not - At first your query seemed quite advanced for me, but now that I took some time to review it, I fully understand everything that is going on - I really appreciate it!

Randy · Accepted Answer · 2010-09-12 03:27:45Z

1

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.

answered Sep 12, 2010 at 3:27

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

OMG Ponies Over a year ago

The primary key is the movie title, so it's unlikely that there will be duplicate movies with different release dates.

Azhar · Accepted Answer · 2010-09-12 03:25:38Z

0

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

answered Sep 12, 2010 at 3:25

Azhar

20.7k39 gold badges149 silver badges211 bronze badges

Comments

david · Accepted Answer · 2010-09-12 04:19:46Z

0

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

answered Sep 12, 2010 at 4:19

david

1

Comments

dg157 · Accepted Answer · 2010-09-12 11:21:38Z

0

'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.

answered Sep 12, 2010 at 11:21

dg157

311 bronze badge

Collectives™ on Stack Overflow

Oracle SQL Question

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related