Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 2nd, 2006, 10:59 AM
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
  #2 (permalink)  
Old September 2nd, 2006, 11:09 AM
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

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.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 09:12 PM.

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