Contents:

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

Topic: Databases
Related Reading: Ch. 9.2 of text as well as our leccture notes.
Due: 8pm Monday, 4 December 2006

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

• Page 408 has a set of Questions and Exercises for Chapter 9.2, with solutions in the back of the book.

• 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. (8 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.

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

3. (6 points)

   Do Chapter Review Problem 15 of Chapter 9 (p. 429), however your answers should be expressed using SQL statements.

4. 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.