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