the question is on strategic managment information system charper 6 and i need t

Question

the question is on strategic managment information system charper 6 and i need the answer ASAP

Module #06 : Retrieving info from Databases Case Description: Northwind Traders, an international gourmet food distributor, is concerned about product sales, performance of sales representatives, shipping delays, and many other factors that affect their business. You will be required to create five queries and export each query to Excel for further data analysis. You can modify the data in Excel as needed.

Explanation / Answer

Query 1-

select companyName,contactTitle,customerPhone,orderId,orderDate,shippedDate,employeeId,employeeFirstName,employeeLastName, (datediff(orderDate,shippedDate)+1) as DaysToShip from <tableName> where DaysToShip > 30 INTO OUTFILE 'qry_1_shipping_delays.csv'

As table name is not mentioned i am using <tableName>. Please use your table name over here.

Query 2-

select orderId,orderDate,concat('$',format((unitPrice*Quantity),2)) as TotalOrderAmt, concat('$',format((Quantity*unitPrice*discount),2)) as DiscountAmount from <tableName> where YEAR(STR_TO_DATE(orderDate, "%m/%d/%y")) = 2015 orderby OrderDate INTO OUTFILE 'qry_2_ordersummery_2015.csv'

here YEAR is used to calculate the year of particular date and STR_TO_DATE is specifing the column and format of date.

Query 3-

select CONCAT('employeeFirstName',' ','employeeLastName') as Name, sum(concat('$',format((unitPrice*Quantity),2))) as TotalOrderAmt from <tableName> where YEAR(STR_TO_DATE(orderDate, "%m/%d/%y")) = 2016 and EmployeeType = 'sales representative' groupBy Name OrderBy TotalOrderAmt desc INTO OUTFILE 'qry_3_sales_reportorder_2016.csv'

Query 4-

select customerCountry, concat('$',format((unitPrice*Quantity),2)) as TotalOrderAmt, avg(TotalOrderAmt), min(TotalOrderAmt), max(TotalOrderAmt) from <tableName> orderby customerCountry groupBy customerCountry INTO OUTFILE 'qry_4_salesbycountry.csv';

Query 5-

select shippingToCountry, (quantity*unitPrice) as TotalSales, TotalFreightCost, ((TotalFreightCost/TotalSales)* 100) from <tableName> INTO OUTFILE 'qry_5_shppingcostbycountry.csv'

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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