Setting Primary Key & Foreign key, Adding Information.

[Sexy Devil]

I have the following table-

Hotel(hno, hname, city)
Room(hno, rno, type, charge)
Guest(gno, gname, gadd, phone)
Booking(gno, hno, rno, idate, payment)
Departure(gno, hno, rno, odate)

[hno-Hotel Number; hname-Hotel Name, rno-Room Number, gname-Guest Name; gno-Guest Number; gadd-Guest Address; idate-Booking Date;odate-Departure Date]

What are the primary keys and foreign keys ?
How will I fill data in these tables ?

[r937]

for hotel table, primary key is hno

for room table, primary key is ( hno, rno ), and hno is a foreign key

can you do the rest? it works just like that

[Sexy Devil]

yes,... thanks for replying...
i've done the rest... !
but how do i add information to these tables ??
like names of hotel and city in "hotel"

[r937]

use the INSERT statement

[Sexy Devil]

Hey thanks for your help.
Everything worked.
But I think I've messed up with Primary Key and Foreign Key.
When I add-

INSERT INTO Room
VALUES (1,1,'AC',1200)    [hno, rno, type, charge]
 INSERT INTO Room
VALUES (1,2,'AC',1200)    [hno, rno, type, charge]

I get errors-

Violation of PRIMARY KEY constraint 'room_pk'. Cannot insert duplicate key in object 'dbo.Room'. The duplicate key value is (1).
The statement has been terminated.

Could you tell me which are the primary keys for each table and foreign keys among them ?

[r937]

I get errors-

sounds like room_pk is built only on hno, not on the pair (hno,rno)

Could you tell me which are the primary keys for each table and foreign keys among them ?

i already did this for the first two tables

[Sexy Devil]

Sir, Please check-

Hotel(hno, hname, city) [Primary Key- hno]
Room(hno, rno, type, charge) [Primary Keys-hno,rno ]
Guest(gno, gname, gadd, phone) [Primary Key-gno,gname,phone ]
Booking(gno, hno, rno, idate, payment) [Primary Key-gno,rno,idate ]
Departure(gno, hno, rno, odate) [Primary Key-gno,hno,odate ]

Foreign Keys-hno [On Room -> Reference-Hotel]
hno [OnDeparture -> Reference-Hotel]

[ScallioXTX]

Hotel(hno, hname, city) [Primary Key- hno]

Seems fine

Room(hno, rno, type, charge) [Primary Keys-hno,rno ]

Seems fine

Guest(gno, gname, gadd, phone) [Primary Key-gno,gname,phone ]

Too many fields. Remember the primary key is the most minimal information required to identify an object. Only one field is the primary key here.

Booking(gno, hno, rno, idate, payment) [Primary Key-gno,rno,idate ]

Not enough fields. If you do it this way a guest is not able to book the same room number in two different hotels on the same day. Not likely that'll ever happen, but still.

Departure(gno, hno, rno, odate) [Primary Key-gno,hno,odate ]

Same as above.

Also, why are Booking and Departure separate? Is it possible to have multiple departures for one booking or vice versa?

Foreign Keys-hno [On Room -> Reference-Hotel]
hno [OnDeparture -> Reference-Hotel]

There are quite a lot more. At least 5 that I can see right now.

[r937]

rémon, beautiful post

[Sexy Devil]

Thank you for help.
I created the tables and added information using INSERT statement.
I need some help with queries. I could do only 3 of them.
(1)List the Names of all hotels which are in Kolkata, Chennai and Mumbai.

SELECT hname,city
 FROM Hotel
WHERE city not in ('Mumbai','Kolkata','Chennai');

(2)List the names of all guests who checked in last year

SELECT gname, gadd, idate
FROM Hotel H, Booking B, Guest G
WHERE B.idate BETWEEN('2012-01-01') AND('2012-12-31')
  AND G.gno = B.gno
  AND B.hno = H.hno

(3)Find guest name that has booking for a given date.

SELECT gname, gadd, idate
FROM Hotel H, Booking B, Guest G
WHERE B.idate = '2013-01-05'
  AND G.gno = B.gno
  AND B.hno = H.hno

(4)List the names of hotels which are located in more than two cities
(5)For a given hotel(hno supplied) list total no. of room of different type.
(6)For a given hotel(hname supplied) list total no. of room of different type.
(7)List names of hotels which have minimum charge for Luxury room for a given city.
(8)List names of all hotels where charge of Luxury room is between 3000 and 4000
(9)Count the number of bookings of Luxury room in a given hotel in 2012.
(10)Find out total earnings of a hotel in 2012.
(11)Find guest name that has booking on Luxury Room in 2013

Are the first three right ?
Please tell me how do I do the rest. I am not able to execute them.

[ScallioXTX]

(1)List the Names of all hotels which are in Kolkata, Chennai and Mumbai.

SELECT hname,city
 FROM Hotel
WHERE city not in ('Mumbai','Kolkata','Chennai');

This one is not correct since it lists all hotels that are not in any of those cities.

(2)List the names of all guests who checked in last year

SELECT gname, gadd, idate
FROM Hotel H, Booking B, Guest G
WHERE B.idate BETWEEN('2012-01-01') AND('2012-12-31')
  AND G.gno = B.gno
  AND B.hno = H.hno

More or less correct. You don't need the hotel table for this one, so don't use it. Also you'd better use INNER JOIN instead of carthesian product, where. Also, you're selecting too many fields; only the guest name is asked.

SELECT gname
FROM Booking B INNER JOIN Guest G ON G.gno=B.gno
WHERE B.idate BETWEEN('2012-01-01') AND('2012-12-31')

(3)Find guest name that has booking for a given date.

SELECT gname, gadd, idate
FROM Hotel H, Booking B, Guest G
WHERE B.idate = '2013-01-05'
  AND G.gno = B.gno
  AND B.hno = H.hno

Again, only the guest name is asked, nothing else. Again you don't need the hotel here, and you should use INNER JOIN.

Please tell me how do I do the rest. I am not able to execute them.

You should at least give each one a try. We're glad to help, but this a bit too much.

[Sexy Devil]

First Wish you a Happy New Year
Sorry about the first one. Actually I was just checking "not in" operator.

Thanks for correcting the next two.
I've tried, these give correct results.

(4)List the names of hotels which are located in more than two cities

SELECT hname,COUNT(city) AS 'No. Of Cities'
FROM Hotel
GROUP BY hname
HAVING COUNT(city) > 2;

(5)For a given hotel(hno supplied) list total no. of room of different type.

SELECT type, COUNT(type) AS 'No. of Rooms'
FROM Room
WHERE hno = 13
Group BY type

(6)For a given hotel(hname supplied) list total no. of room of different type.

SELECT type, COUNT(type) AS 'No. of Rooms'
FROM Room R INNER JOIN Hotel H ON R.hno=H.hno
WHERE hname = 'The Amber'
Group BY type

(7)List names of hotels which have minimum charge for Luxury room for a given city.

SELECT hname,min(charge) AS 'Min. Charge'
FROM Room R INNER JOIN Hotel H ON R.hno=H.hno
WHERE city = 'Kolkata' AND type = 'Luxury'
GROUP BY hname

(8)List names of all hotels where charge of Luxury room is between 3000 and 4000

SELECT hname
FROM Room R INNER JOIN Hotel H ON R.hno=H.hno
WHERE charge BETWEEN(3000) AND(4000) AND type = 'Luxury'
GROUP BY hname

(9)Count the number of bookings of Luxury room in a given hotel in 2012.


(10)Find out total earnings of a hotel in 2012.

SELECT SUM(charge) AS 'Total Earnings'
FROM Room R INNER JOIN Booking B ON R.hno=B.hno JOIN Hotel H ON R.hno=H.hno
WHERE H.hname = 'Hayatt Hotel'
AND B.idate BETWEEN('2012-01-01') AND('2012-12-31')

This gives NULL results

(11)Find guest name that has booking on Luxury Room in 2013.

Do I have to link 3 tables in (9) & (11)
Please tell how do I find the last three.

[Sexy Devil]

I've done two more...

(9)Count the number of bookings of Luxury room in a given hotel in 2012.

SELECT R.rno,COUNT(R.rno) AS 'No. Of Times'
FROM Room R, Booking B,Hotel H
WHERE R.rno = B.rno AND H.hno = R.hno
AND type = 'Luxury'
AND idate BETWEEN('2012-01-01') AND('2012-12-31')
AND hname = 'Taj'
GROUP BY R.rno

(11)Find guest names that has booking on Luxury Room in 2013.

SELECT gname
FROM Guest G, Booking B, Room R
WHERE G.gno = B.gno
AND type = 'Luxury'
AND idate BETWEEN('2013-01-01') AND('2013-12-31')
GROUP BY gname

I just can't get-
(10)Find the total earnings of a given hotel in 2012

[r937]

two suggestions which will help you immensely down the road...

first, learn explicit JOIN syntax

change this --

FROM Room R, Booking B,Hotel H
WHERE R.rno = B.rno AND H.hno = R.hno

to this --

  FROM Room R
INNER
  JOIN Booking B
    ON B.rno = R.rno
INNER
  JOIN Hotel H
    ON H.hno = R.hno

by using explicit JOIN syntax, you will always have to write the ON clause, and this will prevent you from writing incorrect joins such as the one you wrote for (11) --

FROM Guest G, Booking B, Room R
WHERE G.gno = B.gno

can you see the error there?

second suggestion, always prefix ~every~ column name in a query that has more than one table, not just the columns that have the same name in more than one table

this will aid in understanding a query if you are trying to debug it

[Sexy Devil]

(9)Count the number of bookings of Luxury room in a given hotel in 2012.

SELECT hname, SUM(charge) AS 'Total Earning',H.hno
FROM Room R
INNER JOIN Booking B ON B.rno = R.rno
INNER JOIN Hotel H ON B.hno = H.hno
WHERE idate BETWEEN('2012-01-01') AND('2012-12-31')
GROUP BY hname,H.hno

Is this right ??? It shows result but some extra is added to, I think.
Please tell me the right code for this last one.

[r937]

Is this right ???

no

"for a given hotel" means that you need a WHERE clause to restrict the results to one particular hotel

are you testing these queries somewhere?

[Sexy Devil]

Sorry, sorry sorry... !! I made a big mistake in a hurry.
I copy/pasted the wrong question for the code.

(10)Find out total earnings of a given hotel in 2012.

SELECT SUM(charge) AS 'Total Earning',H.hno
FROM Room R
INNER JOIN Booking B ON B.rno = R.rno
INNER JOIN Hotel H ON B.hno = H.hno
WHERE idate BETWEEN('2012-01-01') AND('2012-12-31')
AND hname = 'The Amber'
GROUP BY H.hno

Yes, I test my queries in Microsoft SQL 2012

[r937]

Yes, I test my queries in Microsoft SQL 2012

good, then you should be able to determine yourself whether your queries are working properly

[Sexy Devil]

Yes, I have tested other queries which you have done.
But I am not able to find whats wrong with this last one. It gives extra. For hname 'The Imperial' 19 the total should sum to 5000, but it shows 38000.

(10)Find out total earnings of a given hotel in 2012.

SELECT SUM(charge) AS 'Total Earning',H.hno
FROM Room R
INNER JOIN Booking B ON B.rno = R.rno
INNER JOIN Hotel H ON B.hno = H.hno
WHERE idate BETWEEN('2012-01-01') AND('2012-12-31')
AND hname = 'The Amber'
GROUP BY H.hno

[r937]

your join between room and booking is wrong

[Sexy Devil]

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.

[r937]

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

[Sexy Devil]

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.