Untitled Document

Case

Judy, who is new to town, has decided to join the local chapter of a woman’s volunteer club that undertakes a number of charitable events and outreach programs. It has approximately 150 current members as well as 30 provisional members (a new member before she becomes a full member). The club would like to create a single table database that could be used to maintain a membership directory and print mailing labels. This table should include each member’s first name, last name, street address, city, state, zip code, phone, fax, e-mail address, the community in which she lives, whether she receives the news letter via regular mail or e-mail, and whether she is a provisional member.

The club wants to be able to query this table to

Create a list of those members who receive the newsletter by mail.
Make an alphabetic list of all members' first name, last name and e-mail address for those members with email addresses.
Make a special directory for those members who live in the communities HG, AG, CLI, and CC (this should be in order by community and within community by last name).
Make a master directory that lists the current members in alphabetical order and followed by the provisional members in alphabetical order.

Create the single table and write the preceding queries.

Answer

The following single table database could be used to maintain information about the members of the club.

CREATE TABLE club_member (
	 member_number 		            CHAR(5) NOT NULL,
	 first_name                  VARCHAR(12),
	 last_name                   VARCHAR(12),
	 community_name		            VARCHAR(12),
	 street_address              VARCHAR(50),
	 city                        VARCHAR(12),
	 state                       CHAR(2),
	 zip_code                    CHAR(5),
	 phone_number                VARCHAR(12),
	 fax_number                  VARCHAR(12),
	 email_address               VARCHAR(25),
	 newsletter_delivery_method  VARCHAR(10),
	 member_type                 VARCHAR(12),
		   PRIMARY KEY (member_number));

Create mailing labels for those members who receive the newsletter by mail.

SELECT first_name, last_name FROM club_member
  WHERE news_letter_delivery_method = 'Mail';

Make an alphabetic list of all members first names, last names and e-mail address for those members with email addresses.

SELECT first_name, last_name, email_address 
			FROM club_member
			WHERE email_address <> NULL
     ORDER BY last_name, first_name;

Make a special directory for those members who live in the communities HG, AG, CLI, and CC (this should be in order by community and within community by last name).

SELECT community_name, last_name, first_name
				FROM club_member
				WHERE community_name IN ('HG','AG','CLI','CC')
				   ORDER BY community_name, last_name, first_name;

Make a master directory that lists the current members in alphabetical order and followed by the provisional members in alphabetical order.

SELECT member_type, last_name, first_name
				FROM club_member
				   ORDER BY member_type, last_name, first_name;