16/400340/TK/45354
mysql> create database TA_45354;
Query OK, 1 row affected (0,00 sec)
mysql> use TA_45354;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| TA_45354 |
+------------+
1 row in set (0,00 sec)
mysql> create table me (my_id int(2) not null auto_increment, first_name varchar(25) not null, last_name varchar(25) not null, primary key(my_id));
Query OK, 0 rows affected (0,24 sec)
mysql> describe me;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| my_id | int(2) | NO | PRI | NULL | auto_increment |
| first_name | varchar(25) | NO | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0,01 sec)
mysql> insert into me (first_name, last_name) values ('Afifah', 'Zuliansyah'), ('Fauziah', 'Mustafrizal'), ('Kemala', 'Mernisi'), ('Suci', 'Yulia');
Query OK, 4 rows affected (0,07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from me;
+-------+------------+-------------+
| my_id | first_name | last_name |
+-------+------------+-------------+
| 1 | Afifah | Zuliansyah |
| 2 | Fauziah | Mustafrizal |
| 3 | Kemala | Mernisi |
| 4 | Suci | Yulia |
+-------+------------+-------------+
4 rows in set (0,00 sec)
mysql> create table social_networks (network_code int(2) not null primary key, network_name varchar(15) not null, network_description varchar(50) not null);
Query OK, 0 rows affected (0,23 sec)
mysql> describe social_networks;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| network_code | int(2) | NO | PRI | NULL | |
| network_name | varchar(15) | NO | | NULL | |
| network_description | varchar(50) | NO | | NULL | |
+---------------------+-------------+------+-----+---------+-------+
3 rows in set (0,00 sec)
mysql> insert into social_networks values (5, 'facebook', 'www.facebook.com'), (6, 'twitter', 'www.twitter.com');
Query OK, 2 rows affected (0,04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select*from social_networks;
+--------------+--------------+---------------------+
| network_code | network_name | network_description |
+--------------+--------------+---------------------+
| 5 | facebook | www.facebook.com |
| 6 | twitter | www.twitter.com |
+--------------+--------------+---------------------+
2 rows in set (0,00 sec)
mysql> create table my_email_login (my_login int(2) not null primary key auto_increment, my_id int(2) not null, network_code int(2) not null, date_signed_up datetime not null, my_email_name varchar(50) not null, my_email_password varchar(255) not null, constraint fk_id foreign key (my_id) references me (my_id), constraint fk_code foreign key (network_code) references social_networks (network_code));
Query OK, 0 rows affected (0,33 sec)
mysql> describe my_email_login;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| my_login | int(2) | NO | PRI | NULL | auto_increment |
| my_id | int(2) | NO | MUL | NULL | |
| network_code | int(2) | NO | MUL | NULL | |
| date_signed_up | datetime | NO | | NULL | |
| my_email_name | varchar(50) | NO | | NULL | |
| my_email_password | varchar(255) | NO | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
6 rows in set (0,00 sec)
mysql> alter table my_email_login auto_increment=7;
Query OK, 0 rows affected (0,02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into my_email_login (my_id, network_code, date_signed_up, my_email_name, my_email_password) values (1, 5, '2017-10-22', 'afifahs07@gmail.com', SHA2('as',224)), (2, 6, '2017-10-23', 'fauziah27@gmail.com', SHA2('fm',224)), (3, 5, '2017-10-24', 'kemala12@gmail.com', SHA2('km',224)), (4, 6, '2017-10-22', 'suciyp02@gmail.com', SHA2('sy',224)), (1, 6, '2017-10-23', 'afifahs07@gmail.com', SHA2('as',224));
Query OK, 5 rows affected (0,11 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from my_email_login;
+----------+-------+--------------+---------------------+---------------------+----------------------------------------------------------+
| my_login | my_id | network_code | date_signed_up | my_email_name | my_email_password |
+----------+-------+--------------+---------------------+---------------------+----------------------------------------------------------+
| 7 | 1 | 5 | 2017-10-22 00:00:00 | afifahs07@gmail.com | b162f6c91e9d02b7eba0c8dc0d4b0ac20002d47bcafa29699a54a682 |
| 8 | 2 | 6 | 2017-10-23 00:00:00 | fauziah27@gmail.com | c5c1bcbf5b107da2b1d632d9e99296d08bba21fd597fbdcd5f374fc1 |
| 9 | 3 | 5 | 2017-10-24 00:00:00 | kemala12@gmail.com | 6485506c27b04cf34e859d036c0502ae982d1d6cf8d48a285ea18ff0 |
| 10 | 4 | 6 | 2017-10-22 00:00:00 | suciyp02@gmail.com | ec7724d7cbbd47739ee9ab023d0aab323ab9c8191e5a60d84e0f8cd6 |
| 11 | 1 | 6 | 2017-10-23 00:00:00 | afifahs07@gmail.com | b162f6c91e9d02b7eba0c8dc0d4b0ac20002d47bcafa29699a54a682 |
+----------+-------+--------------+---------------------+---------------------+----------------------------------------------------------+
5 rows in set (0,00 sec)
mysql> create table mail_boxes (mail_id int(2) not null primary key auto_increment, my_login int(2) not null, date_received datetime not null, from_email_address varchar(50) not null, from_name varchar(50) not null, subject varchar(25) not null, message varchar(200) not null, constraint fk_mailboxes foreign key (my_login) references my_email_login (my_login));
Query OK, 0 rows affected (0,29 sec)
mysql> alter table mail_boxes auto_increment=12;
Query OK, 0 rows affected (0,04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe mail_boxes;
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| mail_id | int(2) | NO | PRI | NULL | auto_increment |
| my_login | int(2) | NO | MUL | NULL | |
| date_received | datetime | NO | | NULL | |
| from_email_address | varchar(50) | NO | | NULL | |
| from_name | varchar(50) | NO | | NULL | |
| subject | varchar(25) | NO | | NULL | |
| message | varchar(200) | NO | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
7 rows in set (0,01 sec)
mysql> insert into mail_boxes (my_login, date_received, from_email_address, from_name, subject, message) values (7, '2017-10-22 19:46:00', 'fauziah27@gmail.com', 'Fauziah Mustafrizal', 'kangen', 'kangen banget'), (8, '2017-10-23 10:27:19 ', 'suciyp02@gmail.com', 'Suci Yulia', 'rindu', 'rindu khikhi'), (9, '2017-10-24 15:08:48', 'afifahs07@gmail.com', 'Afifah Zuliansyah', 'missyu', 'missyu so much'), (10, '2017-10-22 09:15:59', 'afifahs07@gmail.com', 'Afifah Zuliansyah', 'luvluv', 'loveu'), (11, '2017-10-23 20:19:06', 'kemala12@gmail.com', 'Kemala Mernisi', 'adadeh', 'HBd yaw^^');
Query OK, 5 rows affected (0,04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from mail_boxes;
+---------+----------+---------------------+---------------------+---------------------+---------+----------------+
| mail_id | my_login | date_received | from_email_address | from_name | subject | message |
+---------+----------+---------------------+---------------------+---------------------+---------+----------------+
| 12 | 7 | 2017-10-22 19:46:00 | fauziah27@gmail.com | Fauziah Mustafrizal | kangen | kangen banget |
| 13 | 8 | 2017-10-23 10:27:19 | suciyp02@gmail.com | Suci Yulia | rindu | rindu khikhi |
| 14 | 9 | 2017-10-24 15:08:48 | afifahs07@gmail.com | Afifah Zuliansyah | missyu | missyu so much |
| 15 | 10 | 2017-10-22 09:15:59 | afifahs07@gmail.com | Afifah Zuliansyah | luvluv | loveu |
| 16 | 11 | 2017-10-23 20:19:06 | kemala12@gmail.com | Kemala Mernisi | adadeh | HBd yaw^^ |
+---------+----------+---------------------+---------------------+---------------------+---------+----------------+
5 rows in set (0,00 sec)
mysql> create table sent_messages (message_id int(2) not null primary key auto_increment, my_login int(2) not null, date_sent datetime not null, to_email_address varchar(50) not null, subject varchar(25) not null, message varchar(200) not null, constraint fk_sent foreign key (my_login) references my_email_login (my_login));
Query OK, 0 rows affected (0,27 sec)
mysql> alter table sent_messages auto_increment=17;
Query OK, 0 rows affected (0,03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe sent_messages;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| message_id | int(2) | NO | PRI | NULL | auto_increment |
| my_login | int(2) | NO | MUL | NULL | |
| date_sent | datetime | NO | | NULL | |
| to_email_address | varchar(50) | NO | | NULL | |
| subject | varchar(25) | NO | | NULL | |
| message | varchar(200) | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
6 rows in set (0,00 sec)
mysql> insert into sent_messages (my_login, date_sent, to_email_address, subject, message) values (8, '2017-10-22 19:45:59', 'afifahs07@gmail.com', 'kangen', 'kangen banget'), (10, '2017-10-23 10:27:18', 'fauziah27@gmail.com', 'rindu', 'rindu khikhi'), (11, '2017-10-24 15:08:47', 'kemala12@gmail.com', 'missyu', 'missyu so much'), (7, '2017-10-22 09:15:58', 'suciyp02@gmail.com', 'luvluv', 'loveu'), (9, '2017-10-23 20:19:05', 'afifahs07@gmail.com', 'adadeh', 'HBd yaw^^');
Query OK, 5 rows affected (0,10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from sent_messages;
+------------+----------+---------------------+---------------------+---------+----------------+
| message_id | my_login | date_sent | to_email_address | subject | message |
+------------+----------+---------------------+---------------------+---------+----------------+
| 17 | 8 | 2017-10-22 19:45:59 | afifahs07@gmail.com | kangen | kangen banget |
| 18 | 10 | 2017-10-23 10:27:18 | fauziah27@gmail.com | rindu | rindu khikhi |
| 19 | 11 | 2017-10-24 15:08:47 | kemala12@gmail.com | missyu | missyu so much |
| 20 | 7 | 2017-10-22 09:15:58 | suciyp02@gmail.com | luvluv | loveu |
| 21 | 9 | 2017-10-23 20:19:05 | afifahs07@gmail.com | adadeh | HBd yaw^^ |
+------------+----------+---------------------+---------------------+---------+----------------+
5 rows in set (0,00 sec)
mysql> create table notifications (notification_id int(2) not null primary key auto_increment, my_login int(2) not null, date_received datetime not null, subject varchar(25) not null, message varchar(200) not null, constraint fk_notifications foreign key (my_login) references my_email_login (my_login));
Query OK, 0 rows affected (0,26 sec)
mysql> alter table notifications auto_increment=22;
Query OK, 0 rows affected (0,03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe notifications;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| notification_id | int(2) | NO | PRI | NULL | auto_increment |
| my_login | int(2) | NO | MUL | NULL | |
| date_received | datetime | NO | | NULL | |
| subject | varchar(25) | NO | | NULL | |
| message | varchar(200) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0,00 sec)
mysql> insert into notifications (my_login, date_received, subject, message) values (7, '2017-10-26 19:00:09', 'post commented', 'SeungHo commented "i will be there"'), (8, '2017-10-27 16:25:56', 'retweet', 'JooHyuk retweet your tweet'), (9, '2017-10-28 23:08:00', 'mentioned', 'Minho mentioned you'), (10, '2017-10-29 18:09:45', 'tweet liked', 'SooHyun liked your tweet'), (11, '2017-10-30 15:48:37', 'tweet liked', 'ChangWook liked your tweet');
Query OK, 5 rows affected (0,04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from notifications;
+-----------------+----------+---------------------+----------------+-------------------------------------+
| notification_id | my_login | date_received | subject | message |
+-----------------+----------+---------------------+----------------+-------------------------------------+
| 22 | 7 | 2017-10-26 19:00:09 | post commented | SeungHo commented "i will be there" |
| 23 | 8 | 2017-10-27 16:25:56 | retweet | JooHyuk retweet your tweet |
| 24 | 9 | 2017-10-28 23:08:00 | mentioned | Minho mentioned you |
| 25 | 10 | 2017-10-29 18:09:45 | tweet liked | SooHyun liked your tweet |
| 26 | 11 | 2017-10-30 15:48:37 | tweet liked | ChangWook liked your tweet |
+-----------------+----------+---------------------+----------------+-------------------------------------+
5 rows in set (0,00 sec)
mysql> create table updates (update_id int(2) not null primary key auto_increment, my_login int(2) not null, date_received datetime not null, subject varchar(25) not null, message varchar(200) not null, constraint fk_updates foreign key (my_login) references my_email_login (my_login));
Query OK, 0 rows affected (0,27 sec)
mysql> alter table updates auto_increment=27;
Query OK, 0 rows affected (0,04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe updates;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| update_id | int(2) | NO | PRI | NULL | auto_increment |
| my_login | int(2) | NO | MUL | NULL | |
| date_received | datetime | NO | | NULL | |
| subject | varchar(25) | NO | | NULL | |
| message | varchar(200) | NO | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
5 rows in set (0,00 sec)
mysql> insert into updates (my_login, date_received, subject, message) values (7, '2017-10-30 22:00:00', 'app update', 'download from playstore'), (8, '2017-10-26 22:00:59', 'privacy update', 'new version'), (9, '2017-10-30 22:00:00', 'app update', 'download from playstore'), (10, '2017-10-30 22:00:00', 'app update', 'download from playstore'), (11, '2017-10-28 23:50:33', 'new feature', 'stories');
Query OK, 5 rows affected (0,03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from updates;
+-----------+----------+---------------------+----------------+-------------------------+
| update_id | my_login | date_received | subject | message |
+-----------+----------+---------------------+----------------+-------------------------+
| 27 | 7 | 2017-10-30 22:00:00 | app update | download from playstore |
| 28 | 8 | 2017-10-26 22:00:59 | privacy update | new version |
| 29 | 9 | 2017-10-30 22:00:00 | app update | download from playstore |
| 30 | 10 | 2017-10-30 22:00:00 | app update | download from playstore |
| 31 | 11 | 2017-10-28 23:50:33 | new feature | stories |
+-----------+----------+---------------------+----------------+-------------------------+
Menghitung jumlah pesan yang diterima
mysql> select count(message) from mail_boxes;
+----------------+
| count(message) |
+----------------+
| 5 |
+----------------+
1 row in set (0,00 sec)
mysql> select to_email_address,count(message) from sent_messages where to_email_address='afifahs07@gmail.com';
+---------------------+----------------+
| to_email_address | count(message) |
+---------------------+----------------+
| afifahs07@gmail.com | 2 |
+---------------------+----------------+
1 row in set (0,00 sec)
Melihat tujuan email dari facebook
mysql> select from_name, to_email_address, sent_messages.subject, sent_messages.message from mail_boxes join sent_messages where sent_messages.my_login in (select my_login from my_email_login where network_code in(select network_code from social_networks where network_name='facebook'))&&mail_boxes.subject=sent_messages.subject;
+-------------------+---------------------+---------+-----------+
| from_name | to_email_address | subject | message |
+-------------------+---------------------+---------+-----------+
| Afifah Zuliansyah | suciyp02@gmail.com | luvluv | loveu |
| Kemala Mernisi | afifahs07@gmail.com | adadeh | HBd yaw^^ |
+-------------------+---------------------+---------+-----------+
2 rows in set (0,00 sec)
Melihat pesan yang diterima
mysql> select from_name, from_email_address, message, date_received from mail_boxes;
+---------------------+---------------------+----------------+---------------------+
| from_name | from_email_address | message | date_received |
+---------------------+---------------------+----------------+---------------------+
| Fauziah Mustafrizal | fauziah27@gmail.com | kangen banget | 2017-10-22 19:46:00 |
| Suci Yulia | suciyp02@gmail.com | rindu khikhi | 2017-10-23 10:27:19 |
| Afifah Zuliansyah | afifahs07@gmail.com | missyu so much | 2017-10-24 15:08:48 |
| Afifah Zuliansyah | afifahs07@gmail.com | loveu | 2017-10-22 09:15:59 |
| Kemala Mernisi | kemala12@gmail.com | HBd yaw^^ | 2017-10-23 20:19:06 |
+---------------------+---------------------+----------------+---------------------+
5 rows in set (0,00 sec)
mysql> select my_email_login.my_email_name, mail_boxes.from_email_address, mail_boxes.message from mail_boxes, my_email_login where my_email_login.my_login=mail_boxes.my_login and mail_boxes.my_login=7;
+---------------------+---------------------+---------------+
| my_email_name | from_email_address | message |
+---------------------+---------------------+---------------+
| afifahs07@gmail.com | fauziah27@gmail.com | kangen banget |
+---------------------+---------------------+---------------+
1 row in set (0,00 sec)
mysql> select my_email_login.my_email_name, count(mail_boxes.message) from mail_boxes, my_email_login where my_email_login.my_login=mail_boxes.my_login and mail_boxes.my_login=8;
+---------------------+---------------------------+
| my_email_name | count(mail_boxes.message) |
+---------------------+---------------------------+
| fauziah27@gmail.com | 1 |
+---------------------+---------------------------+
1 row in set (0,00 sec)
