1. For each product, list the total quantity ordered along with the total sale g

Question

1. For each product, list the total quantity ordered along with the total sale generated (total quantity ordered * buyPrice) for that product. The column headers should be “Product Name”, “Total # Ordered” and “Total Sale”. List the products by Total Sale descending.

2. Write a query which lists order status and the # of orders with that status. Column headers should be “Order Status” and “# Orders”. Sort alphabetically by status.

3. Write a query to list, for each product line, the total # of products sold from that product line. The first column should be “Product Line” and the second should be “# Sold”. Order by the second column descending.

4. For each employee who represents customers, output the total # of orders that employee’s customers have placed alongside the total sale amount of those orders. The employee name should be output as a single column named “Sales Rep” formatted as “lastName, firstName”. The second column should be titled “# Orders” and the third should be “Total Sales”. Sort the output by Total Sales descending. Only (and all) employees with the job title ‘Sales Rep’ should be included in the output, and if the employee made no sales the Total Sales should display as “0.00”.

5. Your product team is requesting data to help them create a bar-chart of monthly sales since the company’s inception. Write a query to output the month (January, February, etc.), 4-digit year, and total sales for that month. The first column should be labeled ‘Month’, the second ‘Year’, and the third should be ‘Payments Received’. Values in the third column should be formatted as numbers with two decimals – for example: 694,292.68.

Explanation / Answer

-- 1)

SELECT customerName AS 'Customer Name', lastName, firstName

FROM customers C, employees E

WHERE C.salesRepEmployeeNumber = E.employeeNumber

ORDER BY `Customer Name`;

-- 2)

SELECT productName AS "Product Name", SUM(quantityOrdered) AS 'Total # Ordered', SUM(quantityOrdered * priceEach) AS 'Total Sale'

FROM products p, orderdetails o

WHERE p.productCode = o.productCode

ORDER BY `Total Sale`;

-- 3)

SELECT status AS 'Order Status', COUNT(*) AS '# Orders'

FROM orders

GROUP BY status

ORDER BY `Order Status`;

-- 4)

SELECT productLine AS 'Product Line', COUNT(productCode) AS '# Sold'

FROM products p

ORDER BY `# Sold`;

-- 5)

SELECT CONCAT(lastName,'', firstName) AS 'lastName, firstName', SUM(quantityOrdered) AS '# Orders', SUM(quantityOrdered*priceEach) AS 'Total Sales'

FROM employees E LEFT OUTER JOIN customers C ON E.employeeNumber = C.salesRepEmployeeNumber JOIN orders O ON C.customerNumber = O.customerNumber JOIN orderdetails OD ON o.orderNumber = OD.orderNumber

GROUP BY lastName, firstName;

-- 6)

SELECT DATENAME(month, orderDate) AS 'Month', DATENAME(year, orderDate) AS 'Year', ROUND(SUM(quantityOrdered*priceEach),2) AS 'Payments Received'

FROM orders O, orderDetails OD

WHERE O.orderNumber = OD.orderNumber

GROUP BY orderDate;

Let me know if you have any clarifications. Thank you...

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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