Question

Use the Northwind and Hospital data models diagrams to answer the following questions

1.) HOSPITAL: Write a query to list the name and assignment History for employee #12345. The assigmnet history is to include the department name and also the number of months the employee served in that assignment. (Do not worry about the fact that this cannot be calculated for the current assignment, at least with what we know now). For one bonus point describe what logic might allow you to calculate the length of the employee's current assignment, but don't worry to write the code.

2.) NORTHWIND: Write a user defined function that will accept as inputs a product ID and which will return the most recent date on which that product was ordered. Then write a query (not a stored procedure) that will list all product names, the value of inventory on hand (unit price x units in stock), and the last time the product was ordered by using the UDF you wrote.

1. Hospital Schedule Diagram Below

Bed Hat Room BedCount Bed Type Room Type occupied (y/n) DepartmentID Has Bed Assignment Has Admission Patient ID End Time StartTime BedNum EndTime Room Num Reason AdmissionID Admit PhysicianID Hass Orders Ha Patient Physician LastName LastName FirstName FirstName Gender Gender DOB LicenseNum Orders Has Procedure Order Type Procedure Name PatientID Description Has order Type PhysicianID OrderDate ProcedureID Lab Test Type LabTestID LabTestlD TestName Description Has Has Vitalsigns LabResults Performorder TestTime OrderID Results Results Performed ByID Performed ByID Performed ByID PerformTime OrderID Order ID Department DeptName Office Number CurrentMgrID Has Shift DepartmentID StartTime EndTime Has ShiftJob Quantity Has ShiftAssign JobTypelD Arrive Time DepartTime Takes Ha Has Ha Perform EmpAssignment EmployeeID DepartmentID JobTypeID EndDate Has Job Type JobName Description Has EmpJobQual StartDate EndDate Has Employee Last Name FirstName DateOf Birth Gender Title Has Manages

2. NORTHWIND Diagram Below

Answer 1

Name whene e.efku°=d.derkvo