SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Setting Primary Key & Foreign key, Adding Information.

    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 ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use the INSERT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Hey thanks for your help.
    Everything worked.
    But I think I've messed up with Primary Key and Foreign Key.
    When I add-
    Code:
    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 ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sexy Devil View Post
    I get errors-
    sounds like room_pk is built only on hno, not on the pair (hno,rno)

    Quote Originally Posted by Sexy Devil View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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]

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Sexy Devil View Post
    Hotel(hno, hname, city) [Primary Key- hno]
    Seems fine

    Quote Originally Posted by Sexy Devil View Post
    Room(hno, rno, type, charge) [Primary Keys-hno,rno ]
    Seems fine

    Quote Originally Posted by Sexy Devil View Post
    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.

    Quote Originally Posted by Sexy Devil View Post
    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.

    Quote Originally Posted by Sexy Devil View Post
    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?

    Quote Originally Posted by Sexy Devil View Post
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rémon, beautiful post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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.
    Code:
    SELECT hname,city
     FROM Hotel
    WHERE city not in ('Mumbai','Kolkata','Chennai');
    (2)List the names of all guests who checked in last year
    Code:
    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.
    Code:
    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.

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Sexy Devil View Post
    (1)List the Names of all hotels which are in Kolkata, Chennai and Mumbai.
    Code:
    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.

    Quote Originally Posted by Sexy Devil View Post
    (2)List the names of all guests who checked in last year
    Code:
    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.

    Code:
    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')
    Quote Originally Posted by Sexy Devil View Post
    (3)Find guest name that has booking for a given date.
    Code:
    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.

    Quote Originally Posted by Sexy Devil View Post
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    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
    Code:
    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.
    Code:
    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.
    Code:
    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.
    Code:
    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
    Code:
    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.
    Code:
    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.

  13. #13
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done two more...

    (9)Count the number of bookings of Luxury room in a given hotel in 2012.
    Code:
    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.
    Code:
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    two suggestions which will help you immensely down the road...

    first, learn explicit JOIN syntax

    change this --
    Code:
    FROM Room R, Booking B,Hotel H
    WHERE R.rno = B.rno AND H.hno = R.hno
    to this --
    Code:
      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) --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (9)Count the number of bookings of Luxury room in a given hotel in 2012.

    Code:
    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.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sexy Devil View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sexy Devil View Post
    Yes, I test my queries in Microsoft SQL 2012
    good, then you should be able to determine yourself whether your queries are working properly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your join between room and booking is wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Member Sexy Devil's Avatar
    Join Date
    Dec 2012
    Location
    Kolkata
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •