-- 3. From the following tables, write a SQL query to find those physicians who are the head of the department. Return Department name as "Department" and Physician name as "Physician".
SELECT d.name AS "Department",
       p.name AS "Physician"
FROM department d,
     physician p
WHERE d.head=p.employeeid;

-- From the following table, write a SQL query to count the number of patients who booked an appointment with at least one Physician. Return count as "Number of patients taken at least one appointment"
SELECT count(DISTINCT patient) AS "No. of patients taken at least one appointment"
FROM appointment;

-- 5. From the following table, write a SQL query to find the floor and block where the room number 212 belongs. Return block floor as "Floor" and block code as "Block"
SELECT blockfloor AS "Floor",
       blockcode AS "Block"
FROM room
WHERE roomnumber=212;

-- 6. From the following table, write a SQL query to count the number available rooms. Return count as "Number of available rooms".
SELECT count (*) "number of available rooms" FROM room Where unavailable='false';

--  7. From the following table, write a SQL query to count the number of unavailable rooms. Return count as "Number of unavailable rooms".
SELECT count(*) "Number of unavailable rooms"
FROM room
WHERE unavailable='true';

-- 8. From the following tables, write a SQL query to find the physician and the departments they are affiliated with. Return Physician name as "Physician", and department name as "Department".
SELECT p.name AS "Physician",
       d.name AS "Department"
FROM physician p,
     department d,
     affiliated_with a
WHERE p.employeeid=a.physician
  AND a.department=d.departmentid;

-- 9. From the following tables, write a SQL query to find those physicians who have trained for special treatment. Return Physician name as "Physician", treatment procedure name as "Treatment".
SELECT p.name AS "Physician",
c.name AS "Treatment"
FROM physician p,
     PROCEDURE c,
trained_in t
WHERE t.physician=p.employeeid
  AND t.treatment=c.code;
-- 10. From the following tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".
SELECT p.name as "physician",p.position,d.name as "department" FROM physician p join affiliated_with a ON  a.physician=p.employeeid JOIN department d ON a.department=d.departmentid
WHERE primaryaffiliation ='false';
-- 11. From the following tables, write a SQL query to find those physicians who are not a specialized physician. Return Physician name as "Physician", position as "Designation".
SELECT p.name AS "Physician",
       p.position "Designation"
FROM physician p
LEFT JOIN trained_in t ON p.employeeid=t.physician
WHERE t.treatment IS NULL
ORDER BY employeeid;

-- 12. From the following tables, write a SQL query to find the patients with their physicians by whom they got their preliminary treatment. Return Patient name as “Patient”, address as “Address” and Physician name as “Physician”.
SELECT t.name AS "Patient",
       t.address AS "Address",
       p.name AS "Physician"
FROM patient t
JOIN physician p ON t.pcp=p.employeeid;

-- 13. From the following tables, write a SQL query to find the patients and the number of physicians they have taken appointment. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".
SELECT p.name "Patient",
       count(t.patient) "Appointment for No. of Physicians"
FROM appointment t
JOIN patient p ON t.patient=p.ssn
GROUP BY p.name
HAVING count(t.patient)>=1;

-- 14. From the following tables, write a SQL query to count number of unique patients who got an appointment for examination room ‘C’. Return unique patients as “No. of patients got appointment for room C”..
SELECT count(DISTINCT patient) AS "No. of patients got appointment for room C"
FROM appointment
WHERE examinationroom='C';

-- 15. From the following tables, write a SQL query to find the name of the patients and the number of the room where they have to go for their treatment. Return patient name as “Patient”, examination room as “Room No.”, and starting date time as Date “Date and Time of appointment”.
SELECT p.name AS "Patient",
       a.examinationroom AS "Room No.",
       a.start_dt_time AS "Date and Time of appointment"
FROM patient p
JOIN appointment a ON p.ssn=a.patient;
-- 16. From the following tables, write a SQL query to find the name of the nurses and the room scheduled, where they will assist the physicians. Return Nurse Name as “Name of the Nurse” and examination room as “Room No.”.
SELECT n.name AS "Name of the Nurse",
       a.examinationroom AS "Room No."
FROM nurse n
JOIN appointment a ON a.prepnurse=n.employeeid;

26. From the following table, write a SQL query to find the floor where the maximum numbers of rooms are available. Return floor ID as "Floor", count "Number of available rooms".
SELECT blockfloor as "Floor",
count(*) AS  "Number of available rooms"
FROM room
WHERE unavailable='false'
GROUP BY blockfloor
HAVING count(*) =
  (SELECT max(zz) AS highest_total
   FROM
( SELECTblockfloor ,
count(*) AS zz
      FROM room
      WHERE unavailable='false'
      GROUP BY blockfloor ) AS t );

-- 27. From the following table, write a SQL query to find the floor where the minimum numbers of rooms are available. Return floor ID as "Floor", Number of available rooms.
SELECT blockfloor as "Floor",
       count(*) AS  "No of available rooms"
FROM room
WHERE unavailable='false'
GROUP BY blockfloor
HAVING count(*) =
  (SELECT min(zz) AS highest_total
   FROM
     ( SELECT blockfloor ,
              count(*) AS zz
      FROM room
      WHERE unavailable='false'
 28. From the following tables, write a SQL query to find the name of the patients, their block, floor, and room number where they admitted.     GROUP BY blockfloor ) AS t );
SELECT p.name AS "Patient",
       s.room AS "Room",
       r.blockfloor AS "Floor",
       r.blockcode AS "Block"
FROM stay s
JOIN patient p ON s.patient=p.ssn
JOIN room r ON s.room=r.roomnumber
29. From the following tables, write a SQL query to find the nurses and the block where they are booked for attending the patients on call. Return Nurse Name as “Nurse”, Block code as “Block”.
SELECT n.name AS "Nurse",
       o.blockcode AS "Block"
FROM nurse n
JOIN on_call o ON o.nurse=n.employeeid;
-- 30. From the following tables, write a SQL query to get
a) name of the patient,
b) name of the physician who is treating him or her,
c) name of the nurse who is attending him or her,
d) which treatement is going on to the patient,
e) the date of release,
f) in which room the patient has admitted and which floor and block the room belongs to respectively.
SELECT p.name AS "Patient",
       y.name AS "Physician",
       n.name AS "Nurse",
       u.date AS "Date of release",
       r.roomnumber AS "Room",
       r.blockfloor AS "Floor",
       r.blockcode AS "Block"
FROM undergoes u
JOIN patient p ON u.patient=p.ssn
JOIN physician y ON u.physician=y.employeeid
LEFT JOIN nurse n ON u.assistingnurse=n.employeeid
JOIN stay s ON u.stay=s.stayid
JOIN room r ON s.room=r.roomnumber;

31. From the following tables, write a SQL query to find all those physicians who performed a medical procedure, but they are not certified to perform. Return Physician name as "Physician".
SELECT name AS "Physician"
FROM physician
WHERE employeeid IN
    ( SELECT undergoes.physician
     FROM undergoes
     LEFT JOIN trained_In ON undergoes.physician=trained_in.physician
     AND undergoes.procedure=trained_in.treatment
     WHERE treatment IS NULL );
32. From the following tables, write a SQL query to find all the physicians, their procedure, date when the procedure was carried out and name of the patient on which procedure have been carried out but those physicians are not certified for that procedure. Return Physician Name as “Physician”, Procedure Name as “Procedure”, date, and Patient. Name as “Patient”.
.
SELECT p.name AS "Physician",
       pr.name AS "Procedure",
       u.date,
       pt.name AS "Patient"
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr
WHERE u.patient = pt.SSN
  AND u.procedure = pr.Code
  AND u.physician = p.EmployeeID
  AND NOT EXISTS
    ( SELECT *
     FROM trained_in t
     WHERE t.treatment = u.procedure
       AND t.physician = u.physician );

-- 33. From the following table, write a SQL query to find all those physicians who completed a medical procedure with certification after the date of expiration of their certificate. Return Physician Name as “Physician”, Position as “Position”.
SELECT name AS "Physician",
       position AS "Position"
FROM physician
WHERE employeeid IN
    ( SELECT physician
     FROM undergoes u
     WHERE date >
         ( SELECT certificationexpires
          FROM trained_in t
          WHERE t.physician = u.physician
            AND t.treatment = u.procedure ) );

-- 34. From the following table, write a SQL query to find all those physicians who completed a medical procedure with certification after the date of expiration of their certificate. Return Physician Name as “Physician”, Position as” Position”, Procedure Name as “Procedure”, Date of Procedure as “Date of Procedure”, Patient Name as “Patient”, and expiry date of certification as “Expiry Date of Certificate”.
SELECT p.name AS "Physician",
       p.position AS "Position",
       pr.name AS "Procedure",
       u.date AS "Date of Procedure",
       pt.name AS "Patient",
       t.certificationexpires AS "Expiry Date of Certificate"
FROM physician p,
     undergoes u,
     patient pt,
     PROCEDURE pr,
               trained_in t
WHERE u.patient = pt.ssn
  AND u.procedure = pr.code
  AND u.physician = p.employeeid
  AND Pr.code = t.treatment
  AND P.employeeid = t.physician
  AND u.Date > t.certificationexpires;

-- 35. From the following table, write a SQL query to find those nurses who have ever been on call for room 122. Return name of the nurses.
SELECT n.name
FROM nurse n
WHERE employeeid IN
    ( SELECT oc.nurse
     FROM on_call oc,
          room r
     WHERE oc.blockfloor = r.blockfloor
       AND oc.blockcode = r.blockcode
       AND r.roomnumber = 122 );
       
-- 36. From the following table, write a SQL query to find those patients who have been prescribed by some medication by his/her physician who has carried out primary care. Return Patient name as “Patient”, and Physician Name as “Physician”.
SELECT pt.name AS "Ptient",
       p.name AS "Physician"
FROM patient pt
JOIN prescribes pr ON pr.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp=pr.physician
  AND pt.pcp=p.employeeid;
-- 
SELECT pt.name AS "Ptient",
       p.name AS "Physician"
FROM patient pt
JOIN prescribes pr ON pr.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp=pr.physician
  AND pt.pcp=p.employeeid;
-- 37. From the following table, write a SQL query to find those patients who have been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care. Return name of the patient as “Patient”, name of the physician as “Primary Physician”, and cost for the procedure as “Procedure Cost”.
SELECT pt.name AS " Patient ",
p.name AS "Primary Physician",
pd.cost AS " Procedure Cost"
FROM patient pt
JOIN undergoes u ON u.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
JOIN PROCEDURE pd ON u.procedure=pd.code
WHERE pd.cost>5000;

-- 38. From the following table, write a SQL query to find those patients who had at least two appointments where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care. Return Patient name as “Patient”, Physician name as “Primary Physician”, and Nurse Name as “Nurse”.
SELECT pt.name AS "Patient",
       p.name AS "Primary Physician",
       n.name AS "Nurse"
FROM appointment a
JOIN patient pt ON a.patient=pt.ssn
JOIN nurse n ON a.prepnurse=n.employeeid
JOIN physician p ON pt.pcp=p.employeeid
WHERE a.patient IN
    (SELECT patient
     FROM appointment a
     GROUP BY a.patient
     HAVING count(*)>=2)
  AND n.registered='true'
ORDER BY pt.name;

-- 39. From the following table, write a SQL query to find those patients whose primary care a physician who is not the head of any department takes. Return Patient name as “Patient”, Physician Name as “Primary care Physician”.
SELECT pt.name AS "Patient",
       p.name AS "Primary care Physician"
FROM patient pt
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp NOT IN
    (SELECT head
     FROM department);