SPRING 2009 CSE200 QUIZ5

Name _________________________________________ Seat# __________

Lab Day/Time _______ Friday 1:30pm _______ Friday 3:30pm

Instructions:

· Put away all books, papers, and calculators. Turn off all beepers and cell phones.

· Answers must be legible or they will be marked incorrect.

· Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query… see the below reminders for some (but possibly not all) hints.

· REMEMBER that all the data is not shown. Be sure your queries will work with additional records.

ACCESS REMINDERS

· No quotes around yes/no field values.

· Use # signs around dates.

· Be sure to use the word Like and quotes on criteria that have wildcard characters.

· No table name is used when creating an expression.

· Use square brackets around the table/query name and square brackets around the field name with an exclamation point in between when using table/query data in an expression.

· Aggregate functions: Group By, Min, Max, Avg, Count, Sum, Where, Expression.

· The count aggregate function determines the number of records in a field so any field from that same table can be used… be sure to choose a field!

· When the total line is used (i.e. aggregate functions are used), all the fields listed must have something in the total line.

· When using a total line, sometimes the resulting dynaset field names will change (min, max, sum, count, avg). For example, when counting the number of states, the output field will likely be: CountofState.

· For Query grid problems ONLY, assume data integrity is NOT violated

Attendees
Id#	FName	LName	Address	City	State	Zip
1	Nancy	Davolio	507 - 20th Ave. E. Apt. 2A	Seattle	WA	98122
2	Tim	Smith	908 W. Capital Way	Tacoma	WA	98401
3	Brandon	Coake	722 Moss Bay Blvd.	Kirkland	WA	98033
4	Margaret	Peacock	4110 Old Redmond Rd.	Redmond	WA	98052
5	Matthew	Dunn	14 Garrett Hill	Seattle	WA	98105
6	Helvetius	Nagy	722 DaVinci Blvd.	Kirkland	WA	98034
7	Deborah	Peterson	305 - 14th Ave. S. Suite 3B	Seattle	WA	98128
10	Smith	John	123 Main Street	Columbus	OH	43081
11	Johnson	Mark	420 Broad Street	Columbus	OH	42001
12	Griffin	Jen	500 High Street	Columbus	OH	43081
13	King	Jennifer	400 Walnut Street	Cincinnati	OH	41098
14	Day	Karen	250 Fifth Street	Cincinnati	OH	41077
15	Bair	Jeff	125 Plum Street	Cincinnati	OH	41077
16	Beard	Nancy	10 South Wacker	Chicago	IL	60639
17	Wallace	Brad	120 South LaSalle	Chicago	IL	60639
18	Davis	Joe	15 West Washington	Chicago	IL	60221

Registration
registration#	id#	Service Code
1	4	D
2	7	A
3	7	B
4	2	B
5	1	A
6	5	D
7	6	A
8	6	C
9	9	D
10	10	E
11	14	D
12	17	A
13	17	B
14	12	B
15	16	A
16	5	D
17	13	A
18	13	C
19	11	D

payments
id#	payment$	date	method
1	$400.00	5/16/2002	credit card
4	$250.00	6/16/2002	check
4	$250.00	5/22/2002	credit card
6	$1,200.00	6/21/2002	PO
7	$500.00	5/28/2002	credit card
11	$200.00	6/3/2002	check
11	$1,050.00	6/8/2002	PO
12	$100.00	6/8/2002	credit card
15	$1,250.00	6/12/2002	credit card
16	$800.00	6/16/2002	credit card
17	$100.00	6/2/2002	check

Rates
Service Code	Description	Fee
A	Class Tuition	$800.00
B	Exam - I	$200.00
C	Room & Board	$400.00
D	All Inclusive	$1,250.00
E	Exam - II	$100.00

The Conference-Registration database above consists of the following tables:

• Attendees -list of all attendees of this annual conference. A unique id# is associated with each attendee. Note that some attendees have not yet registered for specific services but have been included since they have indicated their desire to attend.
• Rates – A list of the different conference services and rates, by unique service code. Attendees may sign up for one or more service code items or the all inclusive fee which covers all services.
• Registration – is a list of all this year’s conference registrations by attendee by service (ie: attendee #7 is signed up for both A and B – class tuition and exam).
• Payments – is a list of this year’s payments including the amount, date and payment method.

Name ______________________________ Lab _______ 1:30pm _______ 3:30pm Seat# ________

1. (6 pts ) Database Relationships. Set up the relationships for this database. Using the boxes below, fill in the primary and foreign key(s) - if any - for each table and draw relationship lines between tables.

			REGISTRATION table Primary Key Foreign Key(s)
	ATTENDEES table Primary Key Foreign Key(s)
PAYMENTS table Primary Key Foreign Key(s)		RATES table Primary Key Foreign Key(s)

2a. (4 pts) Why is it invalid to join the payments, attendees and registration tables?

2b. (4 pts) What would happen if I decided to change the id# 1 on the Attendees table from 1 to 21? How would that change the database?

2c. (4 pts) What is the difference between an inner join between two tables and an outer join relative to the foreign key table of the same two tables?

3. (6 pts) List the full name of all attendees planning on taking any of the exams during the conference. Sort the list by last name then first name. All exams are listed as such by their description. Make sure your query will work even if another exam is later added to the table.

Query Name: Q3 Tables Required:

Join Type: Relative to:

Field

Table

Total

Sort

Show

Criteria

OR

Additional Room (if needed)

4. (6 pts) Summarize the payments made for all Ohio Attendees. List the total amount of their payments, the attendee’s id number and full name.

Query Name: Q4 Tables Required:

Join Type: Relative to:

Field

Table

Total

Sort

Show

Criteria

OR

Additional Room (if needed)

5. (5 pts) Create a Registration Fee list which includes each registration record and the associated fee for services. List the registration#, registrant’s id# and the associated fee.

Query Name: Q5 Tables Required:

Join Type : Relative to:

Field

Table

Total

Sort

Show

Criteria

OR

Additional Room (if needed)

6. (15 pts) ) Using Q5 as a starting point, create a list by attendee (id#, last name) that details the number of payments made and the total amount still owed by the attendee based on their registration charges. Be sure to list ALL attendees.

Query Name: Q6A Tables Required:

Join Type: Relative to:

Field
Table
Total
Sort
Show
Criteria
OR

Additional Room (if needed)

Query Name: Q6B Tables Required:

Join Type: Relative to:

Field
Table
Total
Sort
Show
Criteria
OR

Additional Room (if needed)

Query Name Q6C Tables Required:

Join Type: Relative to:

Field
Table
Total
Sort
Show
Criteria
OR

Additional Room (if needed)

SCORE ________/50