Question
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. -
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started