In this assignment, you will work with the OracleTM 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.
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.
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:
|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 The 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 The Empire Strikes Back from the middle
of the movie on till the end, the record of it will look like this:
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))
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;Similarly you can select all rows in other tables (PLANETS, TIMETABLE).
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;
You should write queries for the following situations: