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

Task 2: Find the Number of Messages Sent for Every Person

Construct the SQL statement to find the number of messages sent for every person. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns: - Count of messages - Person ID - First Name - Last Name

Task 3: Find All of the Messages that Have At Least One Image Attached Using INNER JOINs

Construct the SQL statement to find all of the messages that have at least one image attached using INNER JOINs. Note: For messages with multiple images, display only the first image for the message. Display the following columns: - Message ID - Message - Message Timestamp - First Image Name - First image location

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');

Task 2: Find the Number of Messages Sent for Every Person

Construct the SQL statement to find the number of messages sent for every person. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns: - Count of messages - Person ID - First Name - Last Name

Answer)

select count(sender_id),person.person_id, person.first_name, person.last_name from person inner join message

on person.person_id=message.receiver_id

group by receiver_id;

Task 3: Find All of the Messages that Have At Least One Image Attached Using INNER JOINs

Construct the SQL statement to find all of the messages that have at least one image attached using INNER JOINs. Note: For messages with multiple images, display only the first image for the message. Display the following columns: - Message ID - Message - Message Timestamp - First Image Name - First image location

Answer)

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

select message_image.message_id, message.message, message.send_datetime, image.image_name, image.image_location from (

(message inner join message_image on message.message_id = message_image.message_id)

inner join image on image.image_id = message_image.image_id

);

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

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