COMP 150, Assignment 09, Spring 2002

Assignment 09

Overview

Topic: Databases
Related Reading: Ch. 9.1, 9.3 of [Br], as well as notes from lecture22.
Due: 8pm Thursday, 11 April 2002

Internet Requirements

You will need an internet connection to do most of this work.

Practice Problems

Question 1, 2, 3 and 4 of Ch. 9.3 (pp. 417-418 [Br])
answers appear in Appendix F

Do the tutorial involving the CIA country information.
Here is a brief overview of the tables from our lecture notes.
Here are sample answers to the tutorial.

Do queries 1a-1b, 2a-2c, and 3a-3e of the tutorial involving the movie database.
Here is a brief overview of the tables from our lecture notes.
Here are sample answers to the tutorial.

Problems to be Submitted (20 points)

(4 points)
Chapter 9 Review Problem 8 (p. 429 [Br])
(3 points)
Chapter 9 Review Problem 9 (p. 429 [Br])
(3 points)
Chapter 9 Review Problem 10 (p. 429 [Br])
(4 points)
Consider the cia database of countries. For each of the following queries, give a SQL statement which would retrieve the desired result:
1. Display the name, population, area and gdp of 'Afghanistan'
2. Give the name, population and gdp of all countries with region 'North America' or with region 'South America'.
3. Find each country which has a population of more than 1000 times its area (in square kilometers), displaying the name, area and population of such countries.
4. Give the name and area of each country of Africa with area that is either greater than 1 million square kilometers or less than less than 5000 square kilometers.
NOTE: you do NOT need to include the displayed results in your submission; only the SQL statement used.
(6 points)
Consider the movie database. For each of the following queries, give a SQL statement which would retrieve the desired result:
1. Display the title, score and number of votes for all movies from 1996 which received a viewer score of 7.65 or better.
2. Display the actors' names and ord values for the cast members of 'Caddyshack' who have ord values of 5 or less.
3. Find all movies that included either 'Brad Pitt' or 'Jennifer Aniston' in the cast, listing the title of the movie and the name of either Brad or Jennifer, whomever was in the movie.
NOTE: you do NOT need to include the displayed results in your submission; only the SQL statement used.

Overall, please place your answers to all of these questions in a single document to be submitted.

Extra Credit (4 points)

We want you to consider how you could find all movies which included both 'Dan Aykroyd' and 'Chevy Chase' in the cast.

It may seem that a simple change to your answer from (5c) might suffice for this question, but if you try such a simple change, you will probalby see that it does not work (do you understand why not?).

Instead, we will approach this in two stages:

(2 points)
First, we ask you to build a general sequence of operations, using the SELECT, PROJECT and JOIN operators from the first part of Ch. 9.3. Your strategy for answering this query should be to first build a new table which contains all movies which include Dan Aykroyd. (better yet, rely just on producing the list of those movieIDs).

Now you should be able to join this new table with the original information to find which of those selected movies also included Chevy Chase.

Please submit the full sequence of operations which would lead to the desired result.

(2 points)
We would like you to give a single SQL statement which can be used to answer this query. To do so, you will need to use features of the language which were not covered in lecture or in the text.

Specifically, you can embed a

   "(SELECT ... FROM ... WHERE ....)"

clause as part of a larger SQL statement.

Fortunately, as this is extra credit, we expect that you will learn it on your own.

Some discussion of nested selects is found here.

A self-test tutorial is found here.

Answers to the self-test are found here.

comp150 Class Page
mhg@cs.luc.edu