SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 398

PATIENT ( PatientNo , PatientName, DOB, AdmissionDate, DischargeDate)

DOCTOR (DoctorID, DoctorName, DoctorPhone)

SERVICE ( ServiceCode , ServiceDesc)

TREATMENT (PatientNo, DoctorID, TreatmentDesc)

Foreign Key: PatientNo REFERENCES PATIENT
DoctorID REFERENCES DOCTOR

BILLING (PatientNo, ServiceCode, BilledAmount)

Foreign Key: PatientNo REFERENCES PATIENT
ServiceCode REFERENCES SERVICE

Figure 13-11 Relational data model: medical center.

3. List those patients who have been charged more than the average for service
SCAN.
SELECT DISTINCT PatientNo
FROM BILLING
WHERE ServiceCode = 'SCAN'
And BilledAmount >
(SELECT AVG (BilledAmount)
FROM BILLING
WHERE ServiceCode = 'SCAN')
4. List those patients who have been charged for XRAY and SCAN.
SELECT DISTINCT PatientNo
FROM BILLING B1
WHERE EXISTS
(SELECT *
FROM BILLING B2
WHERE B1.PatientNo = B2.PatientNo and
B1.ServiceCode = 'XRAY' and
B2.ServiceCode = 'SCAN')
5. List all patients who have been charged the same for XRAY and SCAN.
SELECT PatientNo, PatientName
FROM PATIENT
WHERE PatientNo IN
(SELECT PatientNo
FROM BILLING
WHERE ServiceCode = 'XRAY' and
BilledAmount = ALL
(SELECT BilledAmount