$--$ Create Appointments table

CREATE TABLE Appointments $($

AppointmentID INT PRIMARY KEY,

PatientID INT,

DoctorID INT,

AppointmentDate DATE,

AppointmentTime TIME,

AppointmentDuration INT,

FOREIGN KEY $($PatientID$)$ REFERENCES Patient$($PatientID$),$

FOREIGN KEY $($DoctorID$)$ REFERENCES Doctor$($DoctorID$)$

$)$;

$--$ Insert data into tables

INSERT INTO Patient $($PatientID$,$ PatientName, PatientSurname, PatientDOB$)$

VALUES

$(1,$ 'Debbie', 'Theart', $\text{'}1980-03-17\text{'}),$

$(2,$ 'Thomas', 'Duncan', $\text{'}1976-08-12\text{'})$;

INSERT INTO Doctor $($DoctorID$,$ DoctorName, DoctorSurname$)$

VALUES

$(1,$ 'Zintle', 'Nukani'$),$

$(2,$ 'Ravi', 'Maharaj'$)$;

INSERT INTO Appointments $($AppointmentID$,$ PatientID, DoctorID, AppointmentDate, AppointmentTime, AppointmentDuration$)$

VALUES

$(1,1,1,\text{'}2024-01-15\text{'},\text{'}09$:$00$:$00\text{'},15),$

$(2,1,2,\text{'}2024-01-18\text{'},\text{'}15$:$00$:$00\text{'},30),$

$(3,2,2,\text{'}2024-01-20\text{'},\text{'}10$:$00$:$00\text{'},15),$

$(4,1,1,\text{'}2024-01-21\text{'},\text{'}11$:$00$:$00\text{'},15)$;

$--$ Q$\mathrm{.}3\mathrm{.}5$: Display all appointments between $2024-01-16$ and $2024-01-20($inclusive$)$

SELECT $*$

FROM Appointments

WHERE AppointmentDate BETWEEN $\text{'}2024-01-16\text{'}$ AND $\text{'}2024-01-20\text{'}$;

$--$ Q$\mathrm{.}3\mathrm{.}6$: Display names and surnames of patients with total number of appointments

SELECT p$.$PatientName, p$.$PatientSurname, COUNT$($a$.$AppointmentID$)$ AS TotalAppointments

FROM Patient p

JOIN Appointments a ON p$.$PatientID $=$ a$.$PatientID

GROUP BY p$.$PatientName, p$.$PatientSurname

ORDER BY TotalAppointments DESC;

$--$ Q$\mathrm{.}3\mathrm{.}7$: Display all appointments with doctor and patient details

SELECT a$.$AppointmentDate, a$.$AppointmentTime, d$.$DoctorName, d$.$DoctorSurname, p$.$PatientName, p$.$PatientSurname

FROM Appointments a

JOIN Doctor d ON a$.$DoctorID $=$ d$.$DoctorID

JOIN Patient p ON a$.$PatientID $=$ p$.$PatientID

ORDER BY a$.$AppointmentDate DESC;

$--$ Q$\mathrm{.}3\mathrm{.}8$: Create view for patients with appointments with Doctor ID $2$

CREATE VIEW PatientsWithDoctor$2$ AS

SELECT p$.$PatientName, p$.$PatientSurname

FROM Patient p

JOIN Appointments a ON p$.$PatientID $=$ a$.$PatientID

WHERE a$.$DoctorID $=2$

ORDER BY p$.$PatientName, p$.$PatientSurname ASC;

$--$ Create stored procedure get$\_$appointments

DELIMITER $//$

CREATE PROCEDURE get$\_$appointments$($IN appointment$\_$date DATE$)$

BEGIN

SELECT a$.$AppointmentTime, a$.$AppointmentDuration, d$.$DoctorName, d$.$DoctorSurname, p$.$PatientName, p$.$PatientSurname

FROM Appointments a

JOIN Doctor d ON a$.$DoctorID $=$ d$.$DoctorID

JOIN Patient p ON a$.$PatientID $=$ p$.$PatientID

WHERE a$.$AppointmentDate $=$ appointment$\_$date

ORDER BY a$.$AppointmentTime ASC;

END$//$

DELIMITER ;