Question

Creating a MySQL database and having trouble with this problem any help is appreciated.

a. Tasks Description 1. Update table room to set the value of column roomtype equal to the corresponding id value in the table roomtype. This requires a select * from roomtype: to see what the id value is for each room Dess. For example, in my database 1 | Critical b. 2 | Private c. 3 Semi-Private 2. Alter table room so that column roomType is an integer instead of a varchar, not null should be maintained. 3. Alter table room so that column roomType is a foreign key to table roomtype, column id.

Answer 1

1) As you need to update so I guess it has to look like this:

UPDATE room

SET roomtype = CASE RoomDesc

WHEN 'Critical' THEN 1

WHEN 'Private' THEN 2

WHEN 'Semi-Private' THEN 3

END

2) Just follow the basic syntax in this part:

alter table room alter column roomtype int

3) I am assuming that roomtype and colmnid both are two different table so you have to assign foreign key to each

ALTER TABLE room
ADD FOREIGN KEY (roomType) REFERENCES roomType(roomtype);