Introduction

In this assignment, you will work with the Oracle^TM DBMS to perform SQL queries. You will be accessing a database and designing queries to retrieve data from that database.

Oracle is one of the most widely used DBMS programs in the world. It runs on virtually every kind of computer, from PCs and Macintoshes, to minicomputers and giant mainframes. Oracle uses a relational data model. Oracle provides a form of SQL as its query language. Oracle uses an interactive interface for generating queries (SQL*PLUS) that will perform many useful functions for you.

Getting Started

Oracle can be accessed from either Unix or DOS (PC). Click here to find documentation on how to access Oracle and how to work in SQL*PLUS. Note that for this assignment you will need to log in to Bulldog or one of the other mainframes in order to work.

Design of the Database

The database you will query is a Star Wars Trilogy database. You are to write queries for the database and then spool your queries along with the results to a file. You should submit this file containing your results.

The Star Wars Trilogy database contains 3 tables as follows:

The tables have been created with the following script:

CREATE TABLE CHARACTERS (
	NAME VARCHAR2(20) PRIMARY KEY,
	RACE VARCHAR2(20) NOT NULL,
	HOMEWORLD VARCHAR2(20) NOT NULL,
	AFFILIATION VARCHAR2(20) NOT NULL)

CREATE TABLE PLANETS (
	NAME VARCHAR2(20) PRIMARY KEY,
	TYPE VARCHAR2(20) NOT NULL,
	AFFILIATION VARCHAR2(20) NOT NULL)

CREATE TABLE TIMETABLE (
	CHARACTERNAME VARCHAR2(20),
	PLANETNAME VARCHAR2(20),
	MOVIE NUMBER(4),
	TIMEOFARRIVAL NUMBER(4) NOT NULL,
	TIMEOFDEPARTURE NUMBER(4) NOT NULL,
	FOREIGN KEY(CHARACTERNAME) REFERENCES CHARACTERS(NAME),
	FOREIGN KEY(PLANETNAME) REFERENCES PLANETS(NAME),
	PRIMARY KEY(CHARACTERNAME, PLANETNAME, MOVIE))

Accessing the Database

The database can be accessed from the SQL prompt as follows:

To select all rows in the table CHARACTERS you can use the query:

       SQL> SELECT * FROM bhoi0001.CHARACTERS;

You can copy the data from each of these tables to tables you create in your own tablespace. For example, to copy the data from the PLANETS table to a table named MYPLANETS in your own tablespace execute the following query:

       SQL> CREATE TABLE MYPLANETS AS SELECT * FROM bhoi0001.PLANETS;

To view all the information about a table use the query:

       SQL> DESC tablename;

The Queries

You should write queries for the following situations:

1. Find the names of all the rebels affiliated characters whose homeworlds are unknown.
2. Show the distinct affiliations and the number of planets associated with each of them.
3. Find names of planets visited by empire affiliated humans (show each planet only once).
4. List each character name and the number of planets that the character has not visited yet.
5. List for each character and for each neutral planet, how much total time did the character spend on the planet.
6. 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. For example, the human Han Solo has visited desert planet Tatooine in the first movie then the output should look like:
   

   CHARACTERNAME	MOVIE
   Han Solo	2

7. On which planets and in which movies has Luke been at the same time on the same planet as Darth Vader.
8. Find distinct names of planets which were visited by characters who did not appear in all 3 movies.
9. Show the name of characters who were never on their homeworlds during the movies. Also show the distinct planets these characters have visited. Do not consider those characters whose homeworlds are not known.
10. Find names and affiliations of characters who have either been to planet Bespin or planet Tatooine, but not both. Sort the output by affiliations of these characters.
11. Find the names of humans who have been to any rebel-affiliated planet more than once.
12. Find the names of characters that have been on all the neutral planets.
13. Find the character who has played the longest part in all the three movies taken together, where the length of the part of a character is the sum of difference between time of departure and time of arrival for all planets for all movies. Also show the amount of part that character played.

What to Turn in