Question
Please answer all three questions below using the Northwind and Hospital data models diagrams below. 1.) NORTHWIND: Write a query to list the customer company
Please answer all three questions below using the Northwind and Hospital data models diagrams below.
1.) NORTHWIND: Write a query to list the customer company names and order dates for all orders placed in the first seven days of ANY AND ALL months during 1997. Have the results ordered by month and then by Day with each month.
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.
3.) 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.
1. Hospital Schedule Diagram Below
2. NORTHWIND Diagram Below
Bed Hat Room BedCount Bed Type Room Type occupied (y) 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 ManagesStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started