Project 1

1.

create a database ecommerse;

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.

desc user_data;

4.

select * from users_data limit 100;

5.select count(distinct country) as country_count, count(distinct language) as language_count from users_data;

6.

select gender,sum(socialNBFollowers) from users_data group by gender;

7a.

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

7b.

select count(hasAnyApp) as count_Users_with_app from users_data where hasAnyApp = 'True';

7c.

select count(hasAndroidApp) as count_Users_with_Androidapp from users_data where hasAndroidApp = 'True';

7d.

select count(hasIosApp) as count_Users_with_Iosdapp from users_data where hasIosApp = 'True';

8.

use ecommerse;

select country, count(product_bought) as bought group by country order by bought desc

9.

use ecommerse;

select country, count(product_sold) as sold group by country order by sold asc

10.

use ecommerse;

select country, count(products_pass_rate) from users_data group by country having max(products_pass_rate)

limit 10;

11.

use ecommerse;

select language, count(language) from users_data group by language;

12.

use ecommerse;

select sum(socialProductsLiked), sum(productsWished) from users_data where gender ='f';

13.

use ecommerse;

select sum(productssold), sum(productsbought) from users_data where gender ='M';

14.

select country,max(productsBought) Max_no_of_buyers from users_data group by country order by max(productsBought) desc limit 1;

15.

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

16.

select * from users_data group by daysSinceLastLogin having min(daysSinceLastLogin) order by daysSinceLastLogin asc limit 110;

17.

select count(type) from users_data where gender='F' and daysSinceLastLogin >= 100;

18.

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

19.

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

20.

elect country,avg(productsSold) Products_sold,avg(productsBought) Products_bought from users_data where gender='M' group by country;