select p.patient_no, p.pat_name, COALESCE(b.charge,’0.00’) as charge from patient p left outer join billed b on p.patient_no= b.patient_no where p.patient_no not in (Select b.patient_no from billed b ) order by p.patient_no, p.pat_name, b.charge asc;
select p.patient_no, p.pat_name, p.date_admitted, r.room_location, r.room_extension, p.date_discharged from patient p left outer join room r on p.room_location = r.room_location where p.date_discharged is null order by p.patient_no, p.pat_name, p.date_admitted, r.room_location, r.room_extension, p.date_discharged asc;
select phy_id, phy_name, date_of_joining, salary from physician where phy_id in (select phy_id from physician order by salary desc) order by phy_id, phy_name, date_of_joining, salary asc limit 2;
select b.patient_no, p.pat_name, b.item_code, i.description, count(i.description) as item_count from patient p inner join billed b on p.patient_no=b.patient_no inner join item i on b.item_code = i.item_code group by b.patient_no, b.item_code order by b.patient_no, p.pat_name, b.item_code, i.description, count(i.description ) asc;
select patient_no, pat_name, from patient where patient_no not in (select patient_no from treats) order by patient_no, pat_name asc;
select b.patient_no, sum(b.charge) as total_charges from billed b group by b.patient_no having sum(b.charge)>200 order by b.patient_no, sum(b.charge) asc;
select r.room_location, r.room_accommodation, r.room_extension from room r where r.room_location not in (select p.room_location from patient p where p.date_discharged is null ) order by room_location, room_accommodation, room_extension asc;
select phy_id, phy_name, date_of_joining, salary from physician where salary in ((select min (salary) from physician) , (select max(salary) from physician)) order by phy_id, phy_name , date_of_joining, salary asc;
