Project 1

## Q1:- Create new schema as ecommerce
create database ecommerce;
USE ECOMMERCE;


## Q2:- 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)

CREATE DATABASE IF NOT EXISTS ecommerce;
USE ecommerce;
CREATE TABLE users_data (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
age INT);
LOAD DATA INFILE '/path/to/your/file/users_data.csv'
INTO TABLE users_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- If the first row is a header and you want to ignore it


## Q3:- Run SQL command to see the structure of table
DESCRIBE USERS_DATA;
SELECT * FROM USERS_DATA;


## Q4:- Run SQL command to select first 100 rows of the database

SELECT * FROM USERS_DATA LIMIT 100;


## Q5:- How many distinct values exist in table for field country and language

SELECT COUNT(DISTINCT COUNTRY), COUNT(DISTINCT LANGUAGE) FROM USERS_DATA;

## Q6:- Check whether male users are having maximum followers or female users.

SELECT GENDER, COUNT(SOCIALNBFOLLOWERS) FROM USERS_DATA GROUP BY GENDER;


## Q7:- 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(HASPROFILEPICTURE) FROM USERS_DATA WHERE HASPROFILEPICTURE = 'TRUE';
SELECT COUNT(HASANYAPP) FROM USERS_DATA WHERE HASANYAPP = 'TRUE';
SELECT COUNT(HASANDROIDAPP) FROM USERS_DATA WHERE HASANDROIDAPP = 'TRUE';
SELECT COUNT(HASIOSAPP) FROM USERS_DATA WHERE HASIOSAPP = 'TRUE';


## Q8:- 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.)

SELECT
COUNTRY, COUNT(PRODUCTSBOUGHT)
FROM
USERS_DATA
WHERE
PRODUCTSBOUGHT != 0
GROUP BY COUNTRY
ORDER BY COUNT(PRODUCTSBOUGHT) DESC;


## Q9:- 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.)

SELECT
COUNTRY, COUNT(PRODUCTSSOLD)
FROM
USERS_DATA
WHERE
PRODUCTSSOLD != 0
GROUP BY COUNTRY
ORDER BY COUNT(PRODUCTSSOLD);


## Q10:- Display name of top 10 countries having maximum products pass rate.

SELECT
COUNTRY, MAX(PRODUCTSPASSRATE)
FROM
USERS_DATA
GROUP BY COUNTRY
ORDER BY MAX(PRODUCTSPASSRATE) DESC
LIMIT 10;


## Q11:- Calculate the number of users on an ecommerce platform for different language choices.

SELECT
COUNT(LANGUAGE), LANGUAGE
FROM
USERS_DATA
GROUP BY LANGUAGE;


## Q12:- 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
COUNT(PRODUCTSWISHED)
FROM
USERS_DATA
WHERE GENDER = 'F' AND PRODUCTSWISHED>0
UNION SELECT COUNT(SOCIALPRODUCTSLIKED)
FROM
USERS_DATA
WHERE GENDER = 'F' AND SOCIALPRODUCTSLIKED>0;


## Q13:- Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

SELECT * FROM USERS_DATA;
SELECT
COUNT(PRODUCTSSOLD)
FROM
USERS_DATA
WHERE GENDER = 'M' AND PRODUCTSSOLD>0
UNION SELECT COUNT(PRODUCTSBOUGHT)
FROM
USERS_DATA
WHERE GENDER = 'M' AND PRODUCTSBOUGHT>0;


## Q14:- Which country is having maximum number of buyers?

SELECT COUNTRY,MAX(PRODUCTSBOUGHT) FROM USERS_DATA
GROUP BY COUNTRY ORDER BY MAX(PRODUCTSBOUGHT) DESC;


## Q15:- List the name of 10 countries having zero number of sellers.

SELECT COUNTRY FROM USERS_DATA WHERE PRODUCTSSOLD=0 LIMIT 10;


## Q16:- Display record of top 110 users who have used ecommerce platform recently.

SELECT * FROM USERS_DATA ORDER BY DAYSSINCELASTLOGIN LIMIT 110;


## Q17:- Calculate the number of female users those who have not logged in since last 100 days.

SELECT COUNT(GENDER) FROM USERS_DATA WHERE GENDER = 'F' AND DAYSSINCELASTLOGIN>100;


## Q18:- Display the number of female users of each country at ecommerce platform.

SELECT COUNTRY,COUNT(GENDER) FEMALE FROM USERS_DATA WHERE GENDER = 'F' GROUP BY COUNTRY;


## Q19:- Display the number of male users of each country at ecommerce platform.

SELECT COUNTRY,COUNT(GENDER) MALE FROM USERS_DATA WHERE GENDER = 'M' GROUP BY COUNTRY;


## Q20:- 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;