1.Using the messaging.sql database (posted down of the question), write the following SQL queries.

Add yourself as a new record in the person table. Use your first name and last name for one of the new records that you are inserting.

Alter the Person table by adding a new property of your choice with the data type of your choice.

Update your record in the Person table by setting some value to your new property.

Delete the Diana Taurasi record.

Alter the Contact List table and add column for Favorite

Update the existing records in the contact_list table to use the new column that you created. Update the records in the table by setting Michael Phelps as everyone's favorite contact (contact_id = 1). The value for the favorite column should be set to y for these records.

Update the existing records in the contact_list table to use the new column that you created. Update the remaining record(s) in the table by setting every contact who is NOT Michael Phelps (contact_id <> 1) to not be a favorite. The value for the favorite column should be set to n for these records

Insert at least 3 new records in the contact_list table. Note: You are required to add at least 3 new records with yourself as a new contact in the contact_list table. Make sure that you provide a value (y or n) for the new favorite column.

Create a new table image with columns (image_id, image_name, image_location). Image_id is the primary key

Create a message-image table with columns (image_id,message_id). Image_id and message_id are the primary keys.

Insert 5 new records in the image table.

Insert 5 new records in the message-image table.

Find All of the Messages that Michael Phelps Sent. Display the following columns

Sender's first name

Sender's last name

Receiver's first name

Receiver's last name

Message ID

Message

Message Timestamp

Find the Number of Messages Sent for Every Person. Display the following columns

- Count of messages

Person ID

First Name

Last Name

Find All of the Messages that Have At Least One Image Attached Using INNER JOINs. Display the following columns:

Message ID

Message

Message Timestamp

First Image Name

First Image Location

message.sql database as follows:

DROP TABLE person CASCADE CONSTRAINTS; DROP TABLE contact_list CASCADE CONSTRAINTS; DROP TABLE message CASCADE CONSTRAINTS;

/* ========================= Create the Person table. Table Name: person Primary Key: person_id ========================= */ CREATE TABLE person ( person_id NUMBER NOT NULL, first_name VARCHAR(25) NOT NULL, last_name VARCHAR(25) NOT NULL, PRIMARY KEY (person_id) );

/* ========================= Create the Contact List table. Table Name: contact_list Primary Key: connection_id ========================= */ CREATE TABLE contact_list ( connection_id NUMBER NOT NULL, person_id NUMBER NOT NULL, contact_id NUMBER NOT NULL, PRIMARY KEY (connection_id) );

/* ========================= Create the Messages table. Table Name: message Primary Key: message_id ========================= */ CREATE TABLE message ( message_id NUMBER NOT NULL, sender_id NUMBER NOT NULL, receiver_id NUMBER NOT NULL, message VARCHAR(255) NOT NULL, send_datetime DATE NOT NULL, PRIMARY KEY (message_id) );

/* ========================= Populate the Person table. ========================= */ INSERT INTO person (person_id,first_name, last_name) VALUES (1, 'Michael', 'Phelps'); INSERT INTO person (person_id,first_name, last_name) VALUES (2, 'Katie', 'Ledecky'); INSERT INTO person (person_id,first_name, last_name) VALUES (3, 'Usain', 'Bolt'); INSERT INTO person (person_id,first_name, last_name) VALUES (4, 'Allyson', 'Felix'); INSERT INTO person (person_id,first_name, last_name) VALUES (5, 'Kevin', 'Durant'); INSERT INTO person (person_id,first_name, last_name) VALUES (6, 'Diana', 'Taurasi');

/* ========================= Populate the Contact List table. ========================= */ INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (1, 1, 2); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (2, 1, 3); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (3, 1, 4); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (4, 1, 5); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (5, 1, 6); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (6, 2, 1); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (7, 2, 3); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (8, 2, 4); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (9, 3, 1); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (10, 3, 4); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (11, 4, 5); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (12, 4, 6); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (13, 5, 1); INSERT INTO contact_list (connection_id, person_id, contact_id) VALUES (14, 5, 6);

/* ========================= Populate the Message table. ========================= */ INSERT INTO message (message_id, sender_id, receiver_id, message, send_datetime) VALUES (1, 1, 2, 'Congrats on winning the 800m Freestyle!', TO_DATE('2016-12-25 09:00:00','YYYY-MM-DD HH:MI:SS')); INSERT INTO message (message_id, sender_id, receiver_id, message, send_datetime) VALUES (2, 2, 1, 'Congrats on winning 23 gold medals!', TO_DATE('2016-12-25 09:01:00','YYYY-MM-DD HH:MI:SS')); INSERT INTO message (message_id, sender_id, receiver_id, message, send_datetime) VALUES (3, 3, 1, 'You're the greatest swimmer ever', TO_DATE('2016-12-25 09:02:00','YYYY-MM-DD HH:MI:SS')); INSERT INTO message (message_id, sender_id, receiver_id, message, send_datetime) VALUES (4, 1, 3, 'Thanks! You're the greatest sprinter ever', TO_DATE('2016-12-25 09:04:00','YYYY-MM-DD HH:MI:SS')); INSERT INTO message (message_id, sender_id, receiver_id, message, send_datetime) VALUES (5, 1, 4, 'Good luck on your race', TO_DATE('2016-12-25 09:05:00','YYYY-MM-DD HH:MI:SS'));

/* ========================= Verify Results ========================= */

/* ========================= Show the details for each of the tables created. DESCRIBE and SHOW COLUMNS FROM will do the same thing. i.e. DESCRIBE table_name; SHOW COLUMNS FROM table_name; ========================= */ select * from person; select * from contact_list; select * from message;