Question $3$

Practical computer work: The answer to this question should be submitted electronically.

$($Marks: $65)$

Using MySQL$,$ create a single Structured Query Language $($SQL$)$ script that answers all the questions below. Include comments to indicate which part of the script answers which question.

The script must execute correctly using MySQL to get full marks.

Consider the following extract from an Entity Relationship Diagram $($ERD$)$ before answering questions $3\mathrm{.}1$ to $3\mathrm{.}8$:

Patient

PatientID $\{$PK$\}$

PatientName

PatientSurname

PatientDOB

$1\mathrm{.}1$

$1.*$

$1\mathrm{.}1$

Doctor

DoctorID $\{$PK$\}$

DoctorName

DoctorSurname

Appointment

AppointmentID $\{$PK$\}$

PatientID $($FK$\}$

DoctorID $\{$FK$\}$

AppointmentDate AppointmentTime

AppointmentDuration

Q$\mathrm{.}3\mathrm{.}1$

Q$\mathrm{.}3\mathrm{.}2$

Q$\mathrm{.}3\mathrm{.}3$

Q$\mathrm{.}3\mathrm{.}4$

Write an SQL statement to create the Patient table.

Write an SQL statement to create the Doctor table.

Write an SQL statement to create the Appointments table.

$(5)$

$(4)$

$(9)$

$(11)$

Write SQL statements to insert the following data:

Table: Patient

Patient ID

Patient Name

Patient Surname

Date of Birth

$1$

Debbie

Theart

$1980-03-17$

$2$

Thomas

Duncan

$1976-08-12$Table: Doctor

Table: Appointments

Doctor ID

Doctor Name

Doctor Surname

$1$

Zintle

Nukani

$2$

Ravi

Maharaj

Appointment

ID

Appointment

Doctor

ID

Patient

ID

Date

Time

Duration

$1$

$2024-01-15$

$9$:$00$

$15$

$2$

$1$

$2$

$2024-01-18$

$15$:$00$

$30$

$2$

$2$

$3$

$2024-01-20$

$10$:$00$

$15$

$1$

$1$

$4$

$2024-01-21$

$11$:$00$

$15$

$2$

$1$

Q$\mathrm{.}3\mathrm{.}5$

Q$\mathrm{.}3\mathrm{.}6$

Write an SQL statement to display all the appointments between $2024-01-16$ and $2024-01-20($inclusive$).$

Write a SQL statement to display the names and surnames of patients with the total number of appointments they have. Sort the results in descending order based on the number of appointments.

$(4)$

$(6)$

Q$\mathrm{.}3\mathrm{.}7$

Write an SQL statement to display all the appointments. The statement should display the appointment date $($in descending order$),$ the time, the doctor's name and surname, and the patient's name and surname.

$(10)$

Q$\mathrm{.}3\mathrm{.}8$

Create a view that gets a list of all the patients that have appointments with the doctor with Doctor ID $2.$ Include just the name and surname of the patients in the result and sort results in ascending alphabetical order according toCreate a stored procedure called get $\_$appointments. It should take the date of an appointment as input and display all the appointment details for that date.

Display the time $($in ascending order$),$ duration, doctor name and surname, as well as patient name and surname