CSE 444 Homework 3

Objectives:

        To understand and be able to manipulate SQL queries
Reading Assignments:
        5.1-5.9
Number of points:
        100
Due date:
        Monday, October 30th.
Tools for the assignment
        For this assignment, you will need the database we have created. This database is available on ISQL01 and is called sqldb Please read the instructions on how to run your first SQL query here:  http://www.cs.washington.edu/education/courses/cse444/CurrentQtr/sqlhelp.html
 
Consider the schema below:
DISTRICTS refer to Seattle Districts (U District, Center City, etc).  THEATERS  lists movie theaters in Seattle, while MOVIES the movies currently showing.  SHOWS lists which theaters show which movies at what times.  BELONGS tells us what theaters can be found in which districts.  BELONGS is NOT one-many: some theaters list themselves as belonging to several districts (that is how the database I downloaded was setup, I don't know why). The SQLDB database on ISQL01 contains five relations corresponding to the entity sets and the relationships in this E/R diagram, and has been loaded with real data taken from   http://www.moviefone.com/ (on Thursday, 10/18/00; some showtimes where missing so I inserted 8pm -- don't plan your weekend based on that.).  Before you start it is helpful to play with  SQL Server browsing the database and getting familiarized with the schema, the data, and SQL Server. Note that the time in SHOWS is actually a time/date SQL type. The date is 10/20/2000 in all entries (i.e. only the time is meaningful).
For each of the questions below you have to show two things:
    a. A SQL query that answers the question.
    b. The results you obtained by running that query on the database.

     
  1. [5 points] Find all movies titles rated "PG-13".
  2. [5 points] Find all  movies that are shown after 10:00.  Print their titles, length, and ratings.
  3. [5 points] Find all theaters that show both "Digimon: The Movie" and "Bring It On": print their names and phone numbers.
  4. [10 points] Find theaters that list themselves both under the University District and under Center City District. print their names and addresses.
  5. [10 points] Find theaters that show only the movie "Dancer In The Dark" and no other movie.
  6. [10 points] Find movie titles that are shown only after 7pm.
  7. [10 points] Find theaters that list themselves under the "CAPITOL HILL" District but not under the "THE U DISTRICT".
  8. [10 points] Find movies that start between 6:00-7:00 in Center City, or start between 8:00-9:00 in the University District.
  9. [10 points] For each theater find the minimum and the maximum length of the movies it shows.
  10. [10 points] For each movie find the latest time it shows at any theater.
  11. [10 points] Find all theaters showing at least four distinct movies.
  12. [5 points] Please answer the following questions
    1. How long did it take you to complete this assignment?
    2. What did you like the best about this assignment?
    3. What did you like the least about this assignment?
    4. What helped you learn the best in this assignment?
    5. What distracted from your learning in this assignment?