SQLTeam.com | Weblogs | Forums

SQL Student need help writing SQL STATEMENTS

#1

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:

  1. 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
  1. 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
  1. 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

#2

The tables with the data.

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 | |
| 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 image;
+----------+------------+----------------+
| image_id | image_name | image_location |
+----------+------------+----------------+
| 1 | good | bedroom |
| 2 | rough | familyroom |
| 3 | bad | livingroom |
| 4 | ugly | outside |
| 5 | poor | gameroom |
+----------+------------+----------------+
5 rows in set (0.00 sec)
Image table above

mysql> select * from person;
+-----------+------------+-----------+----------------+
| person_id | first_name | last_name | favorite_color |
+-----------+------------+-----------+----------------+
| 1 | Michael | Phelps | |
| 2 | Katie | Ledecky | |
| 3 | Usain | Bolt | |
| 4 | Allyson | Felix | |
| 5 | Kevin | Durant | |
| 7 | antino | donaldson | blue |
| 8 | tony | donaldson | |
| 9 | kevin | donaldson | |
+-----------+------------+-----------+----------------+
8 rows in set (0.00 sec)
Person 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

#3

What have you tried so far?

#4

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

#5

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}