Patient ( pid : integer, lname : string, fname : string, primary_did : integer,
ID: 3681203 • Letter: P
Question
Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer)
Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer)
TestingInfo (testid: integer, name: string, cost: integer, machine_needed: string)
MedicalTest (testid: integer, pid: integer, dateOrdered: date, whoOrdered: integer, dateGiven: date, timeGiven: time, nid: integer, techID: integer)
Write query in SQL.
1.) Find all patient IDs, last and first names, doctor IDs, last names, medical test IDs, medical test names and the dates the tests were given to each patient for tests ordered by the last name of the doctor named "Smith"
Explanation / Answer
Here the task is to join 4 tables in a single query. This can be achieved with nested query using inner join.
select patientid, lname,fname,did,lname,testid,name,dategiven from doctor
INNER JOIN Patient on Doctor.did=Patient.primary_did
INNER JOIN MedicalTest on Patients.pid=MedicalTest.pid
INNER JOIN TestingInfo pn MedicalTest.testid=TestingInfo.testid
where
lname='Smith'