The language is T-SQL
The database diagram is shown below.
For more help/ Information, please see the Example below the question.
Question:
The Northwind database diagram is shown below
Using the Northwind database, write a query that shows the number of product units that need to be ordered. When the inventory (units on order plus units in stock) falls below the reorder level, then order enough additional units so that there is 25% of the ReorderLevel units becoming available. When calculating the additional units to order, take into account the number of units already in stock and on order. Do not order fractional units, you must calculate the next highest integer unit value. For example, if your calculation says you need to order 3.4 units, then order 4 units of that product. Your result should show the Product Name and the number of units to order. The column headers must be "Product Name" and Units to Order". The correct result has two rows in the result set. Example Say we have a reorder level of 100 units. We currently have 50 units in stock and 25 units ordered (total expected inventory of 75 units). We need to calculate the number of additional units to order to bring the total expected inventory up to 125 (25% more than the 100 unit reorder level). So, we need to order an additional 50 units. Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24) Using the Northwind database, write a query that shows the number of product units that need to be ordered. When the inventory (units on order plus units in stock) falls below the reorder level, then order enough additional units so that there is 25% of the ReorderLevel units becoming available. When calculating the additional units to order, take into account the number of units already in stock and on order. Do not order fractional units, you must calculate the next highest integer unit value. For example, if your calculation says you need to order 3.4 units, then order 4 units of that product. Your result should show the Product Name and the number of units to order. The column headers must be "Product Name" and Units to Order". The correct result has two rows in the result set. Example Say we have a reorder level of 100 units. We currently have 50 units in stock and 25 units ordered (total expected inventory of 75 units). We need to calculate the number of additional units to order to bring the total expected inventory up to 125 (25% more than the 100 unit reorder level). So, we need to order an additional 50 units. Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24)
