Assignment

Contents:

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

  • Overview

    Topic: Databases
    Related Reading: Ch. 12 and notes
    Due:

    Internet Requirements

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

    Practice Problems

  • Exercise 34 of Ch. 10 (p. 396)

  • Exercise 35 of Ch. 10 (p. 396)

  • Exercise 36 of Ch. 10 (p. 396)

  • Do the practice problems and tutorial involving the CIA country information.
    Here is a brief overview of the table's contents.
    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 an overview of the tables.
    Here are sample answers to the tutorial.

  • Problems to be Submitted (20 points)

    1. (6 points)
      For this problem, consider the tables Movie, Customer and Rents from pp. 385-387 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 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.

    4. (4 points)
      At the end of Ch. 12 there are a series of "Thought Questions" (p. 397). Pick any one question to answer. 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.
    Overall, please type your answers to all of the problems in a single document to be submitted electronically. Please see details about the submission process.

    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 (3c) 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:

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

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

  • Last modified: 5 November 2002