i. How many pets live in each city and state served by Mesa Veterinary Hospital?

Question

i.               How many pets live in each city and state served by Mesa Veterinary Hospital? (i.e. list city, state, and number of pets per city in your output)

ii.              What was the total prescription revenue (price customer paid), total prescription cost (price Mesa Veterinary Hospital paid for drug) and profit (i.e. price – cost) for January?

iii.            How many pets from Gilbert did employee number 1 examine on January 10?

iv.            On what date did the clinic receive payment for appointment 612?

v.              List the veterinarian’s employee name, pet number, pet name, and the total prescription revenue for appointments 611 and 612?

vi.            Dean David Spalding took his pets in on 1/11/17. He calls and wants to know how much charges he incurred on that date, each pet’s diagnosis, and the name of the vet that treated each pet. Finally, he wants to make sure the clinic has each pet’s correct birthdate.

Service Service Examination Service Number Seervice Name Standard Cost List Price Standard Tlme to Pe Exam Number Pet Examination Service Number Service_Quantity Total Service Billed Pet Number Pet Name Parent Number Breed Color Birth Date Exam Number Exam Date Pet Number Employee Number Time Started Time_Completed Scheduled Time Diagnosis Weight Appointment Charge Receipt Number Drug Drug Number Drug Name Drug Cost Drug Price Drug_Examination Drug Number Exam Number Employee Employee Number Employee Name Employee Street_Add Employee_City Employee State Employee_Zip Dosage Number of Refils Actual Cost Actual Price Cash Collection Receipt_Number Receipt Date Amount Parent Number Employee Number Account Number as Account Number Type Bank Number Balance Parent Parent Numbe Parent Name Parent Street Parent City Parent State Parent Zip

Explanation / Answer

i.
select b.parent_state,a.parent_city,a.countn
from
(select parent_city,count(*) countn
from Parent
group by parent_city) a
join
(select distinct parent_city,parent_state from Parent) b
a.parent_city=b.parent_city

ii.

select sum(DE.Actual_cost) cost,
sum(DE.Actual_price) price,
(sum(DE.Actual_price) - sum(DE.Actual_cost)) Profit
from Drug_Examination DE
join Examination E
on DE.Exam_number = E.Exam_number
where month(E.Exam_date) = 1

iii.

Select count(distinct E.Exam_Number)
from Examination E
join Employee Em
on E.Emplyee_Number=Em.Emplyee_Number
join Pet p
on E.Pet_Number=p.Pet_Number
join Parent pr
on p.Parent_Number=pr.Parent_Number
where Em.Employee_number =1
and month(E.Exam_date) = 1
and day(E.Exam_date) = 10
and pr.city='Gilbert'

iv.

select cc.Receipt_date
from Examination E
join
Cash_Collection cc
on E.Receipt_Number=cc.Receipt_Number
where E.Exam_Number = 612

v.

select Em.Employee_name,
p.pet_number,
p.pet_name,
cc.amount
from Examination E
Join
Cash_Collection cc
on E.Receipt_Number=cc.Receipt_Number
join Employee Em
on E.Emplyee_Number=Em.Emplyee_Number
join Pet p
on E.Pet_Number=p.Pet_Number
where E.Exam_Number in (611,612)

vi.

select Em.Employee_name,
p.pet_number,
p.pet_name,
cc.amount,
p.birth_date
from Examination E
Join
Cash_Collection cc
on E.Receipt_Number=cc.Receipt_Number
join Employee Em
on E.Emplyee_Number=Em.Emplyee_Number
join Pet p
on E.Pet_Number=p.Pet_Number
join Parent pr
on p.Parent_Number=pr.Parent_Number
where E.Date = '01/11/17'
and pr.parent_name = 'Dean David Spalding'

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.