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:

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

Q.5 Answer Query: Select Num from Room where Num in (Select roomNum from Equipment where Serial# = \'A01-02X\');

Q.6 Answer Query: Select EmpID,count(roomNum) from RoomAccess group by EmpID having count(roomNum)=(Select Max(cnt) from (Select count(roomNum) cnt from RoomAccess group by EmpID));

Q.7 Answer Query: Select jobTitle as Type,count(ID) as Count group by jobTitle from Employee order by count(ID) desc;

Note: In the above query jobTitle have been used to refer to type of employees as it is not mentioned in the question as whether empRank refers to type of employee or jobTitle.You can use empRank in place of jobTitle if empRank refers to type of Employee.

Q.8 Answer Query: Select p.SSN,p.FirstName,p.LastName,a.AdmissionDate from Patient p join Admission a on p.SSN=a.Patient_SSN where a.FutureVisit is not null;