Project 1

1)

below the navigator click on schema after clicking schema a window will appear which showing the schemas present in it,right click on that window and click on create schema a window appear asking to give a name after typing the name click on apply after that another will appear click on apply your new scheme is created according to the name given to it.

2)

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.

3)

USE ecommerce;
SELECT * FROM users_data ;

SELECT COUNT(*) FROM users_data;

4)

SELECT * FROM users_data limit 100;

5)

SELECT COUNT( DISTINCT Country),COUNT(DISTINCT Language) FROM users_data;

6)

SELECT Gender,MAX(socialNbfollowers) FROM users_data group by Gender;

7)

SELECT'Profilepicture',count(hasProfilePicture) FROM users_data WHERE hasProfilePicture='True'
UNION
SELECT 'iosapp',count(hasIosApp) FROM users_data WHERE hasIosApp='True'
UNION
SELECT 'Android app',count(hasAndroidApp) FROM users_data WHERE hasAndroidApp='True'
UNION
SELECT 'Application for Ecommerce platform',count(hasAnyApp) FROM users_data WHERE hasAnyApp='True';

8)

SELECT country,sum(ProductsBought) total_number_of_buyer FROM users_data GROUP BY country
ORDER BY total_number_of_buyer DESC ;

9)

SELECT Country,avg(ProductsSold) total_number_of_sellers FROM users_data GROUP BY Country
ORDER BY sum(ProductsSold) ASC;

10)

SELECT country,sum(productsPassRate) top FROM users_data GROUP BY
country ORDER BY sum(productsPassRate) DESC limit 10;

11)

SELECT language, count(type) FROM users_data GROUP BY language;

12)

SELECT gender,sum(ProductsWished), sum(Socialproductsliked) FROM users_data
WHERE Gender='F' and hasAnyApp='True';

13)

SELECT gender,sum(productsBought),sum(productsSold) FROM users_data WHERE gender='M';

14)

SELECT country,sum(ProductsBought) buyer FROM users_data GROUP BY country
ORDER BY sum(ProductsBought) DESC limit 1;

15)

SELECT country,sum(ProductsSold) sellers FROM users_data
GROUP BY country ORDER BY sum(ProductsSold) limit 10;

16)

SELECT * FROM users_data ORDER BY daysSinceLastLogin limit 110;

17)

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

18)

SELECT country,count(gender) female FROM users_data WHERE gender='F' GROUP BY country;

19)

SELECT country,count(gender) male FROM users_data WHERE gender='M' GROUP BY country;

20)

SELECT avg(Productssold),avg(ProductsBought),gender,country FROM users_data
WHERE gender='M' GROUP BY country ;