List of Appointments
1. Show a list of appointments for June 2016. Provide the doctor and patients full name, disease name (if any diagnosed), date & time of appointment together in this format ‘YYYY-MON-DD HH:MM’, consultant room number used, and status (i.e. done). Show these appointments in date & time order.*/
select Concat(doctor.surname,’ ‘, doctor.given) as ‘doctor name’,
concat(patient.surname,’ ‘, patient.given) as ‘patient name’,
disease.name as ‘disease name’,
concat(concat(appointment.dateofappointment,” “),date_format(timeofappointment, ‘%h:%i’)) as ‘time of appointment’,
appointment.roomno, appointment.done as ‘status’
from appointment, doctor, patient, disease, diagnosed
where patient.patientid=appointment.patientid
and doctor.doctorid=appointment.doctorid
and appointment.appointmentid=diagnosed.appointmentid
and disease.diseaseid=diagnosed.diseaseid
and appointment.dateofappointment between ‘2016-06-01’ and ‘2017-06-30’
order by appointment.dateofappointment, appointment.timeofappointment;
2. Create a view called “todays_appointments”. The view should list all appointments for the current day. Include the appointment date and time, the doctor’s name, the patient’s name and phone number, and done field. Order the view into doctor’s name, then order by the appointment time.*/
create view todays_appointments as
select concat(appointment.dateofappointment,’ ‘, appointment.timeofappointment) as appointment_date_and_time,
concat(doctor.surname,’ ‘,doctor.given) as doctor_name,
concat(patient.surname,’ ‘, patient.given) as patient_name,
patient.phonehome as phone_number, appointment.done
from doctor, patient, appointment
where patient.patientid=appointment.patientid
and doctor.doctorid=appointment.doctorid
and appointment.dateofappointment=curdate()
order by doctor.surname, doctor.given, appointment.timeofappointment;
/*3. List all appointments made by patients in the suburb “VIEWBANK” (sometimes it has been entered as “VIEW BANK”), during the 2016 year that have not been diagnosed with any diseases. Show the name of the patient and the date and time of the appointment.*/
select concat(patient.surname,’ ‘, patient.given) as ‘patient name’,patient.suburb,
concat(appointment.dateofappointment,’ ‘, appointment.timeofappointment) as ‘appointment date and time’
from appointment, patient
where patient.patientid=appointment.patientid
and appointment.dateofappointment>’2016-12-31′
and patient.suburb like (‘VIEW BANK’ or ‘VIEW BANK’)
and appointment.appointmentid not in (select diagnosed.appointmentid
from appointment, disease,diagnosed where disease.diseaseid=diagnosed.diseaseid
and diagnosed.appointmentid=appointment.appointmentid);
4. Display the name of the FEMALE patient(s), age and sex of who is/are the oldest and youngest.*/
select concat(first.surname,’ ‘, first.given) as ‘patient name’,
(year( getdate())-year(first.dob)) as ‘age’,
first.sex
from (
select top 1 *
from patient
where sex=’F’
order by dob desc ) first
union all
select concat(last.surname,’ ‘, last.given) as ‘patient name’,
(year( getdate())-year(last.dob)) as ‘age’,
last.sex
from (
select top 1 *
from patient
where sex=’F’
order by dob asc) last
/*5. Show a list of the consultation room numbers, along with a count of the number of times they have been used for an appointment (don’t count the appointments that have not been completed/done).*/
select roomno as ‘consultation room numbers’,
count(roomno) as ‘used for an appointment’,
done from appointment
group by roomno, done;
/*6. List all disease names and a count of how many times each disease has been diagnosed at the clinic. The list is of rarely identified diseases, so show only diseases that have a diagnosis count of less than 3. Note: Zero is less than 3. Provide the SQL query and a data VISUALISATION of the results.*/
select disease.name as ‘disease name’,count(diagnosed.diagnosedid) as ‘diagnosed’
from disease left join diagnosed on disease.diseaseid=diagnosed.diseaseid
group by disease.name
having count(diagnosed.diagnosedid)<3;
/*7. Show all the patients that have visited the clinic more than 12 times. Show at least the surname and given name of the patient.*/
Todays Appointments
select patient.surname as ‘patient surname’, patient.given as ‘patient given name’
from patient, appointment
where patient.patientid=appointment.patientid
group by patient.patientid
having count(appointment.appointmentid)>12;
/*8. Need to create a Christmas card address list. Generate a query that includes both patients and doctors data. Show the ID (patientID or doctorID), concatenate a “PA” in front of patientIDs and a “DO” in front of doctorIDs, address, suburb, postcode. Sort list into suburb and then name order. Only select patients that have been a patient (have an appointment) at the clinic in 2016, and only doctors that have seen a patient in the clinic in 2016.*/
select concat(‘PA’, patient.patientID, ‘ ‘,concat(patient.surname,’ ‘, patient.given),’ ‘ , patient.address,’ ‘ , patient.suburb, ‘ ‘ , patient.postcode) as ‘patient adderss’,
concat(‘DO’, doctor.doctorid, ‘ ‘ ,Concat(doctor.surname,’ ‘, doctor.given),’ ‘ , doctor.address, ‘ ‘ , doctor.suburb, ‘ ‘ , doctor.postcode) as ‘doctor address’
from doctor, patient, appointment
where patient.patientid=appointment.patientid
and doctor.doctorid=appointment.doctorid
and appointment.dateofappointment between ‘2015-12-31’ and ‘2017-01-01’
order by patient.suburb, doctor.suburb, patient.surname,’ ‘, patient.given,doctor.surname,’ ‘, doctor.given;
/*9. List all doctors, that are currently active doctors of the clinic, that are female and joined in the last four (4) years. Show the name of the doctor and the name of their supervisor.*/
select Concat(doctor.surname,’ ‘, doctor.given) as ‘doctor name’, supervisorid
from doctor
where (year( curdate())-year(joined))<5
and sex=’F’
and resigned=”;
10. The following two queries are related.
- a) Create a view that shows a count of appointments that each doctor has attended. The view should have the following fields: doctor’s id, doctor’s name, & count of appointments.*/
create view number_of_appoinment_of_each_doctor as
select doctor.doctorid, Concat(doctor.surname,’ ‘, doctor.given) as ‘doctor name’,
count(appointment.appointmentid) as ‘count of appointments’
from doctor, appointment
where doctor.doctorid=appointment.doctorid
group by doctor.doctorid;
b) Using the view in the previous question, show the doctor that has the minimum and maximum number of appointments. Show the name of the doctor.*/
select doctor_name
from number_of_appoinment_of_each_doctor
having (min(count_of_appointments)) and (max(count_of_appointments));
12. Find the list of all the patients and doctors who are treating this patient where the patient is diagnosed with Aggression */
select distinct d.doctorId,p.patientid,
di.name from patient p, disease di, doctor d, diagnosed do, appointment a
where di.diseaseID=do.diseaseID and a.patientID=p.PatientID and a.doctorID=d.DoctorID
and di.name=’Aggression’ and a.appointmentID=do.appointmentID
- B) Orange PLAN and resulting Visualization
I create this visualization by using orange tool. Firstly I select the data from database and then select the columns or rows according to the requirements. Then that filtered data store in a table and visualized data by using different type of visualization.
Show a list of the consultation room numbers, along with a count of the number of times they have been used for an appointment (don’t count the appointments that have not been completed/ done).
select roomno as ‘consultation room numbers’,
count(roomno) as ‘used for an appointment’,
done from appointment
group by roomno, done;
In this visualization firstly I select data from database. Then I select column required from database those full fill the requirements. Then store those data in data table and visualized that data by using distribution visualization.
OUTPUT: – this output display total rooms according to the appointment done corresponding to room number.
List all disease names and a count of how many times each disease has been diagnosed at the clinic.
select disease.name as ‘disease name’,count(diagnosed.diagnosedid) as ‘diagnosed’
from disease left join diagnosed on disease.diseaseid=diagnosed.diseaseid
group by disease.name
having count(diagnosed.diagnosedid)<3;
For data visualization firstly I select data from database. Then I select columns those are required to full fill the requirements. Then I merge those columns in single table and store the data in new data table. At last I select some data from data table and visualized those that by using scatter plot visualization.
OUTPUT: – this output displays the selected data in data table. it display selected disease name corresponding to diagnosed.
Find the list of all the patients and doctors who are treating this patient where the patient is diagnosed with “Aggression”.
select distinct d.doctorId,p.patientid,
di.name from patient p, disease di, doctor d, diagnosed do, appointment a
where di.diseaseID=do.diseaseID and a.patientID=p.PatientID and a.doctorID=d.DoctorID
and di.name=’Aggression’ and a.appointmentID=do.appointmentID
In this visualization firstly I select the data from database. Then I select the required columns from the data those full fill the requirements. Then store data in new data table then I absorbed that I need to select a particular disease that name is “aggression”. Then I select row from data table and store data in new data table. At last I visualized selected data by using scatter plot visualization.
OUTPUT: – this output display disease “Aggression” corresponding to patient and doctor.
Atkinson, M. (1981). Database. Maidenhead, Berkshire, England: Pergamon Infotech.
Bernus, P. (2006). Handbook on architectures of information systems. Berlin: Springer.
Chia, K., Seow, E., & Teo, K. (2004). Database. Singapore: Pearson Prentice Hall.
Ha?rder, T., & Wedekind, H. (2005). Data management in a connected world. Berlin: Springer.
Yuk, M., & Diamond, S. (2014). Data visualization for dummies. Hoboken, New Jersey: John Wiley & Sons, Incorporation.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
