Project 1
/*
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)*/
/*
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 the database
*/
USE ecommerce;
select * from users_Data limit 100;
/*
5.How many distinct values exist in table for field country and language
*/
USE ecommerce;
select count(distinct(country)),count(distinct(language)) from users_data;
/*
6.Check whether male users are having maximum followers or female users.
*/
USE ecommerce;
SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;
/*
7.Calculate the total users those
a.Uses Profile Picture in their Profile
*/
USE ecommerce;
select count(hasProfilePicture) Total_Profile_Picture from users_Data where hasProfilePicture='True';
/*
b.Uses Application for Ecommerce platform
*/
USE ecommerce;
select count(hasAnyApp) Total_Application FROM users_data WHERE hasAnyApp ="True";
/*c.Uses Android app*/
USE ecommerce;
SELECT COUNT(hasAndroidApp) total_use_of_android_app FROM users_data WHERE hasAndroidApp ="True";
/*d.Uses ios app*/
USE ecommerce;
SELECT COUNT(hasIosApp) total_Use_of_IOS_app 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.)
*/
USE ecommerce;
SELECT country,SUM(productsBought) FROM users_data 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.)
*/
USE ecommerce;
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.
*/
USE ecommerce;
SELECT country,SUM(productsPassRate) 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.
*/
USE ecommerce;
SELECT language, COUNT(*) number_of_users 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.)
*/
USE ecommerce;
SELECT SUM(productsWished) Sum_of_Products_wished,SUM(socialProductsLiked) Sum_of_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.)
*/
USE ecommerce;
SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender='M';
/*
14.Which country is having maximum number of buyers?
*/
USE ecommerce;
select country ,SUM(productsBought) Sum_of_product_bought FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;
/*15. List the name of 10 countries having zero number of sellers. */
USE ecommerce;
SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;
/*16.Display record of top 110 users who have used ecommerce platform recently.*/
USE ecommerce;
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.*/
USE ecommerce;
SELECT gender,COUNT(daysSinceLastLogin) number_of_femal_user_not_logged 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 country,count(*) number_of_users from users_data where gender='F' group by country ;
/*19.Display the number of male users of each country at ecommerce platform. */
USE ecommerce;
select country,count(*) number_of_users 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. */
USE ecommerce;
SELECT AVG(productsSold) Average_product_sold,AVG(productsBought) Average_product_bought,country FROM users_data WHERE gender='M' GROUP BY country;