Queries
1.
SELECT petID AS ID, petName AS Name, petAge AS Age
FROM Pets
WHERE UPPER(SUBSTR(petName,1,1)) BETWEEN 'A' AND 'M'
ORDER BY petID
2.
SELECT doctorID AS ID, name, officeNo AS "Office Number", COUNT(appointmentID) AS "Number of Appointments"
 FROM Doctors
JOIN Appointments USING (doctorID)
WHERE shiftPattern IN ('p','P')
GROUP BY doctorID, name, officeNo
HAVING COUNT (appointmentID)>=3;
3.
SELECT petID AS ID, petName AS Name, COUNT(appointmentID)AS "Number of Appointments"
FROM Pets JOIN Appointments USING (petID)
WHERE apptDate BETWEEN '02-Jan-2019' AND '26-Sep-2019'
GROUP BY petID, petName
HAVING COUNT (appointmentID)>=2;
4.
SELECT Apt.petID AS ID, Pets.petName AS Name, diag.COST AS "Cost of Appointment"
FROM ((Appointments apt
INNER JOIN Pets ON apt.petID = Pets.petID)
INNER JOIN Diagnosis diag ON apt.AppointmentID = diag.AppointmentID)
WHERE COST < (SELECT AVG(COST) FROM Diagnosis);
(IF we decide TO change how COST works ALL this will run ON a switch CASE FOR age)
5.
SELECT apt.doctorID AS "Doctor ID", drs.shiftPattern AS "Shift Pattern", COUNT(apt.appointmentID) AS "Number of Appointments", apt.petID AS "Pet ID", Pets.petName AS "Pet Name", apt.apptDate AS "Appointment Date"
FROM (((Appointments apt
INNER JOIN Pets ON apt.petID= Pets.petID)
INNER JOIN Doctors drs ON apt.doctorID = drs.doctorID)
INNER JOIN Diagnosis diag ON apt.AppointmentID = diag.AppointmentID)
WHERE UPPER(diag.diagnosisDesc) LIKE UPPER('%dental%') OR UPPER(diag.diagnosisDesc)  LIKE UPPER('%socialisation%') AND apt.apptDate <'21-Sep-2019'
GROUP BY apt.doctorID, drs.shiftPattern, apt.petID, Pets.petName, apt.apptDate;
6.
SELECT petID AS "Pets without Appointments"
FROM Pets
MINUS
SELECT DISTINCT petID
FROM Appointments;