Create table surdata

create table surdata( id int not null primary key auto_increment, email varchar(50) not null, pnumber int not null, city varchar(50) not null);

describe surdata

 desc surdata;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| email   | varchar(50) | NO   |     | NULL    |                |
| pnumber | int(11)     | NO   |     | NULL    |                |
| city    | varchar(50) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+


Import data to surdata:

load data local  infile '/home/ankittomar/Downloads/mysql/email_subscribers.txt' into table surdata fields terminated by ','(email,pnumber,city);

Query OK, 200 rows affected (0.00 sec)
Records: 200  Deleted: 0  Skipped: 0  Warnings: 0



Select from surdata:

select * from surdata limit 10;
+----+----------------------+---------+-----------+
| id | email                | pnumber | city      |
+----+----------------------+---------+-----------+
|  1 | 53e1228@hotmail.com  |  982958 | Lucknow   |
|  2 | 4e68a8c@hotmail.com  | 9810776 |  Chennai  |
|  3 | 7650b89@yahoo.com    | 9832386 |  Kolkatta |
|  4 | f9b1317@yahoo.com    | 9815433 |  Delhi    |
|  5 | fd6515f@me.com       | 9820667 |  Delhi    |
|  6 | b389f91@gmail.com    |  983429 |  Kolkatta |
|  7 | 7b60d9f@me.com       |  983392 |  Mumbai   |
|  8 | f415e11@yahoo.com    | 9816538 |  Lucknow  |
|  9 | cfd4611@hotmail.com  | 9819398 |  Kolkatta |
| 10 | 53ac4bc@yahoo.com    |  989347 |  Kolkatta |
+----+----------------------+---------+-----------+









1. what all cities did people respond from?

 select distinct city from surdata;
+-----------+
| city      |
+-----------+
|  Lucknow  |
|  Chennai  |
|  Kolkatta |
|  Delhi    |
|  Mumbai   |
+-----------+


2. How many people responded from each city?

 select city, count(*) as Number_of_people from surdata group by city;
+-----------+------------------+
| city      | Number_of_people |
+-----------+------------------+
|  Chennai  |               42 |
|  Delhi    |               40 |
|  Kolkatta |               38 |
|  Lucknow  |               39 |
|  Mumbai   |               41 |
+-----------+------------------+




3. Which city were the maximum respondents from?

select 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);
+----------+------------------+
| city     | Number_of_People |
+----------+------------------+
|  Chennai |               42 |
+----------+------------------+




4. What all email domains did people respond from ?

select distinct substring(email,locate('@',email)) as Domains from surdata;
+--------------+
| Domains      |
+--------------+
| @hotmail.com |
| @yahoo.com   |
| @me.com      |
| @gmail.com   |
+--------------+
4 rows in set (0.00 sec)




5. Which is the most popular email domain among the respondents ?


select 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);
+-------------+---------------------+
| Domain      | Maximum_used_Domain |
+-------------+---------------------+
| @me.com     |                  51 |
| @yahoo.com  |                  51 |
+-------------+---------------------+