SQL coding help Query 1 Which guests only list the guestNo h

SQL coding help

Query 1: Which guest(s) (only list the guestNo) have stayed in both hotelNo 2 and hotelNo 4?

Query 2: which hotels (only list the hotel number) do not have family rooms?

Query 3: Which room or rooms (list the hotel No and the roomNo) have the lowest price?

Query 4 Which types of rooms have an average price higher than the average price of all rooms?

Query 5: You must use a set operator for this query: Which guests (by guestNo) are from IDAHO or have stayed in Hotel Number 6? Guests that are both from Idaho and have stayed in Hotel Number 6 do not need to be listed twice.   

Solution

Please find the required queries below:

1)

SELECT g.guestNo
FROM guest g
   ,booking b
WHERE g.guestNo = b.guestNo
   AND b.hotelNo IN ( 2 ,4 );

2)

SELECT h.hotelNo
FROM hotel h
   ,room r
WHERE h.hotelNo = r.hotelNo
   AND r.type <> \'double\' ;

3)

SELECT h.hotelNo
   ,r.roomNo
FROM hotel h
   ,room r
WHERE h.hotelNo = r.hotelNo
   AND r.price IN (
       SELECT min(price)
       FROM room
       );

4)

SELECT h.hotelNo
   ,r.roomNo
FROM hotel h
   ,room r
WHERE h.hotelNo = r.hotelNo
   AND r.price > (
       SELECT Avg(price)
       FROM room
       );

5)

SELECT g.guestNo
FROM guest g
   ,booking b
WHERE g.guestNo = b.guestNo
   AND b.hotelNo = 6

INTERSECT

SELECT g.guestNo
FROM guest g
WHERE g.city = \'Idaho\'