University:
Southern New Hampshire University
Course:
DAD220 | Introduction to Structured Query Language
Academic year:

2023
Views:

380

Pages:

2
Author:

Rylee Bernard

Major activity 5-2 1. A. SELECT State, COUNT(*) FROM Customers GROUP BY State ORDER BY COUNT(*); Massachusetts 982 B. SELECT Orders.SKU, COUNT(*) FROM Orders GROUP BY Orders.SKU ORDER BY COUNT(*) DESC; Bas-48-1 C 8385 ENT-48-40F 6186 ENT-48-10F 4328 C. SELECT Orders.SKU, COUNT(*) FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.State IN (‘North Carolina’, ‘South Carolina’, ‘Georgia’, ‘Virginia’) GROUP BY Orders.SKU ORDER BY COUNT(*) DESC; BAS-48-1 C 504 ENT-48-40F 337 BAS-08-1 C 257 2. A. SELECT Orders.SKU, Count(*) FROM Orders INNER JOIN RMA ON RMA.OrderID = OrderID GROUP BY Orders.SKU ORDER BY Count(*) DESC; BAS-48-1 C 8282 ENT-48-40F 6118 ENT-48-10F 4287 B. SELECT Orders.SKU, COUNT(*) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN RMA ON Orders.OrderID = RMA.OrderID WHERE Customers.State IN (‘Washington’, ‘Oregon’, ‘Idaho’, ‘Montana’) GROUP BY Orders.SKU ORDER BY COUNT(*) DESC; BAS-48-1 C 697 ENT-48-40F 534 BAS-08-1 C 379 Summary: 1. Using select statements we were able to take orders from the SKU column to order the count of products from highest to lowest. We were also able to count customers per state by grouping them by state and ordering them in the same way to see the most sold products in the United States. Now for doing a number of states we take the orders from SKU again and join them with orders and customers with customer and search them within the states we say and order them in descending order. 2. Now we want to find what products were returned in the united states we again select the KSU column from the orders table and join it with the RMA table with order IDs and group it with SKU orders to order it in descending order to show the top 3 products returned in the united states. If we wanted to look at it by separate states we again select the SKU Column and count from customers and join the orders on customers and customerID we then join RMA with orders and RMA where we count the customers and what they bought and then specify the states we want and group it with SKU and order the products these customers bought in these states in descending order to find that data.

Intro to SQL: Major Activity Module 5

Related Documents

Description

Related Documents

Free up your schedule!

Take 5 seconds to unlock