Project 1

TASK NO 1 : Create new schema as ecommerce

Open SQL workbench -> Right Click on the navigator -> Create a new schema called "ecommerce".

TASK NO 2 : Import .csv file users_data into MySQL

Right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose

TASK NO 3 : Run SQL command to see the structure of table

DESC users_data;

TASK NO 4 : Run SQL command to select first 100 rows of the database.

SELECT * FROM users_data
LIMIT 100;

TASK N0 5 : How many distinct values exist in table for field country and language.

SELECT COUNT(DISTINCT country, language)
FROM users_data;

TASK NO 6 : Check whether male users are having maximum followers or female users.

SELECT MAX(gender)
FROM users_data;

TASK NO 7 : Calculate the total users those

a. Uses Profile Picture in their Profile
b. Uses Application for Ecommerce platform
c. Uses Android app
d. Uses ios app

SELECT COUNT(DISTINCT hasProfilePicture, hasAnyApp, hasAndroidApp, hasIosApp)
FROM users_data;

TASK NO 8 : Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers.

SELECT country, COUNT(productsBought) buyers
FROM users_data
WHERE productsBought >= 1
GROUP BY country
ORDER BY COUNT(productsBought) DESC;

TASK NO 9 : Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.

SELECT country, COUNT(productsSold) sellers
FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER BY COUNT(productsSold) ASC;

TASK NO 10 : Display name of top 10 countries having maximum products pass rate.

SELECT country, MAX(productsPassRate)
FROM users_data
GROUP BY country LIMIT 10 ;

TASK NO 11 : Calculate the number of users on an ecommerce platform for different language choices.

SELECT COUNT(DISTINCT type, language) UsersWithLanguageChoice
FROM users_data;

TASK NO 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 gender, COUNT(DISTINCT productsWished) Choice
FROM users_data
WHERE gender='F' OR 'productsWished' UNION
SELECT gender, COUNT(DISTINCT socialProductsLiked) sociallike
FROM users_data where gender='F' OR 'socialProductsLiked';

-- The choice for female users is to put their product in 'socialProductsLiked' in ecommerce platform.

TASK N0 13 : Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

SELECT gender, COUNT(DISTINCT productsSold) Choice
FROM users_data
WHERE gender='M' OR 'productsSold' UNION
SELECT gender, COUNT(DISTINCT productsBought) buyer
FROM users_data where gender='M' OR 'productsBought';

-- The choice for male users is to be a 'seller'.

TASK N0 14 : Which country is having maximum number of buyers?

SELECT country, MAX(productsBought)
FROM users_data;

TASK N0 15 : List the name of 10 countries having zero number of sellers.

SELECT country, productsSold
FROM users_data
WHERE productsSold = 0
GROUP BY country LIMIT 10;

TASK N0 16 : Display record of top 110 users who have used ecommerce platform recently.

SELECT type, daysSinceLastLogin From users_data
GROUP BY daysSinceLastLogin
HAVING min(daysSinceLastLogin)
ORDER BY daysSinceLastLogin ASC
LIMIT 110;

TASK NO 17 : Calculate the number of female users those who have not logged in since last 100 days.

SELECT COUNT(daysSinceLastLogin) NoOfFemaleUsers
FROM users_data
WHERE gender = 'F' AND daysSinceLastLogin > 100;

TASK NO 18 : Display the number of female users of each country at ecommerce platform.

SELECT country, COUNT(gender) NoOfFemaleUsers
FROM users_data
where gender='F'
GROUP BY country;

TASK NO 19 : Display the number of male users of each country at ecommerce platform.

SELECT country, COUNT(gender) NoOfMaleUsers
FROM users_data
where gender='M'
GROUP BY country;

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

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