pet Table

Primary key: PET_ID

Foreign key: PET_MAIN_CARE_GIVER (references the vet Table)

PET_ID	PET_NM	PET_MAIN_CARE_GIVER (FK)
1235	Fido	321
6743	Willow	255
- etc. -

appt Table

Primary key: APPT_PET_ID + APPT_DATE

Foreign keys: APPT_PET_ID (references the pet Table)

APPT_VET_ID (references the vet Table)

APPT_PET_ID (FK)	APPT_DATE	APPT_COST	APPT_VET_ID (FK)
1235	1/12/2001	98	321
1235	7/8/2002	126	255
- etc. -

vet Table

Primary key: VET_ID

Foreign keys: none

VET_ID	VET_L_NM	VET_F_NM	VET_SPECIALITY
321	Smith	John	Small domestic
255	Baker	Sophia	Exotic
- etc. -

Although you dont have to, you may want to create the above tables and populate them with fictitious data.

QUESTION #1

Write the SQL statement to identify the appointment(s) that cost the least. Make sure to show the appointment date, pet name, vet id, and appointment cost.

QUESTION #2

Write the SQL statement to generate a list that indicates the number of times each vet had seen a pet (via an appointment), only if this number is greater than 3. Order the result in ascending order of vet last name.

QUESTION #3

Write the SQL statement to generate the list of vets (by vet id and vet last name) that have had at least one appointment with pet id 435 or pet id 436. Order the results in descending order of vet last name and remove duplicates.

QUESTION #4

Write the SQL statement to generate a list of main caregivers (by name) who have never seen a pet (i.e., they never had a single appointment scheduled!). Do not include duplicate names and order by descending order of last name.

QUESTION #5

Write the SQL statement to generate the number of veterinarians with a specialty that includes the word domestic. Show the name of the specialty and the number of veterinarians within that specialty, but only consider the vets who are main care givers.