Project 1

Q1. Create new schema as ecommerce

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)

i tried somany times but user_data.csv shows the error as shown in above screen shot.

=====================================================

Q3. Run SQL command to see the structure of table

USE ecommerce;

DESC 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) Country,
COUNT(DISTINCT language) Language
FROM
users_data;

==========================================================

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

SELECT
gender, MAX(socialNbFollowers) Max_Followers
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(*) 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 DISTINCT
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 DISTINCT
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
COUNT(productsBought)
FROM
users_data
WHERE
(gender = 'M' AND productsBought > 0)
UNION SELECT
COUNT(productsSold)
FROM
users_data
WHERE
(gender = 'M' AND productsSold > 0);

=================================================

Q14. Which country is having maximum number of buyers?

SELECT
country, COUNT(productsBought)
FROM
users_data
GROUP BY country
ORDER BY COUNT(productsBought) DESC;

====================================================

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

SELECT
country, SUM(productsSold) sellers
FROM
users_data
GROUP BY country
ORDER BY SUM(productsSold)
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) Female
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' AND hasAnyApp='True'
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' AND hasAnyApp = 'True'
GROUP BY country;

===============================================

Q20. Calculate the average number of products sold and bought on
ecommerce platform by male users for each country. */

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

===================================================

This is the Complete SQL PROJECT 1 . AND Please Find Below Collected project1.sql File