CS468

Makeup SQL Assignment

from Ramakrisnan & Gehrke

INTRODUCTION

On World Wide Web there are more than 100 different web pages dedicated to Star Wars. However, the answer to a specific trivia question is sometimes difficult to find, precisely because of the abundance of information. In this assignment we will be creating a new Star Wars Trilogy database for the trivia lovers. We decided to begin with the time tables - who went where and when. We have started to input the information into the database. Your task will be to create SQL queries that answer some of the often asked questions (or questions that we found most fascinating and asked often)

A database has been created using Microsoft Access. The database currently contains :

Tables

Characters: contains information about the character's Name (primary key), Race (if known), Homeworld (if known) and Affiliation(rebels/empire/neutral/free-lancer).

Planets: contains information about the planet's Name (primary key), it's Type (gas/swamp/forest/handmade/ice/desert), and it's Affiliation(rebels/empire/neutral)

TimeTable: contains Character's Name, Planet's Name, Movie in which the character visited the planet and the time of arrival and departure from the planet.The primary key is Character's Name, Planet's Name and Movie. Movie 1 represents The Star Wars, Movie 2 represents Empire Strikes Back, Movie 3 represents Return of the Jedi. Each movie has been divided into 10 time chunks and these chunks are used to define time of arrival and departure. So that, if Darth Vader visited Bespin (Cloud City) in Empire Strikes Back from the middle of the movie till it's end, the record of it will look like this:

Character's Name Planet's Name Movie Time of Arrival Time of Departure
Darth Vader Bespin 2 5 10

 

THE TASK

Get acquainted with Access and create SQL queries that answer the following questions

  1. Find all characters that have been on all neutral planets
  2. Find distinct names of the planets visited by empire affiliated humans.
  3. For each character and for each neutral planet, how much time total did the character spend on the planet?
  4. On which planets and in which movies has Luke been at the same time on the planet as Darth Vader.
  5. Find humans that visited desert planets and droids that visited swampy planets. List the movies when it happened and the names of the characters. The output should be sorted by the movie and the character's name.

This is, at this point, a final list.

What to submit

  1. The results of the queries

    A copy of the SQL queries. As Access does not let you print out the SQL queries, you can cut and paste into your favorite text editor and save them that way.

To create a new query:

Here is a simple sample query that uses the Access notation for handling column names with quotes and spaces: 
SELECT T.[Character's Name]
FROM   TimeTable T
WHERE  T.[Planet's Name]='Star Destroyer';

May the force be with you.