Project 1
1.Create new schema as ecommerce:
2.Import .csv file users_data into MySQL:-
3.Run SQL command to see the structure of table:
=> desc users_data;
4.Run SQL command to select first 100 rows of the database:
=>select * from users_data limit 100;
5.To find distinct values exist in table for field country and language:
=>select count(distinct(country)) country,count(distinct(language)) language from users_data;
6.To Check whether male users are having maximum followers or female users:
=>select sum(socialNbFollowers) as total_followers , gender from users_data group by gender;
7. To calculate the total users those:
- Uses Profile Picture in their Profile
=>select count(hasProfilePicture) as total_profile_picture from users_data where hasProfilePicture= 'True';
2.Uses Application for Ecommerce platform:
=> select count(hasAnyApp) as total_Anyapplication from users_data where hasAnyApp = 'True';
3.Uses Android app:
=>select count(hasAndroidApp) as total_android_users from users_data where hasAndroidApp ='true';
4.Uses ios app:
=>select count(hasIosApp) as total_IosUsers from users_data where hasIosApp ='true';
8.To calculate the total number of buyers for each country and sort the result in descending order of total number of buyers:
=> select country,sum(productsBought) as total_buyers from users_data group by country order by sum(productsBought) desc;
9.To 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 country,avg(productsSold) from users_data group by country order by avg(productsSold) ASC;
10.Display name of top 10 countries having maximum products pass rate.
=>select country,sum(productsPassRate) Products_PassRate 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 language, count(*) as no_of_users from users_data group by language;
12.To 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 gender,sum(productsWished) as Total_product_wished , sum(socialProductsLiked) as Total_Social_Products_liked from users_data where gender = 'F';
13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
=> select gender, SUM(productsSold) ,SUM(productsBought) FROM users_data WHERE gender='M';
14.Which country is having maximum number of buyers?
=> select country ,SUM(productsBought) Sum_of_product_bought 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 country , productsSold from users_data where productsSold=0 limit 10;
16.Display record of top 110 users who have used ecommerce platform recently.
=> SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;
17.Calculate the number of female users those who have not logged in since last 100 days.
=>select gender, COUNT(daysSinceLastLogin) 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 country,count(*) no_of_female_users from users_data where gender ='F' group by country;
19.Display the number of male users of each country at ecommerce platform.
=>select country,count(*) no_of_male_users from users_data where gender ='M' group by country;
20.To Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
=>select country,avg(productsSold) as average_products_sold , avg(productsBought) as average_products_bought
from users_data where gender ='M' group by country ;
