Setting Primary Key & Foreign key, Adding Information

Databases
21

I thought of using the charge as charge per night. I tried to convert and subtract booking date and departure date. , multiplying the result with charge.
But it gives me 64 results of which majority are negative.

Select
((convert(numeric,B.idate) - convert(numeric,D.odate))*R.charge)
AS 'Total Earning'
From Room R, Booking B, Departure D
WHERE B.idate BETWEEN('2012-01-01') AND('2012-12-31')
AND D.odate BETWEEN('2012-01-01') AND('2012-12-31')
AND R.hno = 19
Group BY B.idate, D.odate, R.charge

Please help. :confused: :headbang:

22

all of a sudden, there is a new table involved

and you seem to have abandoned explicit JOIN syntax, reverting to the despised and deprecated “comma join” syntax

well, i can’t help you if you don’t help yourself

you need practice with your joins

23

Well, I changed the ‘payment’ from char(10) to int. Then I stored the amount payed by guests.
So it became easy to find out the total earning.

SELECT sum(payment) 
FROM Booking B
WHERE idate BETWEEN('2012-01-01') AND('2012-12-31')
AND hno = 20

I thank you for helping me through out these problems. I have learned some SQL.
I will try to practice.