task4.sql

-- Q01
-- a.

-- create view doctor_epatient_nurse as
-- (
-- select doctor.name as doctor_name, 
-- 	doctor.id as doctor_id,
-- 	emergency_patient.id as e_patient_id,
-- 	emergency_patient.name as e_patient_name,
-- 	nurse.id as nurse_id,
-- 	nurse.name as nurse_name
-- from doctor join 
-- 		(
-- 			treat join 
-- 			(
-- 				emergency_patient join 
-- 				(
-- 					attend join nurse on attend.nurse_id = nurse.id
-- 				)
-- 				on emergency_patient.id = attend.epatient_id
-- 			)
-- 			on (emergency_patient.id = treat.epatient_id)
-- 		)
-- 		on (doctor.id = treat.doctor_id)
-- )


-- b.

-- create view doctor_surgery_nurse as 
-- (
-- select doctor.id as doctor_id,
-- 	doctor.name as doctor_name,
-- 	surgery.id as surgery_id,
-- 	surgery.patient_id as patient_id,
-- 	surgery.patient_name as patient_name,
-- 	nurse.id as nurse_id,
-- 	nurse.name as nurse_name

-- from doctor join 
-- 	(
-- 		perform join 
-- 		(
-- 			surgery join 
-- 			(
-- 				assign join nurse on assign.nurse_id = nurse.id
-- 			)
-- 			on assign.patient_id = surgery.patient_id
-- 		)
-- 		on surgery.id = perform.surgery_id
-- 	)
-- 	on doctor.id = perform.doctor_id
-- )

-- c. 

-- create view visitor_patient as 
-- (
-- select visitor.name as visitor_name,
-- 	visitor.id as visitor_id,
-- 	patient.id as patient_id,
-- 	patient.name as patient_name

-- from visitor join 
-- 	(
-- 		visit join patient on patient.id = visit.patient_id
-- 	)
-- 	on visitor.id = visit.visitor_id
-- )



-- d. 

-- create view doctor_appointments as 
-- (
-- select doctor.id as doctor_id,
-- 	doctor.name as doctor_name,
-- 	appointment.id as appointment_id,
-- 	appointment.patient_name as patient_name,
-- 	appointment.patient_id as patient_id,
-- 	appointment.reason as reason
-- from doctor join 
-- 	(
-- 		schedule join appointment on appointment.patient_id = schedule.patient_id
-- 	)
-- 	on doctor.id = schedule.doctor_id
-- )

-- e. 

-- create view patient_service as 
-- (
-- select patient.id as patient_id,
-- 	patient.name as patient_name,
-- 	service.id as service_id,
-- 	service.name as service_name,
-- 	service.availed_date as date_availed
-- from patient join 
-- 	(
-- 		avail join service on avail.service_id = service.id
-- 	)
-- 	on patient.id = avail.patient_id
-- )