Contents:

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.  If you test your answer, you get to select the database engine.  If the default is not available, try another like Tilly(Oracle).
   

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

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:

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

Solutions to even practice book exercises
50.
SELECT *
FROM Customer
52.
SELECT Address
FROM Customer
WHERE Address LIKE  '%Lois Lane%'

Solutions to extra sample problems using the cia database

a.
SELECT name
FROM cia
WHERE region = 'Oceania'

b.
SELECT population
FROM cia
WHERE name = 'Poland'

c.
SELECT name
FROM cia
WHERE area < 50

d.
SELECT name
FROM cia
WHERE area < 50 AND population > 50000

e.
SELECT gpd/1000000000000
FROM cia
WHERE name = 'France'