Project 1

1.Create new schema as ecommerce

create schema ecommerce

use ecommerce

2.Add steps: 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

Done.

3.Run SQL command to see the structure of table

Done

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

select * from users_data limit 100;

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

select count(distinct country) as Country,count(distinct language) as Language from users_data;

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

select count(socialNbFollowers),gender from users_data group by gender;

Female users are having more followers.

7.Calculate the total users those

Uses Profile Picture in their Profile
Uses Application for Ecommerce platform
Uses Android app
Uses ios app

1.select count(hasProfilePicture) from users_data where hasProfilePicture ='True';

2.select count(hasAnyApp) from users_data where hasAnyApp='True';

3.select count(hasAndroidApp) from users_data where hasAndroidApp='True';

4.select 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. (Hint: consider only those users having at least 1 product bought.)

select sum(productsBought) TotalProduct, country from users_data where productsBought>0 group by country order by sum(productsBought) 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.)

select sum(productsSold) TotalProduct, country from users_data where productsSold>0 group by country order by sum(productsSold) asc ;

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

select sum(productsPassRate) PassRate,country 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.

select count(*) No_of_User,language from users_data group by 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.)

select sum(socialProductsLiked) LikedProducts ,gender from users_data where gender='F'
union select sum(productsWished) wishedProducts,gender from users_data where gender='F';

Like product has more choice for female.

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

select sum(ProductsSold) Products from users_data where gender='M'
union select sum(productsBought) Products from users_data where gender='M';

Buy choice is more for Male.

14.Which country is having maximum number of buyers?

select sum(productsBought) ,country 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.

select productsSold,country from users_data where productsSold =0 group by country limit 10;

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

select count(*) No_of_User,country ,daysSinceLastLogin from users_data
where daysSinceLastLogin <400 group by country 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(*) No_of_User,country ,gender,daysSinceLastLogin from users_data
where gender='F' and daysSinceLastLogin >100 group by country order by daysSinceLastLogin asc;

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

select count(*) No_of_User,country ,gender from users_data where gender='F' group by country;

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

select count(*) No_of_User,country ,gender from users_data where gender='M' group by country;

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

select avg(productsSold) Sold ,avg(productsBought) Bought ,country from users_data
where gender='M' group by country;