Good! How about three exercises for consolidating our knowledge?
Show the name of each treatment and the first and last names of the patients to whom it was recommended for all therapies recommended by physicians with the surname Core or Calderwood.
Consider only patients with odd ID numbers.
To filter patients' Ids by odd numbers, you can of course take a look at the to check the Id's manually and use the IN operator in the following manner:
WHERE Patient.Id IN (1, 3, 5 ... 15)
However, there is also a more concise way to do this. When dividing an odd number by two, the remainder is always 1.
Use the modulo (%) operator to find the remainder after division of one number by another. The use of the modulo operator in this exercise is as follows:
WHERE Patient.Id % 2 = 1
Excited about the modulo operator? You'll find more about it in our Common Functions in MS SQL Server course!
Type:
SELECT
Name,
Patient.FirstName,
Patient.LastName
FROM Therapy
JOIN Physician
ON PhysicianId = Physician.Id
JOIN Patient
ON PatientId = Patient.Id
JOIN Treatment
ON TreatmentType = Treatment.Type
WHERE Physician.LastName IN ('Core', 'Calderwood')
AND Patient.Id % 2 = 1
