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

#9 ) Select Distinct Id, Model, Count(E.ID)

From Equipment E Inner Join EquipmentType ET On E.TypeId = ET.Id

Group By E.Id Having Count(E.ID) > 3

#10) Select FutureVisit From Admission Where LeaveDate = (Select Max(LeaveDate) From Admission Where Patient_SSN = \'111-22-3333\' ) AND Patient_SSN = \'111-22-3333\'

#11 ) Select DoctorID

From Examine E Inner JOIN Admission A On E.AdmissionNum = A.Num

Where Patient_SSN = \'111-22-3333\'

Group By DoctorID Having Count(DoctorID) > 2

#12 ) Select Distinct TypeId

From Equipment Where PurchaseYear In (2010, 2011)