SQL Student need help writing SQL STATEMENTS

antinod331 · April 19, 2019, 6:08am

Here are the tables!

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

Table Name: image
Field Type Notes
image_id
int(8)
 Primary key
 Auto-increment value
 Required
image_name
varchar(50)
 Required
image_location
varchar(250)
 Required

Table Name: message_image
Field Type Notes
message_id
int(8)
 Primary key
 Required
image_id
int(8)
 Primary key
 Required

These are the FIVE tables withe the specs. I need an SQL STATEMENT answering these two questions:

Task 14: 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 15: 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

Task 13: 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
I would greatly appreciate the help writing these SQL statements.

Antino

antinod331 · April 19, 2019, 7:48am

The tables with the data.

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 | |
| 7 | 2 | 3 | n |
| 8 | 2 | 4 | n |
| 9 | 3 | 1 | |
| 10 | 3 | 4 | n |
| 11 | 4 | 5 | n |
| 12 | 4 | 6 | n |
| 13 | 5 | 1 | |
| 14 | 5 | 6 | n |
| 15 | 7 | 7 | n |
| 16 | 8 | 8 | n |
| 17 | 9 | 9 | n |
+---------------+-----------+------------+----------+
17 rows in set (0.00 sec)
Contact_list table above

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.01 sec)
message table above

mysql> select * from message_image;
+------------+----------+
| message_id | image_id |
+------------+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+------------+----------+
5 rows in set (0.00 sec)
message_image table above

Thanks for your help!!!
Antino

graz · April 20, 2019, 2:36pm

What have you tried so far?

antinod331 · April 20, 2019, 3:30pm

Hello Graz,
I haven't tried much, don't really know where to begin other than the SELECT command. I know I need a WHERE command . There are three questions that are baffling me. I believe on two of the questions, I probably also need a JOIN or INNER JOIN command. I need help putting these statements together.

Thanks, Antino

jeffw8713 · April 22, 2019, 3:07pm

This is a MS SQL Server forum - and you are using MySQL. Any potential solution here may not work in MySQL.

Task 1: this requires an aggregate query using GROUP BY and COUNT to get the results.

SELECT ...
  FROM {your tables}
 GROUP BY
       {columns};

Task 2: there are several possibilities on how to approach this - some are specific to MS SQL Server. Using INNER JOIN to a derived table where the derived table uses GROUP BY is one possibility.

SELECT ...
  FROM {message table} t1
 INNER JOIN {message_image} t2 ON t2.{key columns} = t1.{key columns}
 INNER JOIN (SELECT {key columns}, MIN(...)
               FROM {image table} it
              GROUP BY
                    {group colums}) t3 ON t3.{key columns} = t2.{key columns}

For the above query - you need to join from the message table to the message_image table - and to the image table. The GROUP BY query will use aggregates to return the first (min) values for the columns.

Task 3: This one will require an INNER JOIN - and a WHERE clause to identify only those rows associated with the specified individual.

SELECT ...
  FROM table1 t1
 INNER JOIN table2 t2 ON t2.{key columns} = t1.{key columns}
 WHERE {some column} = {value}