# SQL Queries Query!

Ok we’ve been given the following tables

Room (roomNo,hotelNo,type,price)
Booking (hotelNo,guestNo,dateFrom,dateTo,roomNo)

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;
``````
1. List the names and addresses of all guests in London aplhabetically ordered by name.
``````
FROM Hotel, Guest
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
``````
1. What is the most commonly booked room type for each hotel in London?
``````
SELECT type, COUNT(*) AS TypeCount
(SELECT *
FROM Hotel
FROM Room
GROUP BY type
ORDER BY TypeCount DESC;
``````

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

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:

``````
FROM Hotel;

``````

2. List full details of all hotels in London. Not quite. The ; would go after the london part, so it should be:

``````
FROM Hotel

``````

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…

``````
FROM Hotel H, Booking B, Guest G
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
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