Assignments | Class Photo | Course Home | Schedule & Lecture Notes | Submit | Tutoring Hours

Saint Louis University

Computer Science 140
Introduction to Computer Science

Michael Goldwasser

Spring 2008

Dept. of Math & Computer Science

Assignment 09

Databases

Contents:


Overview

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


Internet Requirements

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 Problems


Problems to be Submitted (20 points)

  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.

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)

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

CSCI 140, Spring 2008
Michael Goldwasser © 2008
goldwamh at our university domain

Last modified: Monday, 14 April 2008
Assignments | Class Photo | Course Home | Schedule & Lecture Notes | Submit | Tutoring Hours