help in SQL simple queries ?

[curt girl]

Hello All..

I have a database which include those tables

Hotel(hotelno,hotelname,city)
guest(guestno,guestname,guestadress)
room(roomno,hotelno,type,price)
booking(hotelno,guestno,datefrom,dateto,roomno)

pks are underlined..

and i have to find those queries

1- find those guests who booked one room or more during this current month ?

2- find out the rooms no. with their hotels name which are empty (not booked) today (current day) ?

can anyone help me solving them ?

[amrita]

Please give us what have you tried yet ? So that we can tell you what is the issue ?

[FunkyDexter]

Just to be clear, we don't do people's homework for them and this looks suspiciously like a homework assignment. If you want to get an answer you'll need to have a go at it yourslef and then come back to us with specific problems that you were unable to resolve.

If you need a hand getting started on querying then check the faqs at the head of this forum. There are some excellent ones on SQL which should give you all you need. W3Schools also offer an excellent set of explanations of the various SQL commands.

If this is some commercial work that you're hoping to get done then there is a section where you post a work request but people will want paying for the work they do.

[curt girl]

Please give us what have you tried yet ? So that we can tell you what is the issue ?

well that was my answer but it didnt work

1- select distinct count(g.guestno) , b.datefrom
from guest g ,booking b
where g.guestno=b.guestno
and b.datefrom=( select ..........???

im new to the sql and i really dont know how to deal with the date queries
im sure that i can get the soluation using the subquery but i couldnt apply it ..

2- select distinct hotelname ,b.roomno
from hotel , booking b ,room r
where r.roomno=b.roomno
and b.datefrom<>sysdate

the result wasnot as expected it gives me all the rooms no. in each hotel


any advice please ?

[GaryMazzone]

What is the database?

[curt girl]

Just to be clear, we don't do people's homework for them and this looks suspiciously like a homework assignment. If you want to get an answer you'll need to have a go at it yourslef and then come back to us with specific problems that you were unable to resolve.

If you need a hand getting started on querying then check the faqs at the head of this forum. There are some excellent ones on SQL which should give you all you need. W3Schools also offer an excellent set of explanations of the various SQL commands.

If this is some commercial work that you're hoping to get done then there is a section where you post a work request but people will want paying for the work they do.

Thank you Sir, for clarification but there is a misunderstanding of my question
it Is not obligatory home work or business at all, but I try to study the material as selfstudy and faced with this exercise, I could not find out the right solution
It was my fault from the beginning that i did not put my attempts in the solution and now I wrote put it down in my privuos post..
I repeat my thanks to you with sincere appreciation and respect for all tips and advices you offer here ..

[curt girl]

What is the database?

dunno what u mean in your question
but if u mean my database
it is (myproject) which include all the above tables
sorry if i didnt get what u mean

[Pradeep1210]

No... he means SQL Server, Access, MySQL, Oracle etc. Which one?

[FunkyDexter]

It was my fault...

That's OK. I didn't mean to tell you off. It's just that you'll get a much better response round here if people can see you've had a go yourself.

For question 1, what does 'in the current month' actually mean? Does it mean the from date is in the current month, or the to date, or both, or either? You'll need to clarify that before you can design a query to resolve it. Looking at your query so far it looks like you're basing it on the from date only, which means you'll want something like:-

Select *
From Booking
Where Month(DateFrom) = Month(GetDate())

The syntax might change a bit depending on which database system you're using. The point is you check that the month of the from date is the same as the month of the current date. If you want to base it on both dates in some way then it's going to get more complicated.

For question 2 there's a couple of ways you can do it but I personally like the NOT EXISTS clause because it's the easiest to understand.

Select * From Room
Where NOT EXISTS
  (Select * From Booking
  Where Booking.RoomID = Room.RoomID
  And Booking.HotelID = Booking.HotelID
  And FromDate <= GetDate()
  And ToDate >= GetDate())

Hope that helps as a starter. Try those queries out and, if they don't give you what you want, let us know what the problem is and we'll be able to help further.

[GaryMazzone]

This was my go at it in SQL Server:

1:

SELECT 
    GuestName,Count(Booking.*)
FROM 
    Guest 
INNER JOIN Booking 
    ON Booking.Guestno = Guest.GuestNo
WHERE 
    (MONTH(Booking.DateFrom) = MONTH(GETDATE()) AND YEAR(Booking.DateFrom) = YEAR(GETDATE())
    OR 
    (MONTH(Booking.DateTo) = MONTH(GETDATE()) AND YEAR(Booking.DateTo) = YEAR(GETDATE())

2:

SELECT 
    Hotel.HotelName,
    Hotel.City,
    Room.RoomNo,
    Room.Type
FROM HOTEL
INNER JOIN ROOM
    ON HOTEL.HotelNo = Room.HotelNo
LEFT OUTER JOIN BOOKING
    ON Room.RoomNo = Booking.RoomNo
WHERE 
    GETDATE() BETWEEN Booking.DateFrom AND Booking.DateTo
    AND Booking.RoomNo IS NULL

[curt girl]

No... he means SQL Server, Access, MySQL, Oracle etc. Which one?

well , im using Oracle ..

[curt girl]

That's OK. I didn't mean to tell you off. It's just that you'll get a much better response round here if people can see you've had a go yourself.

For question 1, what does 'in the current month' actually mean? Does it mean the from date is in the current month, or the to date, or both, or either? You'll need to clarify that before you can design a query to resolve it. Looking at your query so far it looks like you're basing it on the from date only, which means you'll want something like:-

Select *
From Booking
Where Month(DateFrom) = Month(GetDate())

The syntax might change a bit depending on which database system you're using. The point is you check that the month of the from date is the same as the month of the current date. If you want to base it on both dates in some way then it's going to get more complicated.

For question 2 there's a couple of ways you can do it but I personally like the NOT EXISTS clause because it's the easiest to understand.

Select * From Room
Where NOT EXISTS
  (Select * From Booking
  Where Booking.RoomID = Room.RoomID
  And Booking.HotelID = Booking.HotelID
  And FromDate <= GetDate()
  And ToDate >= GetDate())

Hope that helps as a starter. Try those queries out and, if they don't give you what you want, let us know what the problem is and we'll be able to help further.

I do appreciate ur help
and for question 1 .. i think it means
during the current month means that date from
is included..
logically your select statment is more close to the correct answer
but i dont know if i can use the function(Getdate()) with the Oracle

..as well as in the second query
it sounds really great using the NOTEXIST clause

..thank u again .. i will rebuild mt answer i will be back if i got new problem
and i wish that isnot going to happen..

[curt girl]

This was my go at it in SQL Server:

1:

SELECT 
    GuestName,Count(Booking.*)
FROM 
    Guest 
INNER JOIN Booking 
    ON Booking.Guestno = Guest.GuestNo
WHERE 
    (MONTH(Booking.DateFrom) = MONTH(GETDATE()) AND YEAR(Booking.DateFrom) = YEAR(GETDATE())
    OR 
    (MONTH(Booking.DateTo) = MONTH(GETDATE()) AND YEAR(Booking.DateTo) = YEAR(GETDATE())

2:

SELECT 
    Hotel.HotelName,
    Hotel.City,
    Room.RoomNo,
    Room.Type
FROM HOTEL
INNER JOIN ROOM
    ON HOTEL.HotelNo = Room.HotelNo
LEFT OUTER JOIN BOOKING
    ON Room.RoomNo = Booking.RoomNo
WHERE 
    GETDATE() BETWEEN Booking.DateFrom AND Booking.DateTo
    AND Booking.RoomNo IS NULL

really im speachless about that
even i know no thing about sql server but obviously ..it sounds so close to the oracle sql
..one day i will use ur code
and it may be my start with the sql server
thank u again

[Pradeep1210]

In oracle you would use SYSDATE instead of GETDATE(). Try that.

[FunkyDexter]

If you're using my queries as a basis I'd recommend you do a quick double check on Gary's queries because he picked up something I didn't. Checking the month matches isn't enough, you need to check that the year also matches.

[GaryMazzone]

I just noticed the I also missed the group by in the first query. There should be a GROUP BY GuestName after the WHERE clause.

[FunkyDexter]

meh, we'll call it a team effort