SQL Select Statement

[Bandog76]

Just completed this assignment and wanted to make sure if the answer, I provided is correct.

Hotel Schema

Hotel(hotelNo, hotelName, city)
Room(roomNo, hotelNo, type, price)
Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest(guestNo, guestName, guestAddress)


Simple Queries

5.7 List full details of all levels
SELECT*
FROM hotel;

5.8 List full details of all hotels in London
SELECT hotelNo, hotelName
FROM city;

OR
SELECT*
FROM hotel
WHERE city=’London’;

5.9 List the names and address of all guests living in London, alphabetically ordered by name.
SELECT guestName, guestAddress
FROM Guest
WHERE London
ORDERED BY guestName ASC;

5.10 List all double or family rooms with a price below $40.00 per night in ascending order of price.

SELECT type, price
FROM room
WHERE type=’double’ OR ‘family’ AND price < 40
ORDER by price;
5.11 List the booking for which no dateTo has been specific
SELECT booking
FROM date to
WHERE


Aggregate Functions

5.12 How many hotels are there?
SELECT COUNT (*) As my count
FROM hotel;

5.13 What is the average price of a room
SELECT room AVG (price)
FROM Room
Group by Price;

5.14 What is the total revenue per night from all double rooms?
SELECT count (*)
FROM room
WHERE type=’double room’

5.15 How many different guest have made booking for august
SELECT Count (*) AS ‘different guests’
FROM booking
WHERE date to = ‘August’

Subqueries and Joins

5.16 List the price and type of all rooms at the Grosvenor hotel
SELECT room
WHERE hotel=’Grosvenor Hotel’ ALL (SELECT room =’type’ AND ‘price’

5.17 List all guests currently staying at the Grosvenor Hotel
SELECT guestNo, guestName, guestAddress
FROM Guest
WHERE hotel ALL (SELECT hotel
FROM Guest
WHERE hotel=’Grosvenor’

5.18 What is the total income from bookings for the Grosvenor Hotel today.
SELECT hotelNo, guestNo, dateFrom, dateTo, roomNo (SELECT AVG (income)
FROM booking

[kimmckenzie]

You got most of them but here's a list of the ones that have problems.

For 5.8 the OR one is correct, not the first one.

5.9 almost be you need to check again.

5.14 look at the SUM function and change the group by so that it sums by the date

5.15 I don't know how your date is set up but it probably won't = 'August'. You need to either take the month out of the date use Between if the database allows it or you can use > one date and less than the another date. Just < is better than <= because it only has to check one time.

5.16 , 5.17 and 5.18 if you can use a join rather than a sub query the performance will be better because the indexes will be used. Sub queries are generally going to require full table scans.


For instance for 5.18 you have
What is the total income from bookings for the Grosvenor Hotel today.
SELECT hotelNo, guestNo, dateFrom, dateTo, roomNo (SELECT AVG (income)
FROM booking


Select SUM(price)
from room, hotel,booking
where hotel.hotelno = room.hotelNo
and room.hotelNo = booking.hotelNo
and room.roomNo = Booking.roomNo
and hotel.hotelName = 'Grosvenor'
and Booking.dateFrom > #7/8/2003#
and Booking.dateTo < #7/10/2003#



The pound signs in date part is database specific to Access so ....