Owners can have any number of pets. Pets can be prescribed medications by the doctors.
Pet
id,name,age,breed,gender,ownerId
Owner
id,first,last,phone,address
Record
petId,medicine,date
Write out SQL queries to answer these questions:
a) Pharmacy just called and said there was a bad batch of medicine, find the phone numbers to call for all pets prescribed that medicine.
b) Find the pets that are overdue for their annual checkup.
c) Find the owners who have had multiple pets with the same ailment.
d) What percentage of pit bulls have not had rabies vaccine shots?
a) Pharmacy just called and said there was a bad batch of medicine, find the phone numbers to call for all pets prescribed that medicine.
SELECT O.PHONE,O.ADDRESS FROM Owner O
INNER JOIN Pet P ON O.OwnerId = P.OwnerId
INNER JOIN Record R ON P.Id = R.PetId
WHERE R.Medicine ='Medicine Name' AND Date = '2017-03-17'
b) Find the pets that are overdue for their annual checkup.
SELECT P.Id,P.Name,P.Age FROM Pet P
INNER JOIN Record R ON P.Id = R.PetId
WHERE CAST(GETDATE() AS DATE) > DATEADD(YEAR, 1,R.Date)
c) Find the owners who have had multiple pets with the same ailment.
SELECT O.Id FROM Owner O
INNER JOIN Pet P ON O.OwnerId = P.OwnerId
INNER JOIN Record R ON P.Id = R.PetId
GROUP BY O.Id,P.Id,R.Medicine HAVING COUNT(*) > 1
Using a function like DATEADD() on a Column (e.g. R.Date) in the WHERE clause (or a JOIN condition) will prevent the query being SARGable, and thus SQL won't use any indexes that would speed up the query.
d) What percentage of pit bulls have not had rabies vaccine shots?
Select count(Id)100 / ( Select COUNT() from Pet ) from Pet where Breed='Pit bulls' and Id Not In (Select ID from Pet P inner join record R on P.id = R. petId
where breed='Pit Bulls' and medicine ='Rabies')