Hello, Can you help me How to code those MySQL questions please MySQL Hands-On #

Question

Hello, Can you help me How to code those MySQL questions please MySQL Hands-On #1: Joins & Grouping Prerequisites: These exercises utilize the sample database from MySqlTutorial.org (Links to an external site.)Links to an external site.. You will need to have downloaded and run the installation script before attempting these exercises. Deliverable: All queries should be written in a single .sql script file. The database schema is: 1. Write a query to display each customer’s name (as “Customer Name”) alongside the name of the employee who is responsible for that customer’s orders. The employee name should be in a single “Sales Rep” column formatted as “lastName, firstName”. The output should be sorted alphabetically by customer name. 2. Determine which products are most popular with our customers. 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. 3. 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. 4. 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. 5. 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”. 6. 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

If you have any doubts, please give me comments..

-- 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;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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