Consider the following database schema (the primary keys of the relations are CA
ID: 642085 • Letter: C
Question
Consider the following database schema (the primary keys of the relations are CAPITALIZED).
Patient (ID, Name)
Insurance (POLICYNO, PatientId, InsuranceCo)
Insurance(PatientId) references Patient(Id)
Physician (ID, Name)
Visit (ID, PatientId, PhysId, VisitDate, ReasonForVisit)
Visit(PatientId) references Patient(Id)
Visit(PhysId) references Physician(Id)
Express each of the following SQL queries in English.
Example:
SELECT P.Name
FROM Patient P, Insurance I
WHERE P.Id = I.PatientID;
Answer:
Find the names of patients who have insurance.
(a)
SELECT P.Name
FROM Physician P, Patient Q, Visit V
WHERE V.PhysId = P.Id AND V.PatientId = Q.Id
AND Q.Name = `John Doe';
(b)
SELECT COUNT(DISTINCT V1.PatientId)
FROM Visit V1
WHERE V1.PatientId NOT IN (SELECT DISTINCT V2.PatientId
FROM Visit V2
WHERE V2.VisitDate<`2015-01-01');
(c)
SELECT DISTINCT P.Name
FROM Patient P, Visit V
WHERE P.Id = V.PatientId
and NOT EXISTS (SELECT * FROM Visit V2
WHERE V2.PatientId = V.PatientId
and V2.PhysId <> V.PhysId );
(d)
SELECT P.Name, Q.Name
FROM Patient P, Physician Q, Visit V
WHERE V.PatientId = P.Id and V.PhysId = Q.Id
and V.VisitDate >= (SELECT V2.VisitDate
FROM Visit V2
WHERE V2.PatientId = V.PatientId);
(e)
SELECT P.Name
FROM Patient P
WHERE P.Id IN (SELECT V1.PatientId
FROM Visit V1, Visit V2
WHERE V1.ReasonForVisit LIKE `%fever%'
AND V2.ReasonForVisit LIKE `%cough%'
AND V1.PatientId = V2.PatientId
AND V1.VisitDate = V2.VisitDate
AND V1.VisitDate >= `2014-12-01'
GROUP BY V1.PatientId
HAVING COUNT(*) > 2 );
Explanation / Answer
a) Names of all physicians who have a patient named 'John Doe'.
b) Count all Distinct Patients With Visit Date Greater Or Equal To 01-01-2015
c) Distinct Names of all patients who have only one physician attending them
d) Obtain the name of patient and name of physician who the patient attended last
e) Names of all patients who have visited the physician more than twice on the same date for fever and for cough and the visit date is greater than or equal to 01-12-2014