I need help on SQL Queries

Question

I need to know if my query is correct or not this is the database schema:

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

List the rooms details that are unoccupied at the hotel number 'H004' in October. This is what I wrote:

 SELECT*
FROM Room r,Hotel h 
WHERE r.hotelNo=h.hotelNo AND hotelNo='H004'
      AND roomNo NOT IN 
      (SELECT roomNo
       FROM Booking b,Hotel h
       WHERE dateFrom='1-Oct'
              AND dateTo='31-Oct'
       AND b.hotelNo=h.hotelNo
        AND hotelNo='H004');

Answer 1

I would phrase this with not exists:

select r.*
from rooms r
where r.hotelno = 'H004' and not exists (
    select 1
    from booking b
    where 
        b.hotelno = r.hotelno 
        and b.roomno = r.roomno
        and b.datefrom <  '2020-11-01'
        and b.dateto   >= '2020-10-01'
)

This reads as: find all rooms at the given hotel for which there is no booking that overlaps the month of October 2020.

Notes:

• you don't need to bring the hotel table to get the results you want; and if you ever needed to: use standard joins! Implicit joins (with commas in the from clause are legacy syntax, that should not be used in new code)

• MySQL understands date literals as strings in format YYYY-MM-DD