SQL Project 1 on e-commerce database

Project 1: Business model Customer to Customer (C2C) allows customers to do business with each other. This model is growing fast with e-commerce platforms where sellers may be required to pay some amount and buyers can buy it without paying anything. An E-Commerce website brings the seller and buyer to the same platform. …

1. Create a new scheme for e-commerce

create database e-commerce;
use e-commerce;

2.Import .csv file users_data into MySQL
(right click on e-commerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options : Create a new table, select delete if exist -> next -> next)

use e-commerce;

select count(*) from users_data;

3. Run SQL command to see the structure of the table

desc users_data;

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

select * from users_data limit 100;

5. How many distinct values exist inthe table for field country and language

select count(distinct country) country, count(distinct language) language from users_data;

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

select gender, sum(social followers) from users_data group by gender;

7. Calculate the total number of users those
a.Uses Profile Picture in their Profile
b.Uses Application for E-commerce platform
c.Uses Android app
d.Uses ios app

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

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

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

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

8. Calculate the total number of buyers for each country and sort the result in descending order oof ta otal number of buyers. (Hint: consider only those users having at least 1 product bought.)

select country,count(products bought) 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 the total number of sellers. (Hint: consider only those users having at least 1 product sold.)

select country,count(products sold) Sellers from users_data group by country order by Sellers asc;

10.Display the name of top 10 countries have ing 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 e-commerce 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 on 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 sellers or buyers. (Hint: use UNION to solve this question.)

select count(productsSold)count productss boughtt) from users_data where gender='M';

14.Which country having g maximum number of buyers?

select country,products boughtught) 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 county, products sold from users_data where productsSold=0 limit 10;

16.Displa recordord of the top 110 users who have used the e-commerce platform recently.

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

17.Calculate the number of femalethosee who have not logged in in lasthe t 100 days.

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

18. Di splay the number of female users of each country at the e-commerce platform.

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

19. Display the number of male users of each country on the e-commerce platform.

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

20. Calculate the average number of products sold and boughe-commercemerce 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;