a Write a stored procedure to display the average fee that t

a.) Write a stored procedure to display the average fee that the dogs with dogName X were charged. X is to be an input parameter to the stored procedure. It is expected that your stored procedure will work for any instances of the above schema. Demonstrate that the stored procedure works by trying it on the above tables.

b.) Write a stored procedure to display the locations at which the veterinarians with vetName X performed exams. It is expected that your stored procedure will work for any instances of the above schema. Demonstrate that the stored procedure works by trying it on the above tables.

Both parts of this problem involve the following schema: Veterinarians (licenscNum: Integer,vetName: String), Examine (licenseNum: Integer, did: Integer, lid: Integer, fee: Real); Dogs (did: Integer, dogName: String); Location (lid: Integer, locName: String), Use the following instances: Veterinarians vetName Alice Mary Jim licenseNum 112 211 Dogs: did 324 582 731 dogName Fido Examine did 324 731 324 582 731 324 582 731 582 582 lid 1001 1003 1001 1001 1002 100 1002 1001 1001 1003 fee 10 20 30 50 35 25 35 20 25 licenseNum 112 112 112 211 211 211 211 211 Location lid 1001 1002 1003 locName St. Cloud Minneapolis Duluth

Solution

a)

create procedure proc5

@ X varchar(20)

as

begin

select avg(fee)

from Examine

join Dogs

   on Examine.did = Dogs.did

where dogName = \'Spot\'

end