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 and 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 Personal Address Book 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 Personal Address Book  database contains four tables as follows:

The tables have been created with the following script:

CREATE TABLE PEOPLE (
	ID NUMBER PRIMARY KEY,	
	NAME VARCHAR2(50) NOT NULL,
	CATEGORY VARCHAR2(15) CHECK ( CATEGORY IN ( 'FAMILY', 'FRIENDS', 'COLLEAGUES', 'BUSINESS', 'OTHER' ) ),	
	BIRTHDATE DATE,
	MET_ON_DATE DATE );

CREATE TABLE TELEPHONE (
	ID NUMBER PRIMARY KEY,	
	PEOPLE_ID NUMBER NOT NULL,
	NUMBER_STRING VARCHAR2(20) NOT NULL,
	TYPE_OF_NUMBER VARCHAR2(10) CHECK ( TYPE_OF_NUMBER IN ( 'HOME', 'OFFICE', 'CELL' ) ),
	FOREIGN KEY(PEOPLE_ID) REFERENCES PEOPLE(ID) );

CREATE TABLE ADDRESS (
	ID NUMBER PRIMARY KEY,	
	PEOPLE_ID NUMBER NOT NULL,
	STREET VARCHAR2(50)NOT NULL,
	CITY VARCHAR2(20) NOT NULL,
	STATE VARCHAR2(20) NOT NULL,
	ZIPCODE VARCHAR2(10),
	TYPE_OF_ADD VARCHAR2(10) CHECK ( TYPE_OF_ADD IN ( 'HOME', 'OFFICE' ) ),
	FOREIGN KEY(PEOPLE_ID) REFERENCES PEOPLE(ID) );

CREATE TABLE APPOINTMENT (
	ID NUMBER PRIMARY KEY,	
	PEOPLE_ID NUMBER NOT NULL,
	ADDRESS_ID NUMBER NOT NULL,
	APP_DATE DATE NOT NULL,
	FOREIGN KEY(PEOPLE_ID) REFERENCES PEOPLE(ID),
	FOREIGN KEY(ADDRESS_ID) REFERENCES ADDRESS(ID) );

Accessing the Database

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

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

       SQL> SELECT * FROM joshi031.PEOPLE;

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 PEOPLE table to a table named MYPEOPLE in your own tablespace execute the following query:

       SQL> CREATE TABLE MYPEOPLE AS SELECT * FROM joshi031.PEOPLE;

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 people whose ZIPCODE is not known.
2. Show all the distinct telephone numbers and the type of telephone number associated with each of them.
3. Find the names of all people marked as friends who do not have a cellphone number.
4. Find all the appointments that have dates same as that of birthday of some family member.
5. Calculate the total time spent in appointments with business contacts.
6. Name the persons (and list them by category) with whom an appointment in Minneapolis is scheduled.
7. List all the  business contacts with whom there is an appointment at their home.
8. Find all the contacts reachable by cellphone and group them by category.
9. List all the people whose address is not known.
10. Find the people who have more than one home phone number.
11. Find the people who have homes in Minneapolis and St. Paul.
12. List all of the people who have a cell phone number, an office number and a home number in the database.

What to Turn in