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 ?
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
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”
use the INSERT statement
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 ?
sounds like room_pk is built only on hno, not on the pair (hno,rno)
i already did this for the first two tables
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]
Seems fine
Seems fine
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.
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.
Same as above.
Also, why are Booking and Departure separate? Is it possible to have multiple departures for one booking or vice versa?
There are quite a lot more. At least 5 that I can see right now.
rémon, beautiful post :award:
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.
BList the Names of all hotels which are in Kolkata, Chennai and Mumbai.[/B]
SELECT hname,city
FROM Hotel
WHERE city not in ('Mumbai','Kolkata','Chennai');
BList the names of all guests who checked in last year[/B]
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
BFind guest name that has booking for a given date.[/B]
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.
This one is not correct since it lists all hotels that are not in any of those cities.
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')
Again, only the guest name is asked, nothing else. Again you don’t need the hotel here, and you should use INNER JOIN.
You should at least give each one a try. We’re glad to help, but this a bit too much.
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.
BList the names of hotels which are located in more than two cities[/B]
SELECT hname,COUNT(city) AS 'No. Of Cities'
FROM Hotel
GROUP BY hname
HAVING COUNT(city) > 2;
BFor a given hotel(hno supplied) list total no. of room of different type.[/B]
SELECT type, COUNT(type) AS 'No. of Rooms'
FROM Room
WHERE hno = 13
Group BY type
BFor a given hotel(hname supplied) list total no. of room of different type.[/B]
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
BList names of hotels which have minimum charge for Luxury room for a given city.[/B]
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
BList names of all hotels where charge of Luxury room is between 3000 and 4000[/B]
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
BCount the number of bookings of Luxury room in a given hotel in 2012.[/B]
BFind out total earnings of a hotel in 2012.[/B]
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 [B]NULL[/B] results
BFind guest name that has booking on Luxury Room in 2013.[/B]
Do I have to link 3 tables in (9) & (11)
Please tell how do I find the last three.
I’ve done two more…
BCount the number of bookings of Luxury room in a given hotel in 2012.[/B]
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
BFind guest names that has booking on Luxury Room in 2013.[/B]
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-
BFind the total earnings of a given hotel in 2012[/B]
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
BCount the number of bookings of Luxury room in a given hotel in 2012.[/B]
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.
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?
Sorry, sorry sorry… !! I made a big mistake in a hurry.
I copy/pasted the wrong question for the code.
BFind out total earnings of a given hotel in 2012.[/B]
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
good, then you should be able to determine yourself whether your queries are working properly
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.
BFind out total earnings of a given hotel in 2012.[/B]
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
your join between room and booking is wrong