MySQL The following tables have already been created for you. Each table is alre
ID: 3907817 • Letter: M
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
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.
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
Custom Column-- Your Choice------ Your Choice
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.
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
favorite------------ varchar(10)--- ? 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
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.
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
Task 10: Create the Message-Image Intersection Table
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.
Table Name: message_image
Field----------- Type--------- Notes
message_id--- int(8)------ ? Primary key ? Required
image_id------- int(8)------- ? Primary key ? Required
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
Explanation / Answer
Task 1:
//Set the auto increment field to NULL or 0 if you want it to be auto magically assigned. And please
//insert your own name here
Insert into person set values (0,'Donald','Trump');
Tast 2:
ALTER TABLE person ADD age VARCHAR(50);
Task 3:
UPDATE person SET AGE=71 where fname='Donald' and lname='Trump';
Task 4:
DELETE FROM person where fname='Diana' and lname='Taurasi';
Happy Learning :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.