Project 1

Ans 1: Created new schema as ecommerce

Ans 2: Succesfully Importted .csv file users_data into MySQL

Ans 3:
use ecommerce;
DESC users_data;

Ans 4:
use ecommerce;
SELECT * FROM users_data LIMIT 100;

Ans 5:
use ecommerce;
SELECT
COUNT(DISTINCT country),
COUNT(DISTINCT language)
FROM
users_data;

Ans 6:

use ecommerce;
SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;

Ans 7:
USE ecommerce;
SELECT * FROM users_data;
SELECT 'Total_users_having_ProfilePicture', COUNT(hasProfilePicture) Total_Users FROM users_data
WHERE
hasProfilePicture = 'True'
UNION
SELECT 'Total_users_using_Application', COUNT(hasAnyApp) FROM users_data
WHERE
hasAnyApp = 'True'
UNION
SELECT 'Total_users_using_AndroidApplication', COUNT(hasAndroidApp) FROM users_data
WHERE
hasAndroidApp = 'True'
UNION
SELECT 'Total_users_using_IosApplication', COUNT(hasIosApp) FROM users_data
WHERE
hasIosApp = 'True';

Ans 8:

SELECT * FROM users_data;
SELECT country,SUM(productsBought) total_number_of_buyer
FROM users_data
GROUP BY country
ORDER BY total_number_of_buyer DESC ;

Ans 9:

SELECT * FROM users_data;
SELECT country,SUM(productsSold) total_number_of_sellers
FROM users_data
GROUP BY country
ORDER BY total_number_of_sellers ;

Ans 10:

SELECT * FROM users_data;
SELECT country, MAX(productsPassRate) Maximum_Product_Pass_Rate from users_data
GROUP BY country
ORDER BY Maximum_Product_Pass_Rate DESC LIMIT 10;

Ans 11:

SELECT * FROM users_data;
SELECT language, COUNT(language) FROM users_data GROUP BY language;

Ans 12:

SELECT * FROM users_data;
SELECT 'Sum_of_ProductsWished', SUM(productsWished) Total_number
FROM users_data
WHERE gender='F'
AND hasAnyApp='True'
UNION
SELECT 'Sum_of_ProductsLiked', SUM(socialProductsLiked) Total_number
FROM users_data
WHERE gender='F'
AND hasAnyApp='True';

Ans 13:

SELECT * FROM users_data;
SELECT 'Sum_of_ProductsSold', SUM(productsSold) Total_number
FROM users_data
WHERE gender='M'
UNION
SELECT 'Sum_of_ProductsBought', SUM(productsBought) Total_number
FROM users_data
WHERE gender='F';

Ans 14:

SELECT * FROM users_data;
SELECT country, MAX(productsBought) Maximum_Product_Bought from users_data
GROUP BY country
ORDER BY Maximum_Product_Bought DESC LIMIT 1;

Ans 15:

SELECT * FROM users_data;
SELECT country FROM users_data WHERE productsSold=0 LIMIT 10;

Ans 16:
SELECT * FROM users_data;
SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

ANs 17:

SELECT * FROM users_data;
SELECT COUNT(*) FROM users_data WHERE gender='F' AND DaysSinceLastLogin>100;

Ans 18:
SELECT * FROM users_data;
SELECT country, COUNT(gender) Female FROM users_data WHERE gender='F' GROUP BY country;

Ans 19:

SELECT * FROM users_data;
SELECT country, COUNT(gender) Male FROM users_data WHERE gender='M' GROUP BY country;

Ans 20:

SELECT * FROM users_data;
SELECT country,
AVG(productsSold) Average_of_ProductsSold,
AVG(productsBought) Average_of_ProductsBought
FROM
users_data
WHERE
gender='M'
GROUP BY country;