Project 1

1. Create Schema through a navigator or in code by writing (create 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)

3.SQL command to see the structure of table:

use ecommerce;
desc users_data;

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

select * from users_data limit 100;

5.distinct values exist in table for field country and language
select count(distinct country),count(distinct language) from users_data;

6.whether male users are having maximum followers or female users.select gender,sum(socialNbFollowers) from users_data group by gender;

7.Calculate the total users those ,Uses Profile Picture in their Profile,Uses Application for Ecommerce platform
select 'total_user_having_profile_picture' , count(hasProfilePicture) from users_data where
hasProfilePicture ='True' union
select 'total_user_having_any_app', count(hasAnyApp) from users_data where
hasAnyApp='True' union
select 'total_user_having_android_app', count(hasAndroidApp) from users_data where
hasAndroidApp='True' union
select 'total_user_having_Ios_app', count(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.
select country,count(productsBought) from users_data
where productsBought>0 group by country order by count(productsBought) desc;

9.Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.
select country,count(productsSold) from users_data
where productsSold>0 group by country order by count(productsSold) asc;

10.Display name of top 10 countries having maximum products pass rate
select country,productsPassRate from users_data order by productsPassRate desc limit 10;

11.Calculate the number of users on an ecommerce platform for different language choices.
select language,count(type) from users_data group by language ;

12.select 'product in a wishlist', count(productsWished) choices
from users_Data where gender='F' and productsWished>0
union
select 'product liked socially', count(socialProductsLiked) choices2
from users_Data where gender='F' and socialProductsLiked>0;

13. Check the choice of male users about being seller or buyer
select 'Selling', count(productsSold) choices
from users_Data where gender='M' and productsSold>0
union
select 'Buying', count(productsBought) choices2
from users_Data where gender='M' and productsBought>0;

14.country is having maximum number of buyers?
select country, count(productsBought) from users_data group by country order by count(productsBought) desc limit 1;

15.ame of 10 countries having zero number of sellers
select country, sum(productsSold) 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.
select * from users_data order by daysSinceLastLogin asc limit 110;

17.Calculate the number of female users those who have not logged in since last 100 days
select count(gender) total_no_of_female_users from users_data where gender='F' and daysSinceLastLogin=100;

18.Display the number of female users of each country at ecommerce platform
select count(gender) female,country from users_data where gender='F' and hasAnyApp=''True group by country;

19.Display the number of male users of each country at ecommerce platform.
select count(gender) male,country from users_data where gender='M' and hasAnyApp='True' group by country;

20. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country,avg(productsSold) Sold,avg(productsBought) Bought
from users_data
where gender='M' and hasAnyApp='True' group by country;