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

ID: 3805926 • Letter: U

Question

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

1. Write a strored procedure that will receive two date values as parameters and return the customer company names, order ID values, and order dates for all orders placed during that period.

2. write a stored procedure that will receive all values needed for a new supplier except supplierID, create new supplier record, and return the value of the new record's supplierID in a RETURN statement. Set up the SP so that the default value for contry is 'USA.'

3. NORTHWIND: write a stored procedure that will receive A category name parameter and a country and return all product information for products in that category produced by suppliers from that country.

4. NORTHWIND: write a stored procedure that will receive all values needed for a new product except productID, create new product record, and return the value of the new record's productID in a RETURN statement. Set up the SP so that the default value for discontinued is 0 (ZERO).

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) Write a strored procedure that will receive two date values as parameters and return the customer company names, order ID values, and order dates for all orders placed during that period.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetOrders]'1998-05-02', '1998-05-06'
@StartDate [datetime],
@EndDate [datetime]
AS
BEGIN
SET NOCOUNT ON;
   Select CompanyName [CompanyName], OrderID, OrderDate
   From Orders O
   Inner Join Customers C On C.CustomerID=O.CustomerID
   Where OrderDate between @StartDate AND @EndDate

END;

CREATE PROCEDURE [dbo].[AddSupplier]
@CompanyName nvarchar(40),
@ContactName nvarchar(30) = NULL,
@ContactTitle nvarchar(30) = NULL,
@Address nvarchar(60) = NULL,
@City nvarchar(15) = NULL,
@Region nvarchar(15) = NULL,
@PostalCode nvarchar(10) = NULL,
@Country nvarchar(15) = NULL,
@Phone nvarchar(24) = NULL,
@Fax nvarchar(24) = NULL,
@HomePage ntext = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Suppliers]
([CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax]
,[HomePage])
VALUES
(@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
IsNull(@Country,'USA'),-- Insert USA as default when country is null
@Phone,
@Fax,
@HomePage)
Return @@IDENTITY -- This is identity or SupplierID column in this case

END;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetProducts]
@CategoryName nvarchar(15),
@CountryName nvarchar(15)
AS
BEGIN
SET NOCOUNT ON;
   Select distinct P.* From Products P
   Inner Join Categories C On P.CategoryID = P.CategoryID
   Inner Join Suppliers S On S.SupplierID = P.SupplierID
   Where C.CategoryName = @CategoryName AND S.Country = @CountryName

END

CREATE PROCEDURE [dbo].[AddProduct]
@ProductName nvarchar(40),
@SupplierID int= NULL,
@CategoryID int = NULL,
@QuantityPerUnit nvarchar(20) = NULL,
@UnitPrice money = NULL,
@UnitsInStock smallint = NULL,
@UnitsOnOrder smallint = NULL,
@ReorderLevel smallint = NULL,
@Discontinued bit = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Products]
([ProductName]
,[SupplierID]
,[CategoryID]
,[QuantityPerUnit]
,[UnitPrice]
,[UnitsInStock]
,[UnitsOnOrder]
,[ReorderLevel]
,[Discontinued])
VALUES
(@ProductName,
@SupplierID,
@CategoryID,
@QuantityPerUnit,
@UnitPrice,
@UnitsInStock,
@UnitsOnOrder,
@ReorderLevel,
ISnull(@Discontinued,0)) -- Default value 0 in case of Null
Return @@IDENTITY

END;

Navigate

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

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

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

Question

Explanation / Answer

Related Questions

Navigate