Project

1.Create new schema as ecommerce

Ans : create database ecommerce;

2.Import .csv file users_data into MySQL
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options : Create a new table , select delete if exist -> next -> next)

Ans : use ecommerce;

3.Run SQL command to see the structure of table

Ans :desc users_data;

4.Run SQL command to select first 100 rows of the database

Ans :select*from users_data limit 100;

5.How many distinct values exist in table for field country and language

Ans :select count(distinct country) as country from users_data
union
select count(distinct language) as language from users_data;

6.Check whether male users are having maximum followers or female users.

Ans :select sum(socialNbFollowers),gender from users_data group by gender order by gender desc;

7.Calculate the total users those

1. Uses Profile Picture in their Profile
2. Uses Application for Ecommerce platform
3. Uses Android app
4. Uses ios app

Ans : select 'prrofile picture' Name ,count(hasProfilePicture) as count , hasProfilePicture as status from users_data where hasProfilePicture = 'true'
union
select 'Uses Aplication' Name ,count(hasAnyApp),hasAnyApp from users_data where hasAnyApp = 'true'
union
select 'Android app' Name ,count(hasAndroidApp),hasAndroidApp from users_data where hasAndroidApp = 'true'
union
select 'ios App',count(hasiosApp),hasiosApp from users_data where hasiosApp = 'true';

8.Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers. (Hint: consider only those users having at least 1 product bought.)

Ans : select country,count(productsBought) from users_data group by country order by country desc;

9.Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers. (Hint: consider only those users having at least 1 product sold.)

Ans : select country,count(productsBought) from users_data group by country order by country asc;

10.Display name of top 10 countries having maximum products pass rate.

Ans : select country,sum(productsPassRate) as product_pass from users_data group by country order by sum(productsPassRate) desc limit 10 ;

11.Calculate the number of users on an ecommerce platform for different language choices.

Ans : SELECT distinct language,count(language) as Users FROM USERS_DATA group by language order by count(language);

12.Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform. (Hint: use UNION to answer this question.)

Ans : select 'Wished Product' as status ,gender,sum(productsWished) as products from users_data where gender = 'F'
union
select 'Liked Product'as status , gender,sum(socialProductsLiked) as products from users_data where gender = 'F';

13.Check the choice of male users about being a seller or buyer. (Hint: use UNION to solve this question.)

Ans :select 'product Sold' as status ,gender,sum(productsSold) as products from users_data where gender = 'M'
union
select 'Product Bought'as status , gender,sum(productsBought) as products from users_data where gender = 'M';

14.Which country has the maximum number of buyers?

Ans : select country,sum(productsBought) as productBought from users_data group by country order by sum(productsBought) desc limit 1 ;

15.List the name of 10 countries having zero number of sellers.

Ans : select country , sum(productsSold) as ListNumberOfSale from users_data group by country order by sum(productsSold) asc limit 10;

16.Display record of top 110 users who have used ecommerce platform recently.

Ans : select * from users_data where daysSinceLastLogin order by daysSinceLastLogin limit 110;

17.Calculate the number of female users who have not logged in since the last 100 days.

Ans : select count(gender) as Users from users_data where gender = 'F' and daysSinceLastLogin <= 100 ;

18.Display the number of female users of each country on an ecommerce platform.

Ans : select country , count(gender) as country from users_data where gender = 'F' group by country order by count(gender) desc;

19.Display the number of male users of each country on the ecommerce platform.

Ans : select country , count(gender) as gender from users_data where gender = 'M' group by country order by count(gender) desc;

20.Calculate the average number of products sold and bought on ecommerce platforms by male users for each country.

Ans : select country,gender , avg(productsSold) as Product_sold,avg(productsBought) as Product_Bought from users_data where gender = 'M' group by country;