Mentorness Internship Task-2

MENTORNESS
INTERNSHIP
P R E S E N T E D BY M D M O H S H I N K H A N
SQL PROJECT
WALMART SALES ANALYSIS (TASK 2)
SQL
Structured query language (SQL) is a

programming language for storing and
processing information in a relational database.
A relational database stores information in
tabular form, with rows and columns
representing different data attributes and the
various relationships between the data values.
You can use SQL statements to store, update,
remove, search, and retrieve information from
the database. You can also use SQL to maintain
and optimize database performance.
Project
Project
Overview
Overview
• In this project SQL is used to
answer the questions related to
dataset.
• The sales industry relies on data
to make informed decisions and
provide a better customer
experience.
• A sales dataset is used to gain
insights into customer preference,
purchase trends and other key
factor that impact the sales
operation.
Dataset
Details
• The dataset consists of the following columns:
• Invoice_ID: Unique identifier for each invoice.
• Branch: Branch of the store where the sale took
place.
• City: City where the store branch is located.
• Customer_type: Type of customer (e.g., Member,
Normal).
• Gender: Gender of the customer.
• Product_line: Category of the product sold.
• Unit_price: Price per unit of the product.
• Quantity: Quantity of the product sold.
• Tax_5%: Tax applied on the sale.
• Total: Total amount of the sale.
• Date: Date of the transaction.
• Time: Time of the transaction.
• Payment: Payment method used (e.g., Cash,
Credit Card).
• cogs: Cost of goods sold.
• gross_margin_percentage: Gross margin
TASKS
1. Retrieve all columns for sales made in a specific branch (e.g., Branch
'A').
2. Find the total sales for each product line.
3. List all sales transactions where the payment method was 'Cash'.
4. Calculate the total gross income generated in each city.
5. Find the average rating given by customers in each branch.
6. Determine the total quantity of each product line sold.
7. List the top 5 products by unit price.
8. Find sales transactions with a gross income greater than 30.
9. Retrieve sales transactions that occurred on weekends.
10. Calculate the total sales and gross income for each month.
11. Find the number of sales transactions that occurred after 6 PM.
12. List the sales transactions that have a higher total than the average
total of all transactions.
13. Calculate the cumulative gross income for each branch by date.
14.Write a SQL query calculate the cumulative gross income for each branch by date
15. Find the total cogs for each customer type in each city.
1.Write a SQL query for retrieve all
columns for sales made in a
specific branch
Solution
SELECT * FROM Walmart_Sales WHERE
Branch = 'A';
2.Write a SQL query for find the
total sales for each product line.
Solution
SELECT Product_line, SUM(Total) AS
Total_Sales FROM walmart_sales
GROUP BY Product_line;
3.Write a SQL query for list all
sales transactions where the
payment method was 'Cash'
Solution
SELECT *FROM walmart_sales
WHERE Payment = 'Cash';
Ingoude
Compan
y
4.Write a SQL query for calculate
the total gross income generated
in each city
Solution
uSELECT City, SUM(gross_income) AS
Total_Gross_Income FROM
walmart_sales GROUP BY City;
average rating given by customers
in each branch
Solution
SELECT Branch, AVG(Rating) AS
Average_Rating FROM walmart_sales
GROUP BY Branch;
6.Write a SQL query for determine
the total quantity of each product
line sold.
Solution
SELECT Product_line, SUM(Quantity)
AS Total_Quantity_Sold FROM
walmart_data GROUP BY Product_line;
7.Write a SQL query for list the top
5 products by unit price.
Solution
SELECT Product_line, Unit_price FROM
walmart_data ORDER BY Unit_price
DESC LIMIT 5;
8.Write a SQL query for find sales
transactions with a gross margin
percentage greater than 30%.
Solution
SELECT * FROM walmart_data WHERE
gross_margin_percentage > 30;
9.Write a SQL query for retrieve
sales transactions that occurred on
weekends.
Solution
STRFTIME('%w', Date) IN ('0', '6');
10.Write a SQL query for Calculate
the total sales and gross income
for each month.
Solution
SELECT STRFTIME('%Y-%m', Date) AS
Month, SUM(Total) AS Total_Sales,
SUM(gross_income) AS
Total_Gross_Income FROM
walmart_data GROUP BY
STRFTIME('%Y-%m', Date) ORDER BY
Month;
number of sales transactions that
occurred after 6 PM
Solution
SELECT COUNT(*) AS
Transactions_After_6PM FROM
walmart_data WHERE STRFTIME('%H',
Time) >= '18';
12.Write a SQL query for list the
sales transactions that have a
higher total than the average total
of all transactions
Solution
Total > (SELECT AVG(Total) FROM
walmart_data);
13.Write a SQL query for find
customers who made more than 5
purchases in a single month
Solution
SELECT Customer_type,
STRFTIME('%Y-%m', Date) AS Month,
COUNT(*) AS Number_of_Purchases
FROM walmart_data GROUP BY
Customer_type, STRFTIME('%Y-%m',
Date) HAVING COUNT(*) > 5;
14.Write a SQL query calculate the
cumulative gross income for each
branch by date.
Solution
SELECT Branch, Date,
SUM(gross_income) OVER (PARTITION
BY Branch ORDER BY Date) AS
cumulative_gross_income FROM
walmart_data ORDER BY Branch,
Date;
15.Find the total cogs for each
customer type in each city.
Solution
SELECT City, Customer_type,
SUM(cogs) AS total_cogs FROM
walmart_data GROUP BY City,
Customer_type ORDER BY City,
Customer_type;

Mentorness Internship Task-2

Uploaded by

Copyright:

Available Formats

Mentorness Internship Task-2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Mentorness Internship Task-2

Uploaded by

Copyright:

Available Formats

MENTORNESS

Structured query language (SQL) is a

You might also like