Based on the “classicmodels” DB in Mysql which is avilable here http://www.mysql

Question

Based on the “classicmodels” DB in Mysql which is avilable here http://www.mysqltutorial.org/how-to-load-sample-database-into-mysql-database-server.aspx

I need answers fo rthese quistion using SQL query :

Required Queries

1. Produce a report that has for each office postalCode, a sum of the number of payments .

2. Produce a report of customer postalCode sorted by orders(determined by count of the number of items ordered). Sort by orders descending.

3. Produce a report for each product line give count the number of orders

4. Produce a report that has the office code, manager name and count of the number of employees that report to that manager . Add a comment to the query indicating why the sum of the employees from this query is greater than the number of employees.

5. Find manager first name, sales person first name, customer name, customer phone for customers who ordered products that have less than 1000 in stock. Label the output (example Manager Employee Customer …)

6. Find employee with the most orders(determined by order quantity * price summed up)

7. List employee name(first and last name), customer phone number, count of the # comments on orders made by that customer that have these words in them( “reevaluate”, “cancel”, “concerned”)

8. For each sales person, for each customer , for each product ordered by the customer figure the average discount((MSRP-buyPrice)/MSRP)

Thanks in advance

Explanation / Answer

**
CREATE TABLE 'Orders' ('OrderID' INT UNSIGNED NOT NULL AUTO_INCREMENT,'CustomerID' VARCHAR(10),'EmployeeID' MEDIUMINT UNSIGNED NOT NULL,'OrderDate' DATE,'RequiredDate' DATE,'ShippedDate' DATE,'ShipVia'   TINYINT UNSIGNED,'Freight'   DECIMAL(10,2) UNSIGNED DEFAULT 0,'ShipName'   VARCHAR(0),'ShipAddress' VARCHAR(40),'ShipCity'    VARCHAR(25),'ShipRegion' VARCHAR(10),'ShipPostalCode' VARCHAR(5),'ShipCountry' VARCHAR(10),PRIMARY KEY ('OrderID'),INDEX ('OrderDate'),INDEX ('ShippedDate'),INDEX ('ShipPostalCode'),FOREIGN KEY ('CustomerID') REFERENCES 'Customers' ('CustomerID'),FOREIGN KEY ('EmployeeID') REFERENCES 'Employees' ('EmployeeID'),FOREIGN KEY ('ShipVia') REFERENCES 'Shippers'('ShipperID'));

**
CREATE TABLE 'offices'('officeCode' VARCHAR(10) NOT NULL,'city' VARCHAR(50) NOT NULL,'phone' VARCHAR(20) NOT NULL,'addressLine1' VARCHAR(50) NOT NULL,'addressLine2' VARCHAR(30) DEFAULT NULL,'state' VARCHAR(50) DEFAULT NULL,'country' VARCHAR(50) NOT NULL,'postalCode' VARCHAR(20) NOT NULL,'territory' VARCHAR(10) NOT NULL,PRIMARY KEY ('officeCode'),INDEX ('phone'),INDEX ('city'));

**
DELIMITER
CREATE PROCEDURE 'CustOrderList'(IN AtCustomerID VARCHAR(5))
BEGIN
SELECT ProductName, SUM(Quantity) as TOTAL
FROM Products
INNER JOIN 'Order Details' USING(ProductID)
INNER JOIN Orders USING (OrderID)
INNER JOIN Customers USING (CustomerID)
WHERE Customers.CustomerID = AtCustomerID
GROUP BY ProductName;
END $$
DELIMITER ;

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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