Query 2:Which guests (by first and last name and guest no) have booked rooms at
ID: 3844914 • Letter: Q
Question
Query 2:Which guests (by first and last name and guest no) have booked rooms at the Holiday Inn in Tulsa? 2.5 pts
Query 3: list the number of rooms of each type (single, double, family) in each hotel. You should display the name of the hotel, type of room and the count. Hint: you will be grouping by more than one column. 2 pts
Query 4: which hotel rooms (list the hotelNo and roomNo) have a price higher than the average of all rooms? 1.5 pts
Query 5: Which hotels (by name) do not have family rooms? 1.75 pts
Query:
Booking(HotelNo,GuestNo,DateFrom,DateTo,RoomNO
Guest(GuestNo,Guestfirst,guestlast,gueststreet,guestcity,gueststate,guestphone,guestemail.
HOTEL(hotelno,hotelname,hotelcity,hotelphone)
ROOM(roomNO,hotelNO,Type,price)
EXAMPLE
Select hotelno
From hotel
where hotel.hotelno <'5'
Order By DESC
Explanation / Answer
2. SELECT Guestfirst,guestlast,Guestno from Guest WHERE Guestno = (SELECT GuestNo FROM Booking WHERE hotelNo = (SELECT hotelNo FROM Hotel WHERE (hotelName = ‘Holiday Inn’ && hotelcity='Tulsa')));
3.select a.hotelname, b.type, count(*)
from Hotel a, Room b
where (a.hotelno = b.hotelno)
group by a.hotelname, b.type;
4.select a.hotelno, b.roomno
from hotel a, room b
where (a.hotelno = b.hotelno) and
b.price > (select avg(c.price) from room c);
5.select a.hotelno, b.roomno
from hotel a, room b
where (a.hotelno = b.hotelno) and
(b.type <> 'family');