Patients_2019 sql

  1. select r.room_location,r.rrom_accomodation,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_accomodation,room_extension asc;

  2. List of vacant rooms
    select patient_no,pat_name from patient where patient_no not in (select patient_no from treats)
    order by patient_no,pat_name asc;

  3. Patients with no charges
    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;

  4. Max and Min salary
    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;

  5. Number of items
    select phy_id,phy_name, date_of_joining,salary from physicain where phy_id in
    (select phy_id from physicianorder by salary desc) order by
    phy_id,phy_name,date_of_joining,salary asc limit 2;

  6. No of treatments
    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;

  7. Top 2
    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 ron 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;

  8. sum total charges
    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;

Hello Ramkiii

Thanks for reaching out to MemSQL. All I see is a bunch of SQL statements. Do you have a specific question on the queries? Are you using MemSQL now and which version?

Cheers,
Ramesh