MCS-274 Lab 2: Object-Relational SQL (Spring 2007)

Due March 19, 2007

In this lab, you will use the Oracle database to try out object-relational SQL queries on a suitably modified version of the movie database. As with the prior lab, all the work for this lab can be most easily done on one of the MCS lab PCs running Linux, since we have the Oracle SQL Developer client software installed on those machines. However, you could also download and install this software on your own machine and still connect to our server, thebe.gac.edu.

For this lab, you again need not write a true lab report. Instead, just turn in your SQL queries and the results they produced. You can copy these in SQL Developer and paste them into whatever program you are using to write your lab report, such as OpenOffice.org Writer or emacs. I recommend using the Run Script button and copy the results from the Script Output tab. Important: Check your results for reasonableness. If in any case you have reason to doubt that the result is correct, but haven't been able to track down what is wrong with your SQL query or fix it, you should indicate what is wrong with the result.

To create the database, run the ORmovies.sql script.

Be sure to answer each question using a single SQL query that directly produces the desired result. It is not acceptable to issue one query, manually extract some information from the result, and use that to type in a second query, which produces the desired result.

Your queries should not produce results with duplicate rows. In some cases this may require that you use the keyword "distinct" after "select".

Try not to make your queries unnecessarily complex. In particular, don't mention a table if it isn't needed to find the result.

  1. What movies were made in the years from 1985 through 1995? For each give the title and year made.
  2. Again, list the movies from 1985 through 1995, but this time include the first and last names of the directors as well.
  3. Who acted in 'The Big Easy'? (For each, list the first and last names.)
  4. What actors are listed as having acted in two or more movies?
  5. What movies did Claude Rains act in?
  6. List all those combinations of a person's first and last names and a movie's title such that the person directed the movie and also acted in that same movie.
  7. List all those combinations of a person's first and last names and a movie's title such that the person directed the movie but did not act in it.