Scribd
Upload
646 views3 pages

GIVA SQL Assignment: Q) Create A Sales Table With The Following Fields

The document describes a SQL assignment to create a sales table with customer, purchase, and product fields. It provides the table structure and sample data. It then lists 4 questions and the SQL queries to answer each question: 1) name customers who purchased more than once and total amount, 2) highest selling item, 3) average number of purchases per customer per month, and 4) customers who spent more than a given amount (Rs. 1000 in this case).

Uploaded by

Aditi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
646 views3 pages

GIVA SQL Assignment: Q) Create A Sales Table With The Following Fields

The document describes a SQL assignment to create a sales table with customer, purchase, and product fields. It provides the table structure and sample data. It then lists 4 questions and the SQL queries to answer each question: 1) name customers who purchased more than once and total amount, 2) highest selling item, 3) average number of purchases per customer per month, and 4) customers who spent more than a given amount (Rs. 1000 in this case).

Uploaded by

Aditi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

GIVA SQL Assignment

Q) Create a sales table with the following fields:


Customer Name

Email

Phone

Date of purchase

SKUs bought (multiple comma separated values)

Total price

Discount

Date of purchase

Ans:

CREATE TABLE `Sales` (

`Customer_Name` VARCHAR(255),

`Phone` INT(25) NOT NULL AUTO_INCREMENT,

`Price` DOUBLE(255),

`Discount` DOUBLE(255),

` Purchase_date ` DATE(20),

`SKU` VARCHAR(255),

PRIMARY KEY (`Phone`)

);

SAMPLE DATA:

Customer_Name Phone Price Discount Purchase_date SKU


Aman 1234567890 4000 120 01-01-2021 A,B,C
Sonia 5678901234 1000 0 01-01-2021 A
Aman 1234567890 2500 0 05-01-2021 A,B
Preeti 9012345678 1000 250 06-01-2022 A
Rakesh 2345678901 2000 300 06-01-2021 B,C
Saurabh 3456789012 1000 120 10-01-2021 A
Rohan 4567890123 1000 0 15-01-2021 A
Ram 6789012345 1500 50 15-01-2021 B

1. Name and list of customers who purchased more than once


Select Customer_Name, Phone, sum(Price) as Total_Purchase_Amount

From Sales

Group by Customer_Name, Phone

Having Count(distinct Phone) >= 1

2. Highest selling item name


Select

distinct category, count(distinct category) as Item_Count

From

select

transform(cast(json_parse(SKU) as array<varchar>) , x-> x) as category

from Sales

cross join unnest(category) as tbl(req_category)

group by category

order by Item_Count desc


Limit 1

3. Number of times a customer purchases on an average in a


month
Select Customer_Name, count(distinct Phone)/30 as Customer_Average

from Sales

group by Customer_Name
4. List of all customers who have purchased items worth more
than Rs. X (here X should be a
variable)
Let us take the value of X = 1000

Select Customer_Name, Phone, sum(Price) as Total_Purchase_Amount

From Sales

Group by Customer_Name, Phone

Having sum(Price) > 1000

You might also like