13. Write a query that inserts the following booking: guest 10074, hotel 114, ro
ID: 3888795 • Letter: 1
Question
13. Write a query that inserts the following booking: guest 10074, hotel 114, room number 303, on startdateAugust 17, 2017 going through August 20, 2017. Will your insert statement enter the data in the Bookings table successfully? Why or why not? 14. Write a query that adds ten dollars (10.00) to the rates for all of the single rooms in hotels in the state of New Hampshire. 15. Write query that removes the information on hotel 105 from the Hotel table. Will your delete statement remove the information successfully? Why or why not?Explanation / Answer
INSERT INTO BOOKINGS (GID,roomNo,HID,startdate,enddate) VALUES (10074,114,303,'08/17/2017','08/20/2017');
This statement will add the data in table successfuly. But it is not normalized data. In the booking table, combination of columns (GID, HID, roomNo) will ensure only unique row in that table only. But it will not check the information with other tables as no foreign key is set.
In this insert statement GID is 10074, room No is 303 and HID is 114. So (10074,303,114) creates the unique combination for this table and that is the only checks.So it satisfies the unique condition and the record is inserted. But as per normalization rule it is wrong. The GUEST table must have the data of Guest 10074 having basic identity of guest and the Room table mut have room no 303 having its rate /type etc. It can happen only through foreign key constraint which is not applied in Guest table.
For that we need to use update query which is given below:
UPDATE Room
SET rate = rate + 10.0
from Hotel
where Hotel.HID = Room.HID AND Room.roomtype = 'single' AND Hotel.hotelstate= 'NH'
This query will update signle room rate of all the hotel from NH. Now to verify the updated rates please use select query as
SELECT * FROM Room
DELETE FROM Hotel
WHERE HID=105
DELETE command generally deletes all the rows in the current table and from its child table according to the condition mentioned in the WHERE clause. If you want to delete data from that particular table then use ONLY keyword after FROM keyword (like DELETE FROM ONLY table_name).
This statement will not removes the information as there is no record with hotel id 105. It will return a command tag with DELETE 0. Here 0 specifies that no records are deleted.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.