Write SQL queries against the tables you created above to an

Write SQL queries against the tables you created above to answer the following queries:

Q1: Report the hospital rooms (the room number) that are currently occupied.

Q2: For a given division manager (say, ID = 10), report all regular employees that are supervised by this manager. Display the employees ID, names, and salary.

Q3: For each patient, report the sum of amounts paid by the insurance company for that patient, i.e., report the patients SSN, and the sum of insurance payments over all visits. Note: If you keep the insurance coverage as a percentage, then compute this percentage before getting the sum.

Q4: Report the number of visits done for each patient, i.e., for each patient, report the patient SSN, first and last names, and the count of visits done by this patient.

The Relational Model Employee ID, FName, LName, Salary, jobTitle, officeNum, empRank, supervisorID) That is one design, other designs for the ISA relationship are possible... EquipmentType (ID, Desc, model, nstructions) Equipment (Serial#, TypeID, PurchaseYear, Lastlnspection, roomNum) Room (Num, occupied flag) Room Service (roomNum service) RoomAccess (roomNumLEmplD) Patient (SSN, FirstName, LastName, Address, TelNum) Doctor (LD, gender, specialty, LastName, FirstName) Admission (Num, AdmissionDate, LeaveDate, TotalPayment, InsurancePayment, Patient SSN, FutureVisit) Examine (Doctor D AdmissionNum, comment) Stay In (AdmissionNum RoomNum startDate, endDate)

Solution

#1
Select Num from Room
where occupied flag=0;

#2
select ID, names, salary
from Employee
where supervisorID=10;

#3
select Patient_SSN, sum((InsurancePayment/TotalPayment*100)) as InsurancePaymentPerc, count(NUM) as OverallVisits
from Admission
group by Patient_SSN;

#4
select Patient_SSN, FirstName, LastName, count(NUM) as OverallVisits
from Admission
group by Patient_SSN, FirstName, LastName;