CSE 444 Homework 1

    Objectives:

            To understand and be able to write simple SQL queries, without updates.
    Reading Assignments:
            6.1 - 6.4
    Number of points:
            100
    Due date:
            Monday, October 14th.
    Tools for the assignment
    For this assignment, you will need the database  we have created. This database is available on ISQL01 and is called cse444_hwk.
    Please read the instructions on how to run your first SQL query here: Using ISQL01 server
    If you chose to work in your own database, here are the scripts to create the tables and load the data.
     
    The database has the following schema:

    MOVIES(MovieCode, Title, Rating, Length)
    SHOWS(TheaterCode, MovieCode, Time)
    THEATERS(TheaterCode, Name, Address, Phone)
    BELONGS(DistrictCode, TheaterCode)
    DISTRICT(DistrictCode, Name)

    DISTRICTS refer to Seattle Districts (U District, Downtown, etc).  THEATERS  lists movie theaters in Seattle, while MOVIES contains the movies currently showing (notice that the data is a bit old: don't use it to plan your next weekend).  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. The CSE444_HWK database on ISQL01 has been loaded with real data from   http://www.moviefone.com/, about two years ago.  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.

    Make sure to complete the readings before you attempt to do the homework.

    For each of the questions below you have to turn in 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 Downtown. 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 Downtown, 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: