SQL coding help Query 1: Which guest(s) (only list the guestNo) have stayed in b

Question

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.   

guest guestNo guestFirst guestLast guestStreet guestCity guestState guestPhone guestEmail 1 Robert Croft 123 main Boise ID (208) 564-1456 rcroft@yahoo.com 2 Howard Smith 234 main Tulsa OK (405) 744-1144 hsmith@ gmail.com 3 Mike Rose 345 main Hays KS (785) 624-5511 mrose@yahoo.com 4 Matt Smith 234 main Monroe LA (318) 597-8417 matt4432@yahoo.com 5 Jennifer Bratton 2526 Chatham Way Stillwater OK (405) 777-5413 brattonj@outlook.com 6 Bryan Hedrick 35 West Liberty Meridian ID (208) 555-3262 bryanhedrick@ gmail.com 7 Raj Ramanathan 145 E. Highland Fargo ND (210) 555-7487 rajrama@outlook.com 8 Marie James 35 N. Perkins Road Kansas City KS (913) 732-5541 marie23james@outlook.com 9 Tony Chang 2545 Brighton Lane Shreveport LA (318) 267-4419 tony.chang@centurylink.com 10 John Jones 345 Apple St Boise ID (208) 662-4517 john.jones@cableone.net 11 Jason Williamson 1123 15th Street Stillwater OK (405) 238-5959 jasonwokstate@suddenlink.net 12 Martin Johnson 3478 Trenton St. Ruston LA (318) 233-9084 martinj@ gmail.com 13 Susan Ellis 1211 West Oak Oklahoma City OK (405) 332-1167 susanellis@outlook.com

Explanation / Answer

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'

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.