Q1. Find patients name who are treated by Doctor Dr Bill.
Sol.select p.fname,p.lname from patients p inner join doctors d on p.docid=d.doctorid where d.fname='dr.bill';
Q2. Find Patient Details occupied in room 1100.
Sol.select p.patientid,p.fname,p.lname,p.address from patients p inner join room r on p.patientid=r.patientid where r.roomno=1100;
Q3. Nurse Sue checked which all patients on 09-09-2015.
Sol.select p.fname,p.lname from patients p where p.patientid in(select r.patientid from room r inner join nursecheckingdetails n on n.roomno=r.roomno where n.daate='09-09-2015');
Q4. List Names of Nurses who work with Dr Bill.
Sol.select n.fname,n.lname from nurses n inner join doctors d on n.doctorid=d.doctorid and d.fname='dr.bill';
Q5. List Names of Doctors and number of patients they are treating or have treated.
Sol.select d.fname as Doctorfname,d.lname as DoctorLName,count(p.patientid) as NoofPatients from patients p inner join doctors d on p.docid=d.doctorid group by d.fname,d.lname;
Q6. List Names of Doctors and number of nurses they have with them to help.
Sol.select d.fname as Doctorfname,d.lname as DoctorLname,count(n.nurseno) from doctors d left outer join nurses n on d.doctorid=n.doctorid group by d.fname,d.lname;
Q7. How many rooms to type Basic are occupied?
Sol.select count(r.roomno) as NoOfRooms from room r inner join roomdetails rd on r.typeofroom=rd.roomtypeid where rd.type='basic' and r.status='occupied';
Q8. How may rooms of Type Basic are available ?
Sol.select count(r.roomno) as NoOfRooms from room r inner join roomdetails rd on r.typeofroom=rd.roomtypeid where rd.type='basic' and r.status='available';
Q9. List doctors who have less than 2 patients to treat
Sol.select d.fname,d.lname from doctors d left outer join patients p on p.docid=d.doctorid group by d.fname,d.lname having count(p.patientid)(select min(salary+10000)from doctors);
Q14. Retrieve names of Doctors who do not have any nurses allocated to them
Sol.select d.fname,d.lname from doctors d where d.doctorid not in(select doctorid from nurses);
Q15. Retrieve no of Doctors who are active and inactive
Sol.select count(status) as Count,Status as DoctorStatus from doctors group by status;
Q16. Count no of Male and female doctors
Sol.select count(doctorid) as Count,minit as Gender from doctors group by minit;
Q17. Find average salary for male and female doctors make
Sol.Select avg(salary) as AvgSalary,minit AS Gender
From doctors
Group by minit;