Assignment Instructions
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 2
SQL – Assignment
Instruction File
Task 0:
Download the data files for this assignment. Your first task is to create tables from these
files. In order to do so, please follow the steps given below sequentially:
1. Open MySQL Workbench
2. Connect to your database using the connection you have created
3. Create a database named superstoresDB
4. In the “Navigator” pane on the left hand side, you will find the created database
5. Right click on the superstoresDB
6. You will see the option called “Table Data Import Wizard”. Click on it.
7. Follow the wizard to create tables by providing the .csv data files that you have
downloaded
8. You need to follow the “Table Data Import Wizard” for each data file given for this
assignment.
Please refer https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html
to get more information on table data import. Once you are done with this task, attempt
following tasks:
Task 1: Understanding the data in hand
A. Describe the data in hand in your own words. (Word Limit is 500)
B. Identify and list the Primary Keys and Foreign Keys for this dataset (Hint: If a table
don’t have Primary Key or Foreign Key, then specifically mention it in your answer.)
Task 2: Basic Analysis
Write the SQL queries for the following:
A. Find the total and the average sales (display total_sales and avg_sales)
B. Display the number of customers in each region in decreasing order of
no_of_customers. The result should contain columns Region, no_of_customers
C. Find the region having maximum customers (display the region name and
max(no_of_customers)
D. Find the number and id of products sold in decreasing order of products sold (display
product id, no_of_products sold)
E. Find all the customers from Atlantic region who have ever purchased ‘TABLES’ and
the number of tables purchased (display the customer name, no_of_tables
purchased)
Task 3: Advanced Analysis
Write sql queries for the following:
A. Display the product categories in descending order of profits (display the product
category wise profits i.e. product_category, profits)?
B. Display the product category, product sub-category and the profit within each sub-
category in three columns.
C. Where is the least profitable product subcategory shipped the most? For the least
profitable product sub-category, display the region-wise no_of_shipments and the
profit made in each region in decreasing order of profits (i.e. region,
no_of_shipments, profit_in_each_region)
o Note: You can hardcode the name of the least profitable product sub-
category
Important Note:
Submit your answers for all these tasks in a .sql file.
