Project Two
docx
keyboard_arrow_up
School
Gadsden State Community College *
*We aren’t endorsed by this school
Course
310
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
3
Uploaded by BailiffGorillaMaster153
7-1 Project Two: Analyzing Databases
1.
Begin by writing SQL commands to capture usable data (which you’ve preloaded into Codio) for your analysis.
2.
Specifically, the product manager wants you to analyze the following:
Analyze the number of returns by state and describe your findings in your report.
Analyze the percentage of returns by product type and describe your findings in your report
To find out how many returns were made in each state, I put in the command:
SELECT Customers.State AS STATE, COUNT(*) AS RETURN_FREQUENCY
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
GROUP BY STATE
ORDER BY RETURN_FREQUENCY DESC
LIMIT 10;
I don’t think the states showed up because I made a couple of mistakes on my last
assignment but I know it is supposed to list the states and the return frequency in a
chart.
And in this screen shot above I put in the following commands:
SELECT Customers.State AS STATE, (COUNT(*) * 100 / (SELECT COUNT(*) FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID)) AS RETURN_PERCENTAGE
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
GROUP BY STATE
ORDER BY RETURN_PERCENTAGE DESC
LIMIT 10;
SELECT sku AS PRODUCT_SKU, description AS PRODUCT_DESCRIPTION, COUNT(*) AS RETURN_FREQUENCY
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
GROUP BY PRODUCT_SKU
ORDER BY RETURN_FREQUENCY DESC\G
SELECT sku AS PRODUCT_SKU, description AS PRODUCT_DESCRIPTION, (COUNT(*) * 100 / (SELECT COUNT(*) FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID)) AS RETURN_PERCENTAGE
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
GROUP BY PRODUCT_SKU
ORDER BY RETURN_PERCENTAGE DESC\G
Since I messed up in the last assignment, it did not work. I do know that it was supposed to output the percentage of returns by product type and the product SKU lists.
3.
In your report, clearly summarize your analysis of the data for stakeholders. Include screenshots of the results of each query. When summarizing results, you may want to consider the following questions:
How does the data provide the product manager with usable information? This data helps represent the area where most of the returns happen so that in the future, they can understand why the particular place has the most returns.
What are the potential flaws in the data that has been presented? A potential flaw is that the data could be entered wrong which would give false information when collecting data
reports.
Are there any limitations on your conclusions, or any other ways of looking at it that you haven’t considered? Clearly communicate your findings to stakeholders. I wasn’t able to see what states had the highest number of returns since I messed up on my last assignment but I did try to put in the commands and I have an understanding of what the commands were supposed to output. This data can help with learning what is causing the high number of returns and can help find a solution to why there are so many returns.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help