Contents:

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

Topic: Databases
Related Reading: Ch. 12 of text as well as our lecture notes.
Due: 8pm Tuesday, 22 April 2008

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

For consistency we ask that you use the "Oracle" database engine, which can be selected from the pulldown menu near the top-right corner of all sqlzoo pages (the syntax varies slightly between database engines and the dataset may very on the different servers for this site).

• Practice using the BBC 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. 389-391 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. (8 points)

   Consider the bbc 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 'Belarus'

   2. Give the name, population, and gdp of all countries that lie in Europe or Africa.

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

      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.