SQL Queries Query!

Databases
1

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.

SELECT *
FROM Hotel;
  1. List full details of all hotels in London.

SELECT *
FROM Hotel;	
WHERE address = ‘London’;
  1. List the names and addresses of all guests in London aplhabetically ordered by name.

SELECT name,address
FROM Hotel, Guest
WHERE Hotel.address = ‘London’
ORDER BY Guest.name ASC;
  1. List all double(‘D’) or family rooms(‘F’) with a price below 40.00 per night, in ascending order of price.

SELECT type
FROM Room
WHERE (type = ‘D’) OR (type = ‘F’)
AND (price < 40.00) ORDER BY price ASC;
  1. List the booking for which no dataTo has been specified.

SELECT *
FROM Booking
WHERE dateTo IS NULL;
  1. How many hotels are there?

SELECT COUNT(*)
AS num_hotels
FROM Hotel;
  1. What is the average price of a room?

SELECT  AVG(price)
AS avg_price
FROM Room;
  1. What is the total revenue per night from all double rooms?

SELECT SUM(price)
AS total_rev
FROM Room
WHERE (type = ‘D’);
  1. List the price and type of all rooms at the Grosvenor hotel.

SELECT price,type
FROM Hotel,Room
WHERE (Hotel.name = ‘Grosvenor’);
  1. List all guests currently staying at the Grosvenor hotel.

SELECT Guest.name
FROM Guest,Hotel
WHERE (Hotel.name = ‘Grosvenor’);
  1. What is the total income from all bookings at the Grosvenor hotel today?

SELECT COUNT(Booking.guestNo)
 (SELECT SUB(price)
  FROM Room;)
FROM Booking,Hotel
WHERE (Hotel.name = ‘Grosvenor’);
  1. List the number of rooms in each hotel.

SELECT COUNT(*)
 (SELECT *
  FROM Hotel; )
AS no_rooms
FROM Room;
  1. List the number of hotels with more than 100 rooms and located in London.

SELECT COUNT(*)
 (SELECT COUNT(*)
  AS no_rooms
  FROM Rooms
  WHERE no_rooms > 100;)
AS no_hotels
FROM Hotel
WHERE (address = ‘London’);
  1. What is the most commonly booked room type for each hotel in London?

SELECT type, COUNT(*) AS TypeCount
 (SELECT *
  FROM Hotel
  WHERE (address = ‘London’);)
FROM Room
GROUP BY type
ORDER BY TypeCount DESC;
2

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:


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:


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…


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:


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


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


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


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.


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…


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…):


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…


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…

3

Wow thanks m8 thats really helpful

4

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.