Instructions For this assessment, you will be building on creating reports using
ID: 3745803 • Letter: I
Question
Instructions For this assessment, you will be building on creating reports using complex queries that join multiple tables and use group functions. Continue to use the database you created in previous assessments. These reports will be going to the sales and marketing department. Use external research and the suggested resources to design your infrastructure. Expanding on the reports previously created, there are a few more reports that require joins and group functions. Given the greater complexity, you need to add to the script to include at least 10 more orders with 1-3 products for each to help with testing purposes. For this report, complete the following queries: 1. A list of customer names with the number of orders that have ordered. 2. A list of all customers that have ordered a product within the last 30 days 3. A list of customer name, order date, and product name ordered by the customer name. 4. A list of product name and the category name that they belong to 5. A list of each product and the total amount that they have sold for along with their average number of units sold. Include products that have been sold. 6. A list of states with the number of units that have been sold to that state. 7. A list of month, year, and average order total for each month 8. A list of mailing addresses that have at least 5 orders. 9. A list of the most popular products sold (consider quantity) with the number of items they have sold. 10. A list of product names that belong to the same category as the most popular product. Create a cohesive script using all of these queries to be used in a report.Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT FirstName, LastName, COUNT(OrderID) AS NoOfOrders
FROM CUSTOMER C INNER JOIN ORDER O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, FirstName, LastName;
-- 2)
SELECT C.CustomerID, FirstName, LastName
FROM CUSTOMER C INNER JOIN ORDER O ON C.CustomerID = O.CustomerID
WHERE DATEDIFF(DAY, SYSDATE, OrderDate)<=30;
-- 3)
SELECT FirstName, LastName, OrderDate, ProductName
FROM CUSTOMER C INNER JOIN ORDER O ON C.CustomerID = O.CustomerID INNER JOIN [ORDER DETAIL] OD ON OD.OrderID = O.OrderID INNER JOIN PRODUCT P ON P.ProductID = OD.ProductID
ORDER BY FirstName, LastName;
-- 4)
SELECT ProductName, CategoryName
FROM PRODUCT P INNER JOIN [PICTURE CATEGORY] PC ON P.CategoryID = PC.CategoryID;
-- 5)
SELECT ProductName, SUM(Price*Quantity) total_amount, AVG(Quantity) AvgNoUnitsSold
FROM PRODUCT P INNER JOIN [ORDER DETAIL] OD ON P.ProductID = OD.ProductID
GROUP BY ProductName;
-- 6)
SELECT State, SUM(Quantity)
FROM CUSTOMER C INNER JOIN ORDER O ON C.CustomerID = O.CustomerID INNER JOIN [ORDER DETAIL] OD ON O.OrderID = OD.OrderID
GROUP BY State;
-- 7)
SELECT MONTH(OrderDate), YEAR(OrderDate), AVG(Quantity)
FROM [ORDER DETAIL]
GROUP BY MONTH(OrderDate), YEAR(OrderDate);
-- 8)
SELECT Email
FROM CUSTOMER C INNER JOIN ORDER O ON C.CustomerID = O.CustomerID INNER JOIN [ORDER DETAIL] OD ON O.OrderID = OD.OrderID
GROUP BY Email
HAVING COUNT(OrderID) >= 5;
-- 9)
SELECT ProductName
FROM Product P INNER JOIN [ORDER DETAIL] OD ON P.ProductID = OD.ProductID
GROUP BY P.ProductID
HAVING MAX(SUM(Quantity)) = (
SELECT MAX(SUM(Quantity))
FROM [ORDER DETAIL]
);