Contents:

• Overview
• Internet Requirements
• Practice Problems
• Problems to be Submitted
• Extra Credit

Topic: Databases
Related Reading: Ch. 12 of Dale/Lewis, as well as our leccture notes.
Due: 8pm Tuesday, 23 November 2004

You will definately need an Internet connection for completing the assignment as well as submission.

• Exercise 50 of Ch. 10 (p. 414)

• Exercise 51 of Ch. 10 (p. 414); answer in back of text

• Exercise 52 of Ch. 10 (p. 414)

• Practice using the CIA country database from sqlzoo.net.

  • Here is a brief overview of the table's contents.
  • Here are some sample queries (answers are embedded)
  • Here is the tutorial
  • Here are sample answers to the tutorial.

• Practice using the Movie database from sqlzoo.net.

  • Here is an overview of the table's contents.
  • Here are some sample queries involving joins (answers embedded).
  • Do queries 1a-1b, 2a-2c, and 3a-3e of the tutorial.
  • Here are sample answers to the tutorial.

1. (6 points)

   For this problem, consider the tables Movie, Customer and Rents from pp. 401-403 of the text (not to be confused with the Internet movie database from the "Gentle Introduction to SQL" which we will use in later questions).

   1. What would the output be of the following SQL query:

      SELECT Title,MovieId
      FROM  Movie
      WHERE Rating = 'R' OR 
            Genre like '%drama%'
      		

   2. What would the output be of the following SQL query:

      SELECT Name, MovieId, DateRented
      FROM  Customer,Rents
      WHERE Customer.CustomerID = Rents.CustomerID
      		

   3. What would the output be of the following SQL query:

      SELECT Title, DateRented
      FROM  Movie,Customer,Rents
      WHERE Movie.MovieId = Rents.MovieId AND
            Customer.CustomerID = Rents.CustomerID AND
            Customer.Name = 'Randy Wolf'
      		

2. (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.

3. (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 title and year for all movies directed by 'Mel Brooks' (you should find 9 such movies)

   3. Display the title for all movies for which 'Mel Brooks' was both the director as well as a cast member (you should find 6 such movies)

   NOTE: you do NOT need to include the displayed results in your submission; only the SQL statement used.

4. (4 points)

   Answer Thought Question 3 of Ch. 12 (p. 415). The length of your answer should be appropriate for the question, however I envision answers in the range of 1/2-page to 1-page.

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

   We will approach this in two stages:

   1. (1 points)

      The first goal, using the techniques we already know, is to create a table which is a simple list of all movieIDs for those movies which included Dan Aykroyd.

   2. (3 points)

      We would like you to give a single SQL statement which can be used to answer this query about movies which include both Dan and Chevy.

      To do so, you will need to use features of the language which were not covered in lecture or in the text. Specifically, in SOME database engines you can embed the

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

      clause from the previous answer as part of a larger SQL statement.

      Not all database engines support nested select statements. In particular MySQL does not support this functionality, yet MySQL appears to be the default engine used by the software. You will likely see a warning about this in which case you should pick a different engine. There should be a dropdown menu near the top right labeled "Pick an engine" which allows you to pick which database engine you wish to use.

      Fortunately, as this is extra credit, we expect that you will learn about nested select statements 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.