Create & Query a Database
Analyze the Data
These data sets are in the form of simple return merchandise authorizations (RMAs) for an electronics company making network switches.
This creates the container for the databases and then lists the databases available.
Here I created a table within the QuantigrationUpdates database to define the schema. I assigned data types and primary key. Each datatype is customized to the data it will apply to.
Here, I created the table Orders that includes the fields needed and also includes PRIMARY KEY AND FOREIGN KEY. The foreign key references the CustomerID in the Customers table.
Here, I created a table called RMA which uses a primary key of RMAID and a foreign key of OrderID. The OrderID is from the orders table and connects the order to the RMA record if there is a return.
Load and Query the Data
Here, I loaded the customers.csv file into my Customers table. I used ‘\n’ instead of ‘\n\r’ because the \r was creating a problem that would not allow me to import it otherwise.
Here, I completed the same step as above but for the file orders.csv
Again, I imported another csv file with the RMA information called rma.csv and this populated the RMA table.
Here, I am counting the number of orders (OrderID) where the state in the customers table = Massachusetts and the city in the customers table = Framingham. So it is only counting the orders in Framingham, Massachusetts. There are 505 records that meet the condition.
Here I’m only counting the records in the Customers table where the state is Massachusetts. There are 982 records.
Here, I entered each of the values in the below table. I entered the names of the states spelled out. I did this because I took a look at some sample records and they all showed the name spelled out. For consistency, I changed them. For instance, where it says NY, I entered New York. This code adds each of the records to the Customers table.
| CustomerID | FirstName | LastName | StreetAddress | City | State | ZipCode | Telephone |
| 100004 | Luke | Skywalker | 15 Maiden Lane | New York | NY | 10222 | 212-555-1234 |
| 100005 | Winston | Smith | 123 Sycamore Street | Greensboro | NC | 27401 | 919-555-6623 |
| 100006 | MaryAnne | Jenkins | 1 Coconut Way | Jupiter | FL | 33458 | 321-555-8907 |
| 100007 | Janet | Williams | 55 Redondo Beach Blvd | Torrence | CA | 90501 | 310-555-5678 |
Here, I entered 4 records into the Orders table. This includes entering the OrderID and CustomerID as INT so no quotes needed around those numbers.
Orders Table
| OrderID | CustomerID | SKU | Description |
| 1204305 | 100004 | ADV-24-10C | Advanced Switch 10GigE Copper 24 port |
| 1204306 | 100005 | ADV-48-10F | Advanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber |
| 1204307 | 100006 | ENT-24-10F | Enterprise Switch 10GigE SFP+ 24 Port |
| 1204308 | 100007 | ENT-48-10F | Enterprise Switch 10GigE SFP+ 48 port |
Here I am counting all records that match city as Woonsocket AND state as Rhode Island. There are 7 records.
Here I am only selecting to see the status and step for order 5175 which has the status of Pending and the step as Awaiting customer Documentation.
Here, the code goes to the RMA table and searches for order 5175. Then it changes the Status to “Complete” and the Status to “Credit Customer Account”
Here, there were 596 records deleted. After some troubleshooting, I found that the Reason column includes the data “Rejected” but when I ran queries, it was telling me the set was empty. When I used the SELECT LENGTH clause, I was able to see it was 9 characters when there are only 8 in the word rejected. All worked well once I did that.
This Changes the entire table name from Customers to Collaborators.
This changes the column names only from CustomerID to CollaboratorID in the Collaborators table.
This does the same in the Orders table. It changes the column name from CustomerID to CollaboratorID.
This exports the results of the query to a csv file.
Analysis
Analyze sales data by state to determine where the company has the largest customer base.
To find the total number of customers for the state of Massachusetts, I selected to count the orders and break them down by state. I joined the Customers and the Orders table because I’m using OrderID from the Orders table to count the records and then State from the Customer’s table so we can see how many orders by state. To find the state with the highest number of customers, I could have also used the MAX function.
I used this query to count the number of unique products (Description) in the Orders table to find the top 3 products sold I the US and I added LIMIT 3 to just show the top 3 rows.
Analyze the data to determine the top three products sold in the southeastern region of the United States.
The top three products from Virginia, North Carolina, South Carolina and Georgia are all the same product but they sell best in Georgia, North Carolina, and South Carolina:
| Description | Total_Sales |
+—————————————-+————-+
| Basic Switch 10/100/1000 BaseT 48 port | 504 |
| Enterprise Switch 40GigE SFP+ 48 port | 337 |
| Basic Switch 10/100/1000 BaseT 8 port | 257 |
This shows the top 3 products where the states match Virginia, North Carolina, South Carolina or Georgia from the customers table. I joined it with the Orders table so that I could show the number of products sold in the 4 states combined. I used the DISTINCT clause to only show the top 3 unique products. Without the DISTINCT clause, it would repeat the same products.
Analyze the data to determine the top three products returned in the United States.
Here, I selected to show the descriptions instead of SKU numbers to represent the products. I also included a COUNT function to count the number of products returned without any further filtering. Then I ordered it by the count in descending order so when I included the LIMIT 3, it just showed the top 3 results.
These are the top 3 products returned:
| Basic Switch 10/100/1000 BaseT 48 port | 8282 |
| Enterprise Switch 40GigE SFP+ 48 port | 6118 |
| Enterprise Switch 10GigE SFP+ 48 port | 4287 |
Analyze the data to determine the top three products returned in the northwestern region of the United States.
I used this query to look at all of the returns from the 4 states combined where it gives me a list of all of the products returned in those state and lists which product is returned and how many of them returned. It’s quite evident that the company has a problem with shipping the incorrect items.
The top 3 products returned from Washington, Oregon, Idaho and Montana:
Reason | Description | COUNT(*) |
+———–+—————————————————-+———-+
| Incorrect t | Basic Switch 10/100/1000 BaseT 48 port | 248 |
| Other | Basic Switch 10/100/1000 BaseT 48 port | 225 |
| Defective | Basic Switch 10/100/1000 BaseT 48 port | 224 |
Report
We evaluated 37,566 returns and 37,994 sales to see what type of insights could be discovered. This is what we have found.
The company’s largest customer base is in Massachusetts with 982 customers. The next largest are Arkansas and West Virginia, respectively.
The top three products sold in the U.S. are:
Product Total Sales
Basic Switch 10/100/1000 BaseT 48 port | 8385
Enterprise Switch 40GigE SFP+ 48 port | 6186
Enterprise Switch 10GigE SFP+ 48 port | 4328
The top three products sold in Virginia, North Carolina, South Carolina, and Georgia are all the same product, but they sell best in Georgia, North Carolina, and South Carolina:
Product Total Sales
Basic Switch 10/100/1000 BaseT 48 port | 504
Enterprise Switch 40GigE SFP+ 48 port | 337
Basic Switch 10/100/1000 BaseT 8 port | 257
- Returns data by region: Provide a well-written summary of findings from your analysis in Step Two, Part B.
These are the top 3 products returned:
Product Total Returns
Basic Switch 10/100/1000 BaseT 48 port | 8282
Enterprise Switch 40GigE SFP+ 48 port | 6118
Enterprise Switch 10GigE SFP+ 48 port | 4287
The highest rate of return is in Massachusetts at 354 returns with 982 total customers in the state.
The lowest rate of returns is from Vermont with 793 sales and only 209 returns.
You may notice that our top 3 products are also the same products with the highest rate of returns. This may indicate a problem that we will explore further below.
The top 3 products returned from Washington, Oregon, Idaho and Montana:
Narrowing in on Washington, Oregon, Idaho, and Montana. We find the following.
Reason for Return Product Total Returns
Incorrect t | Basic Switch 10/100/1000 BaseT 48 port | 248
Other | Basic Switch 10/100/1000 BaseT 48 port | 225
Defective | Basic Switch 10/100/1000 BaseT 48 port | 224
Another interesting insight discovered is about South Carolina. The lowest sales are in South Carolina with 713 Sales and 702 returns. There must be factors affecting the significant number of returns that are not obvious. The reasons for return are equivalent across the board for South Carolina.
Reason for Return Number of Returns
Defective 227
Incorrect 242
Other 233
The most defective items go to Massachusetts
The most Incorrect items go to Idaho
The most Other items go to Massachusetts.
I would recommend further research into Massachusetts as that is a State where our sales and returns are volatile. If we have a State with high sales, it negates the benefits if the rate of returns is as significant as they are here.
Additionally, it is worth noting that the majority of the returns are due to incorrect products. This would indicate that closer attention is needed when preparing orders for shipping to verify the correct products are in the order.