Need help solving these sql queries

Transact-SQL
1

Pet database

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?

2

So how far did you get?

3

Hi Chinmayi,

Hope this might help you!

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

1 Like
4

Can you provide some sample data ;

5

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.

6

Thank you for the help!

7

Hi Chinmayi,

This might be the answer for the last Question::

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')