MySQL The following tables have already been created for you. Each table is alre

Question

MySQL

The following tables have already been created for you. Each table is already populated with some records.

Table Name: person

Field---------------------- Type-------------------- Notes

person_id----------------int(8)-------------------- ? Primary key ? Auto-increment value ? Required

first_name-------------- varchar(25)------------ ? Required

last_name------------- varchar(25)------------ ? Required

Table Name: contact_list

Field-------------------- Type--------------------- Notes

connection_id-------- int(8)------------------- ? Primary key ? Auto-increment value ? Required

person_id-------------- int(8)----------------- ? Required

contact_id------------- int(8)------------------ ? Required

Table Name: message

Field------------------- Type-------------------- Notes

message_id----------- int(8)---------------- ? Primary key ? Auto-increment value ? Required

sender_id-------------- int(8)----------------- ? Required

receiver_id------------- int(8)--------------- ? Required

message--------------- varchar(255)------ ? Required

send------------------_datetime datetime---- ? Required

mysql> SHOW TABLES;
+---------------------+
| Tables_in_messaging |
+---------------------+
| contact_list----------- |
| image------------------ |
| message-------------- |
| message_image--- |
| person--------------- |
+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM contact_list;
+---------------+-----------+------------+----------+
| connection_id | person_id | contact_id | favorite |
+---------------+-----------+------------+----------+
| 1---------------- | 1------------- | 2----------- | n |
| 2---------------- | 1 ------------| 3------------ | n |
| 3---------------- | 1------------ | 4------------ | n |
| 4---------------- | ------------1 | 5------------- | n |
| 5---------------- | 1------------ | 6------------- | n |
| 6----------------- | 2----------- | 1------------- | y |
| 7----------------- | 2----------- | 3------------- | n |
| 8----------------- | 2----------- | 4------------- | n |
| 9----------------- | 3----------- | 1-------------- | y |
| 10--------------- | 3------------ | 4------------- | n |
| 11---------------- | 4----------- | 5------------- | n |
| 12---------------- | 4----------- | 6------------- | n |
| 13----------------- | 5---------- | 1------------- | y |
| 14---------------- | 5------------ | 6------------ | n |
| 15---------------- | 7------------- | 1------------ | y |
| 16--------------- | 8-------------- | 3----------- | n |
| 17--------------- | 9-------------- | 5------------- | n |
+---------------+-----------+------------+----------+
17 rows in set (0.00 sec)

ysql> SELECT * FROM image;
+----------+-------------------+----------------+
| image_id | image_name | image_location |
+----------+-------------------+----------------+
| 1-------- | Disney world--- | Orlando |
| 2-------- | Vatican---------- | Rome |
| 3---------- | Eiffel Tower--- | Paris |
| 4---------- | Nile---river----- | Egypt |
| 5------------ | Grand-- Canyon- | Arizona |
+----------+-------------------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM message;
+------------+-----------+-------------+--------------------------------------------+---------------------+
| message_id----------- | sender_id -----------| receiver_id |------------------ message | -----------------------send_datetime |
+------------+-----------+-------------+--------------------------------------------+---------------------+
| 1------------------------- | 1 -----------------------| 2------------ | Congrats on winning the 800m Freestyle! | 2016-12-25 09:00:00 |
| 2---------------------- | 2-------------------------- | 1 ------------| Congrats on winning 23 gold medals! | 2016-12-25 09:01:00 |
| 3---------------------- | 3-------------------------- | 1-------------- | You're the greatest swimmer ever | 2016-12-25 09:02:00 |
| 4---------------------- | 1----------------------- | 3 -----------------| Thanks! You're the greatest sprinter ever | 2016-12-25 09:04:00 |
| 5 ---------------------| 1----------------------- | 4------------------ | Good luck on your race | 2016-12-25 09:05:00 |
+------------+-----------+-------------+--------------------------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM message_image;
+------------+----------+
| message_id | image_id |
+------------+----------+
| 1---------- | 1 |
| 2----------- | 3 |
| 3---------- | 2 |
| 4----------- | 1 |
| 5---------- | 4 |
+------------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM person;
+-----------+------------+-----------+------------+
| person_id | first_name | last_name | occupation |
+-----------+------------+-----------+------------+
| 1----------- | Michael | Phelps | --------programmer |
| 2---------- | Katie | Ledecky | ----------programmer |
| 3 ----------| Usain | Bolt |---------------- programmer |
| 4--------- | Allyson | Felix |------------- programmer |
| 5--------- | Kevin | Durant |-------------- programmer |
| 7-------- | Anthony | Hopkins |---------- programmer |
| 8---------- | Micheal | Jordan |----------- NULL |
| 9----------- | Don | Johnson--------------- | NULL |
+-----------+------------+-----------+------------+

Task 1

Find All of the Messages that Michael Phelps Sent

Construct the SQL statement to find all of the messages that Michael Phelps sent. Note: You must use the WHERE clause to set the conditions for this query. 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

Explanation / Answer

Task 1: Find All of the Messages that Michael Phelps Sent

Construct the SQL statement to find all of the messages that Michael Phelps sent. Note: You must use the WHERE clause to set the conditions for this query. 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

Answer)

select message_id, (select first_name, last_name from person where person_id=sender_id), (select first_name, last_name from person where person_id=receiver_id), message, send_datetime where sender_id in (

select person_id from person where first_name='Michael' and last_name='Phelps');

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.