a)Again, work on the room table created in the last week with the records as follows:

b)

Select the distinct value of guestNo in the room relation.

c)Select the rooms not in the hotel Hilton.

d)Select the rooms not in the hotel Hilton and whose number is not 333.

e)Select the rooms not in the hotel Hilton or whose number is not 333.

f)Select the rooms in the hotel Hilton and order the results by prices.

g)Select the rooms in the hotel Hilton and order the results by prices in a descendent order.

h)Select the rooms with a price greater than $100, and order the results firstly by hotel name in an ascendant order, and secondly by price in a descendent order.

i)Add a column floor to the room table.

j)Drop the column floor in the room table.

k)Drop the column hotelNo in the room table. What happened? Why?

l)Change the data type of the column price into some numerical data type with decimals.

m)Construct a new relation guest with the columns (guestNo, guestname, address) without setting guestNo as the primary key.

n)Drop the column guestNo and re-add it.

o)Make guestNo as the primary key of the guest table. What happened? Why?

p)Modify the column guestNo so that it cannot be null. Re-do step o).

q)Drop the column guestNo and re-add it. What happened? Why?

r)For the room table, add a constraint stating that guestNo is a foreign key from the guest number. What happened? Why?

s)Insert the following values to the guest table.

INSERT INTO guest VALUES (12345,'James Bond','London'); INSERT INTO guest VALUES (54321,'Lady Gaga',null); INSERT INTO guest VALUES (11111,'Super Man','Mars');

For the room table, add a constraint stating that guestNo is a foreign key from the guest table.

Delete the guest James Bond from the guest table. What happened? Why?

Q2)

Write an SQL statement to select all rooms in the Hilton hotel with the price less than

$200, and order the results firstly by types and secondly by prices.

Results New Query roomNo 101 101 350 Standard Single Master President 12345 150 54321 1000