Project 1

PROJECT - 1 ANSWERS

-- Q.1) Create new schema as ecommerce

A) create database 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)
*/

A)

-- screen short attached it
use ecommerce;
select * from users_data;
select count(*) from users_data;

/*
-- Q.3) Run SQL command to see the structure of table
*/

A)

select * from users_data;

desc users_data;
/*
-- Q.4) Run SQL command to select first 100 rows of the database
*/

A)
select * from users_data;

select * from users_data limit 100;

/*
-- Q.5) How many distinct values exist in table for field country and language
*/

A)
select * from users_data;

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

/*
-- Q.6)Check whether male users are having maximum followers or female users.
*/

A)
select * from users_data;

select sum(socialNbFollowers), gender 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
*/

A)
select * from users_data;

a) Uses Profile Picture in their Profile.
select count(hasprofilepicture) as profilepicture from users_data where hasprofilepicture = 'True';

b) Uses Application for Ecommerce platform.
select count(hasanyapp) as anyapp from users_data where hasanyapp = 'True';

c) Uses Android app.
select count(hasandroidapp) as androidapp from users_data where hasandroidapp = 'True';

d) Uses ios app.
select count(hasiosapp) as iosapp 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.)
*/

A)
select * from users_data;

SELECT
count(productsbought) AS buyers, country
FROM
users_data
WHERE
productsbought >= '1'
GROUP BY productsbought , country
ORDER BY productsbought , country 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.)
*/

A)
select * from users_data;

SELECT
count(productssold) AS sellers, country
FROM
users_data
WHERE
productssold >= '1'
GROUP BY productssold , country
ORDER BY productssold , country ASC;

/*
-- Q10) Display name of top 10 countries having maximum products pass rate.
*/

A)
select * from users_data;

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.
*/

A)
select * from users_data;

select language, count(*) numberofusers 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.)
*/

A)
select * from users_data;

SELECT
SUM(productswished) AS ProductsInWishlist,
SUM(socialproductsliked) AS productslikesinsocially
FROM
users_data
WHERE
gender = 'f'
UNION SELECT
SUM(productswished) AS ProductsInWishlist,
SUM(socialProductsLiked) AS productslikesinSocially
FROM
users_data
WHERE
gender = 'f';

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

A)
select * from users_data;

SELECT
SUM(productssold) AS seller, SUM(productsbought) AS buyer
FROM
users_data
WHERE
gender = 'M'

UNION SELECT

SUM(productssold) AS seller, SUM(productsbought) AS buyer
FROM
users_data
WHERE
gender = 'M';

/*
-- Q14) Which country is having maximum number of buyers?
*/

A)
select * from users_data;

SELECT
country, max(productsbought) AS buyer
FROM
users_data
GROUP BY country
ORDER BY max(productsbought) DESC
LIMIT 10;

/*
-- Q15) List the name of 10 countries having zero number of sellers.
*/

A)

select * from users_data;

SELECT
country AS countries, productssold AS sellers
FROM
users_data
WHERE
productssold = 0
LIMIT 10;

/*
-- Q16) Display record of top 110 users who have used ecommerce platform recently.
*/

A)
select * from users_data;

select * from users_data order by dayssincelastlogin asc limit 110 ;

or

SELECT
daysSinceLastLogin Top_Record
FROM
users_data
ORDER BY daysSinceLastLogin ASC
LIMIT 110;

/*
-- Q17) Calculate the number of female users those who have not logged in since last 100 days.
*/

A)
select * from users_data;

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

/*
-- Q18)Display the number of female users of each country at ecommerce platform.
*/

A)
select * from users_data;

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

/*
-- Q19) Display the number of male users of each country at ecommerce platform.
*/

A)
select * from users_data;

SELECT
country, COUNT(*)
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.
*/

A)
select * from users_data;

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