· 7 years ago · Nov 29, 2018, 09:58 PM
1Create table surdata
2
3create table surdata( id int not null primary key auto_increment, email varchar(50) not null, pnumber int not null, city varchar(50) not null);
4
5describe surdata
6
7 desc surdata;
8+---------+-------------+------+-----+---------+----------------+
9| Field | Type | Null | Key | Default | Extra |
10+---------+-------------+------+-----+---------+----------------+
11| id | int(11) | NO | PRI | NULL | auto_increment |
12| email | varchar(50) | NO | | NULL | |
13| pnumber | int(11) | NO | | NULL | |
14| city | varchar(50) | NO | | NULL | |
15+---------+-------------+------+-----+---------+----------------+
16
17
18Import data to surdata:
19
20load data local infile '/home/ankittomar/Downloads/mysql/email_subscribers.txt' into table surdata fields terminated by ','(email,pnumber,city);
21
22Query OK, 200 rows affected (0.00 sec)
23Records: 200 Deleted: 0 Skipped: 0 Warnings: 0
24
25
26
27Select from surdata:
28
29select * from surdata limit 10;
30+----+----------------------+---------+-----------+
31| id | email | pnumber | city |
32+----+----------------------+---------+-----------+
33| 1 | 53e1228@hotmail.com | 982958 | Lucknow |
34| 2 | 4e68a8c@hotmail.com | 9810776 | Chennai |
35| 3 | 7650b89@yahoo.com | 9832386 | Kolkatta |
36| 4 | f9b1317@yahoo.com | 9815433 | Delhi |
37| 5 | fd6515f@me.com | 9820667 | Delhi |
38| 6 | b389f91@gmail.com | 983429 | Kolkatta |
39| 7 | 7b60d9f@me.com | 983392 | Mumbai |
40| 8 | f415e11@yahoo.com | 9816538 | Lucknow |
41| 9 | cfd4611@hotmail.com | 9819398 | Kolkatta |
42| 10 | 53ac4bc@yahoo.com | 989347 | Kolkatta |
43+----+----------------------+---------+-----------+
44
45
46
47
48
49
50
51
52
531. what all cities did people respond from?
54
55 select distinct city from surdata;
56+-----------+
57| city |
58+-----------+
59| Lucknow |
60| Chennai |
61| Kolkatta |
62| Delhi |
63| Mumbai |
64+-----------+
65
66
672. How many people responded from each city?
68
69 select city, count(*) as Number_of_people from surdata group by city;
70+-----------+------------------+
71| city | Number_of_people |
72+-----------+------------------+
73| Chennai | 42 |
74| Delhi | 40 |
75| Kolkatta | 38 |
76| Lucknow | 39 |
77| Mumbai | 41 |
78+-----------+------------------+
79
80
81
82
833. Which city were the maximum respondents from?
84
85select city,count(*) as Number_of_People from surdata group by city having count(*) = (select count(*) as counted from surdata group by city order by counted desc limit 1);
86+----------+------------------+
87| city | Number_of_People |
88+----------+------------------+
89| Chennai | 42 |
90+----------+------------------+
91
92
93
94
954. What all email domains did people respond from ?
96
97select distinct substring(email,locate('@',email)) as Domains from surdata;
98+--------------+
99| Domains |
100+--------------+
101| @hotmail.com |
102| @yahoo.com |
103| @me.com |
104| @gmail.com |
105+--------------+
1064 rows in set (0.00 sec)
107
108
109
110
1115. Which is the most popular email domain among the respondents ?
112
113
114select substring(email,locate('@',email)) as Domain ,count(*) as Maximum_used_Domain from surdata group by substring(email,locate('@',email)) having count(*) = (select count(*) as counted from surdata group by substring(email,locate('@',email)) order by counted desc limit 1);
115+-------------+---------------------+
116| Domain | Maximum_used_Domain |
117+-------------+---------------------+
118| @me.com | 51 |
119| @yahoo.com | 51 |
120+-------------+---------------------+