Project 1

                                                       Proctect - 1 SQL

1.Create new schema as ecommerce?
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)

use ecommerce;
select count(*) users_data;

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.How many distinct values exist in table for field country and language?
select count(distinct country) contry, count(distinct language) language from users_data;

6.Check 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?
a.Uses Profile Picture in their Profile
select count(hasProfilePicture) Users_with_ProfilePicture from users_data where hasProfilePicture = 'True';

b.Uses Application for Ecommerce platform
select count(hasAnyApp) Users_with_AnyApp from users_data where hasAnyApp = 'True';

c.Uses Android app
select count(hasAndroidApp) Users_with_AndroidApp from users_data where hasAndroidApp = 'True';

d.Uses ios app
select count(hasIosApp) Users_with_IosApp 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 country, count(productsBought) Buyers from users_data group by country order by Buyers 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 country, count(productsSold) sellers from users_data group by country order by sellers asc;

10.Display name of top 10 countries having maximum products pass rate?
select country from users_data group by country Having max(productsPassRate) limit 10;

11.Calculate the number of users on an ecommerce platform for different language choices?
select language, count(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(productsWished), sum(socialProductsLiked) 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 count(productsSold), count(productsBought) from users_data where gender = 'M';

14.Which country is having maximum number of buyers?
select country, max(productsBought) Max_no_of_Buyers from users_data group by country order by max(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 daysSinceLastLogin from users_data order by daysSincelastLogin asc limit 110; 
                                             OR
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 count(type) from users_data where gender = 'F' and daysSinceLastLogin >= 100;
                                             OR
select gender , COUNT(daysSinceLastLogin) 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(gender) Female_users from users_data where gender = 'F' group by country;
                                              OR
select country , count(*) 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(gender) Male_users from users_data where gender = 'M' group by country;
                                              OR
select country, count(*) 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 country, avg(productsSold) Products_Sold , avg(productsBought) Products_Bought from users_data where gender = 'M' group by country;
                                               OR
select avg(productsSold),avg(productsBought),country from users_data where gender='M' group by country;