Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 2nd, 2006, 10:59 AM
Registered User
 
Join Date: Sep 2006
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.



 
Old September 2nd, 2006, 11:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

You want us to do your homework for you?

Did you even bother to at least try some of the assignment?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
please help me out with this assignment... vaidehi BOOK: Beginning Linux Programming, 4th Edition ISBN 978-0-470-14762-7 3 September 10th, 2008 03:57 PM
Programming assignment. Neeko C++ Programming 16 December 18th, 2005 09:48 AM
Java assignment greeny Java Basics 0 November 29th, 2005 08:29 AM
Java assignment greeny JSP Basics 0 November 29th, 2005 08:26 AM
Definite Assignment Ibn_Aziz C# 0 December 21st, 2003 04:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.