Project 1

Q.NO.1

• First I right click on the graphical interface, then I select create schema and given apply

Q.NO.2

• I right click on the ecommerce, then table data import
• Then I choose the path file and select the option drop table if exists
• Then clicking next,next and Then finish.

Q.NO.3

• I first Selected the Database ecommerce by USE command
• Then, I use DESC command to see the structure of the table.

Q.NO.4

• I use SELECT command and LIMIT command to view the contents in the table for first 100 rows.

Q.NO.5

• I use DISTINCT command to view the distinct values in the column country and language.
• Distinct values in the country = 200
• Distinct values in the language = 5

Q.NO.6

• I use SUM function to find the total number of followers for female users and male users.
• Followers for male users = 77038
• Followers for female users = 262458
• Hence Female users have more followers than Male users.

Q.NO.7

• I use COUNT function to count the number of users.
  1. Users having profile picture = 97018
  2. Users having application = 26174
  3. Users having Androidapp = 4819
  4. Users having Ios App = 21527

Q.NO.8

• I have selected the country and count function for type to calculate the total number of buyers for each country by grouping country and sort the result in descending order for the total number of buyers by using Order By. 

Q.NO.9

• I have selected the country and count function for type to calculate the total number of sellers for each country by grouping country and sort the result in ascending order for the total number of sellers by using Order By. 

Q.NO.10

• I have selected country, sum of productsPassRate from users_data where productsPassRate is not equal to 0.
• And I use group by country and order by ProductsPassRate and by using the limit command I display only top ten countries for the products pass rate.

Q.NO.11

• I have selected language, using count function for the type from the users_data and group by language to display the number of users for different languages.

Q.NO.12

• Checking the choice of female users by using COUNT function and WHERE condition to get the expected result and  by using the UNION function we get the total number of users putting the products in a wishlist and liked in social platform.
• Number of female users putting the products in a wishlist = 7710
• Number of female users Likes the products in social platform = 13039

Q.NO.13

• Checking the choice of male users by using COUNT function and WHERE condition to get the expected result and  by using the UNION function we get the total number of users buying  the products and become buyers and some sell the products and become sellers.
• Number of male users being BUYERS = 1471
• Number of male users being SELLERS = 486

Q.NO.14

• FRANCE is the country where the maximum number of buyers is available.

Q.NO.15

 The name of 10 countries having zero number of sellers

1. Équateur
2. Irak
3. Maurice
4. Turquie
5. ÃŽles Vierges britanniques
6. Nicaragua
7. Macédoine
8. ÃŽles Vierges (U.S.)
9. Réunion
10. Chili

Q.NO.16

• A record of top 110 users who have used ecommerce platform recently is displayed by using SELECT command.
• By using Order BY and Limit function it displays the expected result.

Q.NO.17

• The number of female users who have not logged in = 70189
• By using the select command and count function based on the condition gender is female and days since last login should be greater than 100.

Q.NO.18

• First, I select country and count function for the gender from table
• Then, I use Where clause to count the female users only
• To display based on the country I use GROUP BY function on country.

Q.NO.19

• First, I select country and count function for the gender from table
• Then, I use Where clause to count the male users only
• To display based on the country I use GROUP BY function on country.

Q.NO.20

• First I have selected the country, average function for productssold and productsbought from table users_data
• Then I use where condition to restrict the data for male users only
• I use group by function on country to display for each country.