Assignment Instructions

User Manual:

Open the PDF directly: View PDF .
Page Count: 2

Download
Open PDF In Browser	View PDF

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.
2.
3.
4.
5.
6.
7.

Open MySQL Workbench
Connect to your database using the connection you have created
Create a database named superstoresDB
In the “Navigator” pane on the left hand side, you will find the created database
Right click on the superstoresDB
You will see the option called “Table Data Import Wizard”. Click on it.
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 subcategory 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 subcategory

Important Note:
Submit your answers for all these tasks in a .sql file.



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.7
Linearized                      : No
Page Count                      : 2
Language                        : en-IN
Tagged PDF                      : Yes
XMP Toolkit                     : 3.1-701
Producer                        : Microsoft® Word 2016
Creator                         : Meenal Borkar
Creator Tool                    : Microsoft® Word 2016
Create Date                     : 2017:06:22 15:52:09+05:30
Modify Date                     : 2017:06:22 15:52:09+05:30
Document ID                     : uuid:0593F235-1A66-4FF2-8B19-229F526060FB
Instance ID                     : uuid:0593F235-1A66-4FF2-8B19-229F526060FB
Author                          : Meenal Borkar

EXIF Metadata provided by EXIF.tools