MySQL The following tables have already been created for you. Each table is alre
ID: 3908094 • 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
if you have any doubts, please give me comment...
-- 1)
INSERT INTO person VALUES('', 'ANU', 'NAGA');
-- 2)
ALTER TABLE person ADD email VARCHAR(100) NOT NULL;
--3)
UPDATE person SET email = 'mymail@mail.com' WHERE person_id=1;
--4)
DELETE FROM person WHERE first_name = 'Diana' AND last_name='Taurasi';
--5)
ALTER TABLE contact_list ADD favorite VARCHAR(10);
-- 6)
UPDATE contact_list SET contact_id=1, favorite='y';
-- 7)
UPDATE contact_list SET favorite='n' WHERE contact_id<>1;
-- 8)
INSERT INTO contact_list VALUES(1,1,1,'y'),(1,2,2,'n'),(2,2,1, 'y');
-- 9)
CREATE TABLE image(
image_id INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,
image_name VARCHAR(50) NOT NULL,
image_location VARCHAR(250) NOT NULL
);
--10)
CREATE TABLE message_image(
message_id INT(8),
image_id INT(8)
);
-- 11)
--write your data
INSERT INTO image VALUES('',,),('',,),('',,),('',,),('',,);
--12)
INSERT INTO message_image VALUES(1,1), (1,2), (1,3), (2,1), (2,2);
--13)
SELECT S.first_name, S.last_name, R.first_name, R.first_name, M.message_id, M.message, M.send
FROM message M, person S, person R
WHERE M.sender_id = S.person_id AND M.receiver_id = R.receiver_id AND S.first_name = 'Michael' AND S.last_name = 'Phelps';
--14)
SELECT COUNT(*) AS no_of_messages, P.person_id, P.first_name, P.last_name
FROM message M, person P
WHERE M.sender_id = P.person_id
GROUP BY M.person_id, P.first_name, P.last_name;
--15)
SELECT message_id, message, sent, image_name, image_location
FROM message M INNER JOIN message_image MI ON M.image_id = MI.image_id;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.