Question
(the bottom two tables in red text which are hard to read are the image table and message_image table respectively) USE messaging database Task 1:
(the bottom two tables in red text which are hard to read are the "image" table and "message_image" table respectively)
USE "messaging" database
Task 1: Insert Record to the Person Table
Construct the SQL statement to add yourself to the Person table. Note: You are required to 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.
Task 2: Alter the Person Table
Construct the SQL statement to alter the table named person. The columns, column data types, and column notes are provided in the previous section. You need to alter the table to include an additional column of your choice. This column should represent some property of a person. You can choose the data type for the column and any constraints on the column.
Task 3: Update Records in the Person Table
Construct the SQL statement to update the existing record in the person table to use the new column that you created. Update your record (the record with your first and last name) in the Person table by setting some value to your new property.
Task 4: Delete Records from Person Table
Construct the SQL statement to delete the record(s) from the person table where the first name is Diana and the last name is Taurasi.
Task 5: Alter the Contact List Table
Construct the SQL statement to alter the table named contact_list. The columns, column data types, and column notes are provided in the previous section. You need to alter the table to include an additional column named favorite with a data type of varchar(10). This column is not required.
Task 6: Update Records in the Contact List Table
Construct the SQL statement to update the existing records in the contact_list table to use the new column that you created. Update the record(s) 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.
Task 7: Update Records in the Contact List Table
Construct the SQL statement to 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.
Task 8: Insert Records to Contact List Table
Construct the SQL statement to insert at least 3 new records in the contact_list table. Make sure that you use the new column that you created in the previous step. 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.
Task 9: Create the Image Table (see photo above)
Construct the SQL statement to create a table named image. The columns, column data types, and column notes are provided here. Create the image table according to these specifications.
Task 10: Create the Message-Image Intersection Table (see photo above)
Construct the SQL statement to create an intersection table named message_image. The columns, column data types, and column notes are provided here. Create the message_image table according to these specifications.
Task 11: Insert Records to Image Table
Construct the SQL statement to insert 5 new records in the image table.
Task 12: Insert Records to Message-Image Table
Construct the SQL statement to insert 5 new records in the message_image intersection table. Note: You are required to add at least one record where at least one of Michael Phelp's messages includes at least one image. Also, you are required to add at least one message that has multiple images.
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
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
Database Schema Notes Type Notes intl8) .Primary key Requirad Required connection id Primary key Required Required intl8 archari25) Field Notes contect id Required varchari25 Required nt(8) .Primery key varchar 10) Not required Custom Column Your Choice Your Choice Required ender id 'Required nt(a) .Required varchar2ss) 'Required Notes Field Notes Required Primary key Required Required Primary key Required imase idnt(s) Primary key Required Required sql> SELECT FROM contact list; | connection_id |I person id | contact_id I 4 4 6 6 4 4 4 4 12 14 6 14 rows in set (8.08 sec) sal> SELECT FROM message message id | sender_idreceiver_id message l send datetime 2 | Congrats on winning the 888m Freestyle I | 2016-12-25 9:00:88 1 1Congrats on winning 23 gold medals! 1You're the greatest swimmer ever 3 | Thanks ! You're the greatest sprinter eve r | 2016-12-25 9:04:88 | 4| Good luck on your race 4 rows in set (0.88 sec) sql> SELECT FROM person; | person_^id | first_name Last_name | 1 | Michael| Phelps 2Katie 3 Usain | Ledecky [ Bolt 4 | ALlyson Felix 5 Kevin Durant oDiana aurasT
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started