12 points) Improve customer service using database Northwind Traders, an interna

Question

12 points) Improve customer service using database Northwind Traders, an international gourmet food distributor, is concerned about shipping delays over the past six months. Review the orders over the past six months and identify any order that was not shipped within 30 days. Each customer that falls within that time frame will be called to inquire about any problems the delay may have caused. Open the attached Food.accdb database file and perform the following activities:

Open the Employees table. Add yourself as an employee. Fill in all information, with the hire data as today. Set your Title to Manager Assistant, Extension to 1144, and the Reports To field to Buchanan, Steven. Leave the EmployeePicture field blank. Close the Employees table. Create a query to calculate the number of days between the date an order was placed and the date the order was shipped for each order. Use Query Design, from the Customers table, include the fields CompanyName, ContactName, ContactTitle, and Phone; from the Orders table, include the fields OrderID, OrderDate, and ShippedDate; from the Order Details table, include the Quantity field; from the Products table, include ProductName field. Add a calculated field named DaysToShip to calculate the number of days taken to fill each order. (Hint: The expression will include the OrderDate and the ShippedDate; the results will not contain negative numbers.) Add criteria to limit the query results to include any order that took more than 30 days to ship in the past six months (based on the dates in the file, not actual time). Sort the query by ascending OrderID.

Please use the file located here: https://ufile.io/ywrkt

please upload the file to a file share when finished

Explanation / Answer

Hi,

Below is the code-

Ans 1 -
INSERT INTO EMPLOYEES VALUES(20001,'SINGH','VINAY','Manager Assistant','MA','1991-02-30','2012-02-30','CHENNAI','SOUTH INDIA','603103','INDIA','8056102870','1144','','SOME TEXT','Buchanan, Steven','',250000.0);

Ans 2 - select c.companyname,c.ContactName, c.ContactTitle,c.Phone,
o.OrderID, o.OrderDate, o.ShippedDate,DATEDIFF(DAY, o.OrderDate, o.ShippedDate) as DaysToShip,
od.Quantity,p.ProductName
from customer c join orders o
on c.customerid=o.customerid
join orderdetails od
on o.orderid=od.orderid
join products p
on od.productid=p.productid
where DaysToShip>30
and o.orderDate>=DATEADD(month, -6, orderDate)

Note: The last line is to filter the orders which are placed in last 6 months

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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