SQL PROJECT-1

-- 1.Create New Schema as Ecommerce

create database ecommerce

-- 2. Import .csv file users_data into MYSQL

use ecommerce;

select * from user_data;

-- by import option,all the data from users_data file is imported in ecommerce database

-- 3.Run SQl command to see the structure of table

use ecommerce;
desc users_data;

-- 4.Run sql command to select first 100 rows of table

use ecommerce;
select * from users_data limit 100;

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

use ecommerce;
select count(distinct country) distinct_country , count(distinct language) distinct_language from users_data;

-- 6. Check whether male users are having more followers or female users

use ecommerce;
select gender,sum(socialNbFollowers) followers from data group by gender;
-- Of course female users have more followers

-- 7. a) total users who uses profile picture in their profile

use ecommerce;
select count(*) total_users_with_prfile_picture from user_data where hasProfilePicture='True';

-- 7. b) Total users who uses application for ecommerce platform

use ecommerce;
select count(*) total_users from user_data where hasAnyApp='True';

-- 7. c) Total users who uses android application

use ecommerce;
select count(*) total_users from user_data where hasAndroidApp='True';

-- 7. d) Total users who uses IOS app

use ecommerce;
select count(*) total_users from user_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

use ecommerce;
select country ,sum(productsBought) Total_buyers from user_data group by country order by sum(productsBought) desc;

-- 9. To calculate the average number of sellers for each country and sort the result in ascending order of total number of sellers

use ecommerce;
select country ,avg(productsSold) Total_Sellers from user_data group by country order by avg(productsSold) asc;

-- 10. Displaying name of top 10 countries having maximum products pass rate

use ecommerce;
select country,sum(productsPassRate) Products_Pass_Rate from user_data group by country order by sum(productsPassRate) desc limit 10;

-- 11. To calculate the number of users on an ecommerce platform for different language choices.

use ecommerce;
select language,count(*) Users from user_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.

use ecommerce;
select gender,sum(productsWished) ProductsWished , sum(socialProductsLiked) ProductsLiked from user_data where gender='F';
-- Female users chose more to like socially on ecommerce platform than putting product in wishlist.

-- 13. choice of male users about being seller or buyer

use ecommerce;
select gender,sum(productsSold) Products_Sold , sum(productsBought) Products_Bought from user_data where gender='M';
-- From above data it is clear that the Male users are chose to be the buyers more

-- 14. country with maximum number of buyers

use ecommerce;
select country , sum(productsBought) ProductsBought from user_data group by country order by sum(productsBought) desc limit 1;

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

use ecommerce;
select country, (productsSold) from users_data where productsSold=0 group by country limit 10;

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

use ecommerce;
select identifierHash,gender, daysSinceLastLogin from users_data order by daysSinceLastLogin asc limit 110;

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

use ecommerce;
select gender,count(daysSinceLastLogin) No_of_femaleUsers from users_data where gender='F' and daysSinceLastLogin > 100;

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

use ecommerce;
select gender,country,count(*) No_of_FemaleUsers from users_data where gender='F' group by country order by country asc;

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

use ecommerce;
select gender,country,count(*) No_of_maleUsers from users_data where gender='M' group by country order by country asc;

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

use ecommerce;
select country,gender,avg(productsSold) , avg(productsBought) from users_data where gender='M' group by country order by country asc;

PROJECT-1

IMPORT.CSV FILE USERS_DATA INTO MYSQL