Use the two diagram below Northwind and Hospital data models diagrams to answer

Question

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

11. HOSPITAL: List the names of all physicians and the names of all patients they admitted during May 2015. List physicians whether or not they had any admissions.

12. NORTHWIND: Reflect the fact that the contact person for existing supplier “Fresh Flounder Fillets” has changed to Wilfred Wilcox and Wilfred’s phone number is 321-945-1962.

13. HOSPITAL: Write ALL QUERIES needed to reflect the fact that existing Employee 1765 changed jobs on 2015-07-01. Their new job is in Department 19 and their new job is job type 645.

1. Hospital Schedule Diagram Below

2. NorthWind 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

Explanation / Answer

1. NORTHWIND: List the company names and web sites of all suppliers.
Select CompanyName, HomePage
From Suppliers

2. NORTHWIND: List employee hire dates and first and last names in that order.
Select HireDate, FirstName, LastName
From Employees
Order by HireDate, FirstName, LastName

3. NORTHWIND: List the Order IDs for all orders where a discount was given.
Select OrderID
From OrderDetails
Where Discount>0

4.HOSPITAL: List all of the information in the table for admissions on June 2nd, 2015.
Select *
From Admission
Where StartTime='06-02-2015'

5. NORTHWIND: List all of the Canadian customers.
Select *
From Customers
Where Country='Canada'
6. NORTHWIND: List the Order ID, Employee ID, and order dates for all orders shipped to Spain in January 2015.
Select OderID, EmployeeID, OrderDate
From Orders
Where ShipCountry='Spain' and ShippedDate=''

7. NORTHWIND: List all of the order numbers and order dates processed by employee Fred Smith.
Select O.OrderID, O.OrderDate
From Orders O Inner Join Employees E
On O.EmployeeID=E.EmployeeID
Where E.FirstNamr='Fred' and E.LastName='Smith'

8. HOSPITAL: List the names of all employees ever qualified as a Phlebotomist.
Select E.FirstName, E.LastName
From Employee E
Inner Join
(
Select E.EmployeeID
From EmpJobQual E Inner Join JobType J
Where J.JobName='Phlebotomist'
)As B
On E.EmployeeID=.EmployeeID

10. NORTHWIND: The Order Details Discount field contains fractional values such as 0.02, 0.05 but also zeros and these values represent a percentage discount off of the total order amount.
E.g., 0.02 represents a 2% discount.
List all of the products sold in May 2015 and for each sale the total value of the discount.
Select P.ProductName
From Products P
Inner Join
(
Select OD.ProductID, (OD.UnitPrice*OD.Quantity)
,(OD.unitprice*OD.quantity) - ((OD.unitprice*OD.quantity)-(OD.discount*100))
From OrderDetails OD inner join Orders O
On OD.OrderID=O.OrderID
Where O.OrderDate between'05-01-2015'and '05-31-2015'
)As B
On P.ProductID=B.ProductID

11. HOSPITAL: List the names of all physicians and the names of all patients they admitted during May 2015. List physicians whether or not they had any admissions.
Select A.FirstName, A.LastName, B.FirstName, B.LastName, B.StartTime
From Physician A Left Join
(
Select P.FirstName, P.LastName, A.StartTime,A.AdmitPhysianID
From Patient P Inner Join Admission A
On P.PatientID=A.PatientID
Where A.StartTime between'05-01-2015'and '05-31-2015'
)As B
On A.PhysianID=B.AdmitPhysianID

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.