need help forming sql query...

suppose i create two tables

/*this table describes the room */

creat room (
  room_id NUMBER not null/* this is the room number */ )

/*this table describes room reservations */
create room_reservations (

 room_id NUMBER ; /* this is the room number */
 start_time DATE; /*the room is reserved from 'start_time' to 'end_time'
 end_time DATE;   /*the room is reserved from 'start_time' to 'end_time'

How do I write sql statement to find out total hrs reserved for each room?? i am hoping to be able to do this withh one sql statement and by not adding anymore columns to the tables. if the query turns out to be a complicated sql statement, I would appreciate some sort of explanation as well, if it's not too much trouble.

For example, if the row entries for room table are   room_id
   20
   21
   22
And row entries for room_reservations table are   room_id start_time end_time

    20       12pm        1pm
    21        8pm        9pm
    21        10pm       11pm

 The desired sql query should return
  room_id      total_hrs_reserved
    20             1
    21             2
    22             0

I am stuck on this for over a week now;; any help will be greatly appreciated.

thank you in advance

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 02 2000 - 21:52:24 CST