Thread: assignment
View Single Post
  #1 (permalink)  
Old September 2nd, 2006, 10:59 AM
pooja85jain pooja85jain is offline
Registered User
 
Join Date: Sep 2006
Location: indore, mp, .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default assignment

please help me solving the assignment
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:
PEOPLE: Contains information about the people in the address book. The primary key is the ID. NAME is mandatory and other information like CATEGORY (values can be 'FAMILY', 'FRIENDS', 'COLLEAGUES', 'BUSINESS', 'OTHER'), BIRTHDATE, date when you met the person (MET_ON_DATE) is optional.
TELEPHONE: Contains information about the telephone numbers of people in the PEOPLE table. The primary key is the ID. The PEOPLE_ID is a foreign key that refers to a row in the PEOPLE table and is mandatory. The NUMBER_STRING column stores the phone number and is mandatory. TYPE_OF_NUMBER is an optional field which can only take the following values - 'HOME', 'OFFICE', 'CELL'.
ADDRESS: Contains the addresses of the people stored in the PEOPLE table. The primary key is ID. The PEOPLE_ID is a foreign key that refers to a row in the PEOPLE table and is mandatory. The STREET, CITY, STATE and ZIPCODE are address related columns out of which STREET, CITY and STATE are mandatory. TYPE_OF_ADD can take values 'HOME'or 'OFFICE'.
APPOINTMENT: Contains information about the appointment scheuled with a person from the PEOPLE table. The primary key is the ID. PEOPLE_ID and ADDRESS_ID are foreign keys into PEOPLE and ADDRESS tables respectively and are mandatory. APP_DATE is a mandatory date column that stores the date and the time of the appointment.
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.



Reply With Quote