Part -B-

Movie ( movieId, movieName, categoryId, duration, productionDate) Theater( theaterId,theaterName, city, ticketPrice) Category (categoryId, categoryName) ShownAt(theaterId, movieId, startDate, endDate)

1. a) Find the name of all theaters that have shown a movie called Harry Potter.
2. b) Find the name of the shortest movie. (i.e. Find the name of the movie with the minimum duration)
3. c) For each category, find the number of movies that have a duration greater than the average duration of all movies. List the categoryId and the number you calculated.
4. d) Find the name of the oldest (the movie with the smallest ProductionDate) and the latest (the movie with the largest ProductionDate) movie in the database.
5. e) Find the number of the movies that have been shown at the theater with the cheapest ticketPrice.
6. f) List movieName and categoryName for all movies produced in 2002. (Please remember that the ProductionDate is a date.)
7. g) List MovieName of all movies that were shown for longer than 30 days.
8. h) Find the number of different categories in the movie table.
9. i) What is the sql*plus command that is used to show the structure of the theater table.
10. j) Assume that Movie, Theater and Category tables are already created. Please create the

Shown At table.

10. • TheaterId must be number with 4 digits Foreign Key referencing the Theater Table,
    • MovieId must be number 6 digits Foreign Key referencing the Theater Table,
    • StartDate and EndDate must be date data type.
    • Dont forget to declare the Primary Key.
11. k) Insert a new tuple into the Movie table with the following values:
    • MovieId: 101
    • MovieName: Honey, CategoryId: 12,
    • Duration: 100, ProductionDate: 02-JUN-2003
12. l) Change the name of the category 302 with Drama.
13. m) Delete rows of the Theater table that has the minimum ticketPrice.
14. n) Delete all rows that have longest duration in the Movie table.
15. o) Increase the ticketprice %10 of the Theater that is showing the movie called Lord of the Rings