To understand and be able to write simple SQL queries, including updates.
6.1 - 6.6, 7.1
For this assignment, you will use your SQL Server account on IISQLSRV. Login using the SQL Server authentication, and your normal UW login name. Your password has been mailed to you separately. We have uploaded the database for you, in your account. As you work on this assignment you need to update the database content, and even to alter the database schema. If you want to recover the original database, please upload it from the textfiles available here, using SQL Server's Import feature.
As an alternative to SQL Server, you are allowed to use a
different database, e.g. MySQL. In that case, you need to load the
database using the textfiles available
here. There will be less support (or
none at all) for other database systems.
Prepare one text file containing, for each of the questions below, the SQL query (or queries) and the answer returned by the database server after you run that query. Write your name at the beginning of the file. Call the file firstnameLastname.txt (e.g. if your name is John W. Smith then call the file johnSmith.txt; but remember to include John W. Smith at the beginning of the file). Then email the file to Michael.
Consider a database wtih the following schema:
movies(MovieCode, Title, Rating, Length)
movies2theaters(TheaterCode, MovieCode, MovieTime)
theaters(TheaterCode, Name, Address, Phone)
districts refer to Seattle Districts (U District, Center City, 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). movies2theaters lists which theaters show what movies and when. theaters2districts tells us what theaters can be found in which districts. theaters2districts is NOT one-many: some theaters list themselves as belonging to several districts. The database currently loaded on IISQLSRV has been populated 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. Recall that for each of the questions below you have to turn in two things: