SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Swansea, South Wales, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Queries Query!!!!!!

    Ok we've been given the following tables

    Hotel (hotelNo,name,address)
    Room (roomNo,hotelNo,type,price)
    Booking (hotelNo,guestNo,dateFrom,dateTo,roomNo)
    Guest (guestNo,name,address)

    And we've also been given 14 SQL queries to write, they are as follows, with what I have come up with for each one. Can someone tell me which ones are right and which ones are wrong

    1. List Full details of all hotels.
    Code:
    SELECT *
    FROM Hotel;
    2. List full details of all hotels in London.
    Code:
    SELECT *
    FROM Hotel;	
    WHERE address = ‘London’;
    3. List the names and addresses of all guests in London aplhabetically ordered by name.
    Code:
    SELECT name,address
    FROM Hotel, Guest
    WHERE Hotel.address = ‘London’
    ORDER BY Guest.name ASC;
    4. List all double('D') or family rooms('F') with a price below 40.00 per night, in ascending order of price.
    Code:
    SELECT type
    FROM Room
    WHERE (type = ‘D’) OR (type = ‘F’)
    AND (price < 40.00) ORDER BY price ASC;
    5. List the booking for which no dataTo has been specified.
    Code:
    SELECT *
    FROM Booking
    WHERE dateTo IS NULL;
    6. How many hotels are there?
    Code:
    SELECT COUNT(*)
    AS num_hotels
    FROM Hotel;
    7. What is the average price of a room?
    Code:
    SELECT  AVG(price)
    AS avg_price
    FROM Room;
    8. What is the total revenue per night from all double rooms?
    Code:
    SELECT SUM(price)
    AS total_rev
    FROM Room
    WHERE (type = ‘D’);
    9. List the price and type of all rooms at the Grosvenor hotel.
    Code:
    SELECT price,type
    FROM Hotel,Room
    WHERE (Hotel.name = ‘Grosvenor’);
    10. List all guests currently staying at the Grosvenor hotel.
    Code:
    SELECT Guest.name
    FROM Guest,Hotel
    WHERE (Hotel.name = ‘Grosvenor’);
    11. What is the total income from all bookings at the Grosvenor hotel today?
    Code:
    SELECT COUNT(Booking.guestNo)
     (SELECT SUB(price)
      FROM Room;)
    FROM Booking,Hotel
    WHERE (Hotel.name = ‘Grosvenor’);
    12. List the number of rooms in each hotel.
    Code:
    SELECT COUNT(*)
     (SELECT *
      FROM Hotel; )
    AS no_rooms
    FROM Room;
    13. List the number of hotels with more than 100 rooms and located in London.
    Code:
    SELECT COUNT(*)
     (SELECT COUNT(*)
      AS no_rooms
      FROM Rooms
      WHERE no_rooms > 100;)
    AS no_hotels
    FROM Hotel
    WHERE (address = ‘London’);
    14. What is the most commonly booked room type for each hotel in London?
    Code:
    SELECT type, COUNT(*) AS TypeCount
     (SELECT *
      FROM Hotel
      WHERE (address = ‘London’);)
    FROM Room
    GROUP BY type
    ORDER BY TypeCount DESC;
    signature

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)

    Re: SQL Queries Query!!!!!!

    First, a comment: It's always better to select the specific fields you want rather than select *. It will execute faster.

    As for your SQL statements...

    1. List Full details of all hotels. Just hotel specific information? No room info at this level? If so, then your code will work, though this is better:
    Code:
    SELECT HotelNo, Name, Address 
      FROM Hotel;
    2. List full details of all hotels in London. Not quite. The ; would go after the london part, so it should be:
    Code:
    SELECT HotelNo, Name, Address 
      FROM Hotel
     WHERE Address = 'London';
    3. List the names and addresses of all guests in London aplhabetically ordered by name. Close. You needed to include booking and tie all three tables together. Here is what I would use...
    Code:
    SELECT G.Name, Address
    FROM Hotel H, Booking B, Guest G
    WHERE H.Address = ‘London’
      AND H.HotelNo = B.HotelNo
      AND B.GuestNo = H.GuestNo
    ORDER BY G.name ASC;
    **NOTE** The H, B and G are notations that can be used instead of Hotel.Address or Booking.HotelNo. Use which ever you prefer (as you can tell, I like the notations better)

    4. List all double('D') or family rooms('F') with a price below 40.00 per night, in ascending order of price. Just listing the type wouldn't provide helpful information. List the hotel, roomno and price along with the type. Try this instead:
    Code:
    SELECT H.Name, RoomNo, Type, Price
      FROM Hotel H, Room R
     WHERE Type IN ('D','R')
       AND Price < 40.00
     ORDER BY Price, Hotel;
    ** NOTE ** IN is more efficient than multiple OR condition checks. If you have a situation where you are checking one specific field for multiple EXACT values, use the IN

    5. List the booking for which no dataTo has been specified.. This one will work fine, but again try to select specific fields...

    [b]6. How many hotels are there?[b]. This one is good.

    7. What is the average price of a room? Should this one be the average price of ALL rooms, or just the average price per hotel? I think that would be more useful information.

    8. What is the total revenue per night from all double rooms? The code you provided will only give the TOTAL revenue for ALL nights, not per night. This SHOULD work better (based per night on the dateto and assuming one night per booking. Should probably be broken down further for multiple night stays):
    Code:
    SELECT dateTo, SUM(price) AS total_rev
      FROM Booking B, Room R
     WHERE R.type = ‘D’
       AND R.HotelNo = B.HotelNo
       AND R.RoomNo  = B.RoomNo
     GROUP BY DateTo
    9. List the price and type of all rooms at the Grosvenor hotel. Need to tie Hotel and Room together
    Code:
    SELECT DISTINCT Type, Price
      FROM Hotel H, Room R
     WHERE H.Name = ‘Grosvenor’
       AND H.HotelNo = R.HotelNo
     ORDER By Type
    10. List all guests currently staying at the Grosvenor hotel. Need to include the bookings table and tie all three together and include a date
    Code:
    SELECT G.Name
      FROM Guest G, Hotel H, Booking B
     WHERE H.name = ‘Grosvenor’;
       AND H.HotelNo = B.HotelNo
       AND B.DateTo  = TodaysDate
       AND B.GuestNo = G.GuestNo
    ** Note ** TodaysDate is a variable you will need to define....

    [b]11. What is the total income from all bookings at the Grosvenor hotel today?[b]. Nope. Just need to sum the price and include the date.
    Code:
    SELECT SUM(Price)
      FROM Booking B, Room R, Hotel H
     WHERE B.DateTo  = TodaysDate
       AND B.RoomNo  = R.RoomNo
       AND B.HotelNo = R.HotelNo
       AND R.HotelNo = H.HotelNo
       AND H.Name    = ‘Grosvenor’
    12. List the number of rooms in each hotel. Not quite. Try this instead...
    Code:
    SELECT HotelName, Count(*) as RoomCount
      FROM Hotel H, Room R
     WHERE H.HotelNo = R.HotelNo
     GROUP By HotelName
    13. List the number of hotels with more than 100 rooms and located in London.. This one would probably work better split into a couple statements and broken out from there, but you can try this(not guaranteeing this works, just coming off the top of my head....):
    Code:
    SELECT Count(*)
      FROM Hotel
     WHERE Address = 'London'
       AND HotelNo in (SELECT HotelNo
                         FROM Room
                        WHERE EXISTS
                        (SELECT Count(*) AS RoomCount
                           FROM Room 
                          WHERE RoomCount > 100))
    14. What is the most commonly booked room type for each hotel in London?. Close. Try this instead..
    Code:
    SELECT Type, COUNT(*) AS TypeCount
      FROM Hotel H, Room R
     WHERE H.Address = 'London'
       AND H.HotelNo = R.HotelNo
     GROUP BY type
     ORDER BY TypeCount DESC;
    Whew!!! I hope this helps a little...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Swansea, South Wales, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thanks m8 thats really helpful
    signature

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    No problem. I hope all the syntax is correct (should be for the most part), since I was too lazy to create dummy tables with data in them to test the code.

    Let us know if you need more help.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


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
  •